postgresql pg_restore: [archiver] 文件头中不支持的版本 (1.14)

pg_restore: [archiver] unsupported version (1.14) in file header

提问by Bernd Wechner

I have a live server and development box, call them live and dev respectively both running postgresql. I can see both and manage both with pgadmin4 without trouble, and both are fully functional the one behing a live website and the other when I run by website in debug mode on the my dev box. Pretty ordinary setup.

我有一个实时服务器和开发盒，分别称它们为 live 和 dev，它们都运行 postgresql。我可以使用 pgadmin4 轻松查看和管理两者，并且当我在开发箱上以调试模式通过网站运行时，两者都功能齐全，一个是实时网站，另一个是。很普通的设定。

For years I have been running the same bash script I wrote that dumps the live database then restores it on the dev box so I have the latest live snapshot to work with.

多年来，我一直在运行我编写的相同 bash 脚本，该脚本转储实时数据库，然后在开发箱上恢复它，因此我有最新的实时快照可以使用。

Today this fails me with the titled message:

今天，这让我失望，标题为消息：

pg_restore: [archiver] unsupported version (1.14) in file header

I have tried to diagnose this, and searched extensively on-line but am bedeviled and have failed so here I am cap in hand for expertise.

我曾试图对此进行诊断，并在网上进行了广泛的搜索，但我很困惑并且失败了，所以在这里我掌握了专业知识。

To help I will share the following:

为了帮助我将分享以下内容：

$ pg_dump --version
pg_dump (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)
$ pg_restore --version
pg_restore (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)
$ pg_dump --host=live.lan --port=5432 --dbname=mydb --username=myuser --format=custom > test.backup
$ ls -l test.backup 
-rw-r--r-- 1 bernd bernd 2398358 Dec 23 23:40 test.backup
$ file test.backup 
test.backup: PostgreSQL custom database dump - v1.14-0
$ pg_restore --dbname=mydb test.backup 
pg_restore: [archiver] unsupported version (1.14) in file header

Given pg_dump and pg_restore are identical versions and:

鉴于 pg_dump 和 pg_restore 是相同的版本，并且：

$ which pg_dump
/usr/bin/pg_dump
$ which pg_restore
/usr/bin/pg_restore
$ ls -l /usr/bin/pg_dump /usr/bin/pg_restore
lrwxrwxrwx 1 root root 37 Nov 14 23:23 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root 37 Nov 14 23:23 /usr/bin/pg_restore -> ../share/postgresql-common/pg_wrapper

I can see they are not just identical versions but are run by the same wrapper script (which happens to be a perl script - now that's a language you don't see much anymore and I used to code in extensively)

我可以看到它们不仅是相同的版本，而且由相同的包装脚本运行（它恰好是一个 perl 脚本 - 现在这是一种你不再看到的语言，我曾经广泛地编码）

So I'm left totally perplexed. Thinking there may be a version issue with the live machine:

所以我完全困惑了。认为可能是真机版本问题：

$ ssh live.lan
Welcome to Ubuntu 18.04.3 LTS (GNU/Linux 4.15.0-72-generic x86_64)
$ which pg_dump
/usr/bin/pg_dump
$ which pg_restore
/usr/bin/pg_restore
$ pg_dump --version
pg_dump (PostgreSQL) 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)
$ pg_restore --version
pg_restore (PostgreSQL) 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)

I can see the live box does have slightly older version of pg_dump (which would only matter if pg_dump on my dev box somehow used a RPC to the live box to run its pg_dump).

我可以看到 live box 确实有稍微旧版本的 pg_dump（只有在我的 dev box 上的 pg_dump 以某种方式使用 RPC 到 live box 来运行它的 pg_dump 时才重要）。

Now there is maybe a small clue in the fact that my dev box has seen a few postgresql upgrades come through and so for example:

现在可能有一个小线索，我的开发箱已经看到了一些 postgresql 升级，例如：

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

The 11 and 12 clusters remain unused as evidenced by empty log files. I'm using 10. But I do notice that:

空日志文件证明 11 和 12 集群仍未使用。我正在使用 10。但我确实注意到：

$ psql --version
psql (PostgreSQL) 12.1 (Ubuntu 12.1-1.pgdg18.04+1)
$ ssh live.lan
Welcome to Ubuntu 18.04.3 LTS (GNU/Linux 4.15.0-72-generic x86_64)
$ psql --version
psql (PostgreSQL) 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)

which is mildly fishy but again not obviously a cause or related:

这有点可疑，但也不是明显的原因或相关：

1. I am using pg_dump not psql
2. I am using only the dev boxes pg tools not the live boxes (they should be irrelevant, the whole data transfer theoretically over port 5432 on the live box which delivers a databse dump to pg_dump on my dev box.

1. 我使用的是 pg_dump 而不是 psql
2. 我只使用开发盒 pg 工具而不是实时盒（它们应该无关紧要，理论上整个数据传输通过实时盒上的端口 5432 传输数据转储到我的开发盒上的 pg_dump。

Here are the clusters on the love box and it's over port 5432 that on live.lan I'm running pg_dump!

这是 love box 上的集群，它位于 live.lan 上的端口 5432 上，我正在运行 pg_dump！

$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory           Log file
10  main    5432 online postgres /data/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

I am deeply perplexed and hamstrung at present by this. Would deeply appreciate forward moving clues. If I am compelled to fish around in the dark I will probbaly uninstall postgres 11 and 12 again and see if that helps, else I will end up having to trace /usr/share/postgresql-common/pg_wrapperto see how and where the two paths of pg_dump and pg_restore diverge down incompatible version paths.

我目前对此深感困惑和束手无策。将深深地感谢向前移动的线索。如果我被迫在黑暗中钓鱼，我可能会再次卸载 postgres 11 和 12，看看是否有帮助，否则我最终将不得不跟踪/usr/share/postgresql-common/pg_wrapper以查看 pg_dump 和 pg_restore 的两条路径如何以及在哪里分叉到不兼容的版本路径.

Update:

更新：

A further clue I have uncovered, that permits me a workaround but simply deepens the mystery is as follows:

我发现的另一条线索允许我解决问题，但只是加深了谜团，如下所示：

$ sudo -u postgres pg_dump --host=live.lan --port=5432 --dbname=mydb --username=myuser --format=custom > test.backup
$ sudo -u postgres /usr/lib/postgresql/10/bin/pg_dump --host=live.lan --port=5432 --dbname=mydb --username=myuser --format=custom > test2.backup
$ sudo -u postgres pg_restore -l test.backup
pg_restore: [archiver] unsupported version (1.14) in file header
$ sudo -u postgres pg_restore -l test2.backup
... produces listing of contents ...
$ sudo -u postgres pg_dump --version
pg_dump (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)
$ sudo -u postgres /usr/lib/postgresql/10/bin/pg_dump --version
pg_dump (PostgreSQL) 10.11 (Ubuntu 10.11-1.pgdg18.04+1)

That is perplexing beyond belief. The only possible explanations:

这是令人难以置信的令人困惑。唯一可能的解释：

1. in spite of reporting identical version numbers the two pg_dumps are different. I would rule this out as beyond belief.
2. pg_dump runs pg_wrapper which runs /usr/lib/postgresql/10/bin/pg_dump with some mystery argument(s) that break it!

1. 尽管报告了相同的版本号，但两个 pg_dump 是不同的。我会认为这是难以置信的。
2. pg_dump 运行 pg_wrapper，它运行 /usr/lib/postgresql/10/bin/pg_dump 并带有一些破坏它的神秘参数！

The second is plausible, and will require me to instrument pg_wrapper to diagnose.

第二个是合理的，需要我使用 pg_wrapper 进行诊断。

Update 2:

更新 2：

And one instrumentation of pg_wrapper later. It eventuates that pg_dump runs pg_wrapper which runs /usr/lib/postgresql/12/bin/pg_dumpyet it runs /usr/lib/postgresql/10/bin/pg_restore... Go figure! Starting to think this a postgresql version interoperability bug!

稍后对 pg_wrapper 进行一种检测。它最终导致 pg_dump 运行 pg_wrapper 运行/usr/lib/postgresql/12/bin/pg_dump但它运行/usr/lib/postgresql/10/bin/pg_restore......去图！开始认为这是一个 postgresql 版本的互操作性错误！

Update 3:

更新 3：

Looking deeper into pg_wrapper, nailed the cause, and yes I'd argue it's a pg_wrapper bug of a kind though it may be debatable, hardly though IMHO. Here's what it does:

深入研究pg_wrapper，确定原因，是的，我认为这是一种 pg_wrapper 错误，尽管它可能有争议，但恕我直言。这是它的作用：

If --hostis provided then it uses the newest version of postgresql installed (12 in my case and this is for pg_dump, so pg_dump 12 creates the dump)

如果--host提供，则它使用安装的最新版本的 postgresql（在我的情况下为 12，这是用于 pg_dump，因此 pg_dump 12 创建转储）

If --hostis not provided then it consults the user config (10 in my case, and this is for pg_restore, so pg_restore 10 is run and it can't read a file created by pg_dump 12).

如果--host未提供，则它会咨询用户配置（在我的情况下为 10，这是针对 pg_restore，因此运行 pg_restore 10 并且它无法读取由 pg_dump 12 创建的文件）。

So why is this a bug? Because I have a use config, and I'd like it respected whether or not I'm talking to a remote host. More to the point if I specify a host I certainly don't expect to use the latest local version ignoring local config. I'd expect either to respect local config (as is the case when no remote host is specified) or try to match the rmeote hosts version. Arbitrarily leaning on the latest installed version is IMHO deeply questionable.

那么为什么这是一个错误呢？因为我有一个使用配置，而且无论我是否在与远程主机交谈，我都希望它受到尊重。更重要的是，如果我指定一个主机，我当然不希望使用最新的本地版本而忽略本地配置。我希望要么尊重本地配置（就像没有指定远程主机的情况一样），要么尝试匹配 rmeote 主机版本。任意依赖最新安装的版本恕我直言，深表怀疑。

BUT it turns out there is a workaround that works. Essentially instead of:

但事实证明，有一种可行的解决方法。基本上代替：

sudo -u postgres pg_restore -l test.backup

this works:

这有效：

sudo -u postgres pg_restore --host=localhost -l test.backup

By specifying the host ironically we force it to ignore local configs and use the newest version of pg_restore which seems to work fine restoring to a PG 10 cluster.

通过讽刺地指定主机，我们强制它忽略本地配置并使用最新版本的 pg_restore，它似乎可以很好地恢复到 PG 10 集群。

回答by Jan

Here's another twist, but please note first that this is a windows case. If it's just linux for you, don't read further. All advice given here did not help me, eventually the cause IMC was simpler and had to do with the use of pgAdmin. Because of the repetitive nag for newer versions, my habit is to install pgAdmin separately (not using stackbuilder). (AT LEAST) in that case, pgAdmin has its own cache of utility programs and will use these unless you tell it differently. My pg instances are still version 11(.6), but the latest pgAdmin will probably have V12 utilities. Which may quite well cause a version discrepancy. This crept up on me after doing a number of succesful transfers from my main machine to a laptop. So, in pgAdmin do (menu)File->preferences->Paths and set Binary Paths corresponding to your postgres installation, IMC C:\Program Files\PostgreSQL\11\bin. That did the job.

这是另一个转折点，但请首先注意这是一个 windows 案例。如果它只是适合您的 linux，请不要继续阅读。这里给出的所有建议都没有帮助我，最终导致 IMC 更简单，并且与 pgAdmin 的使用有关。由于新版本的重复唠叨，我的习惯是单独安装pgAdmin（不使用stackbuilder）。（至少）在这种情况下，pgAdmin 有自己的实用程序缓存，除非您以不同方式告诉它，否则将使用这些缓存。我的 pg 实例仍然是版本 11(.6)，但最新的 pgAdmin 可能会有 V12 实用程序。这很可能会导致版本差异。在我成功地从我的主机转移到笔记本电脑之后，我突然想到了这一点。因此，在 pgAdmin 中执行 (menu)File->preferences->Paths 并设置与您的 postgres 安装相对应的二进制路径，IMC C:\Program Files\PostgreSQL\11\bin。那做的工作。

回答by Vincent Tang

check and see if your pgadmin is up to date, I had this problem and resolved it by updating it

检查并查看您的 pgadmin 是否是最新的，我遇到了这个问题并通过更新解决了它

回答by asiniy

ubuntu guys: most likely your pg_restoreis outdated. Just use postgres docand install newest version of postgres:

ubuntu 伙计们：很可能你的pg_restore已经过时了。只需使用 postgres doc并安装最新版本的 postgres：

1. Create the file /etc/apt/sources.list.d/pgdg.listand add a line for the repository: deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg mainwhere ubuntu versions are:

   • 20.04 - focal
   • 18.04 - bionic
   • 16.04 - xenial

2. Add keys: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

3. sudo apt-get update && sudo apt-get upgrade

1. 创建文件/etc/apt/sources.list.d/pgdg.list并为存储库添加一行：deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main其中 ubuntu 版本是：

   • 20.04 - 焦点
   • 18.04 - 仿生
   • 16.04 - xenial

2. 添加密钥： wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

3. sudo apt-get update && sudo apt-get upgrade

It worked for me!

它对我有用！

回答by Wariored

This error is caused by a version mismatch between the version of pg_dump used to create the backup file and the version of pg_restore used to attempt the restore.

此错误是由用于创建备份文件的 pg_dump 版本与用于尝试恢复的 pg_restore 版本之间的版本不匹配引起的。

Updating my PostgreSQL solved the problem

更新我的 PostgreSQL 解决了这个问题