Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 being a live website and the other where I run my website in debug mode on the my dev box. Pretty ordinary setup.

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.

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:
$ 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)
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).
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:
$ 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:
$ 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:
I am using pg_dump not psql
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.
Here are the clusters on the love box and it's over port 5432 that on live.lan I'm running 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_wrapper to see how and where the two paths of pg_dump and pg_restore diverge down incompatible version paths.
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:
in spite of reporting identical version numbers the two pg_dumps are different. I would rule this out as beyond belief.
pg_dump runs pg_wrapper which runs /usr/lib/postgresql/10/bin/pg_dump with some mystery argument(s) that break it!
The second is plausible, and will require me to instrument pg_wrapper to diagnose.
Update 2:
And one instrumentation of pg_wrapper later. It eventuates that pg_dump runs pg_wrapper which runs /usr/lib/postgresql/12/bin/pg_dump yet it runs /usr/lib/postgresql/10/bin/pg_restore ... Go figure! Starting to think this a postgresql version interoperability bug!
Update 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:
If --host is 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)
If --host is 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).
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.
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.
                "Starting to think this a postgresql version interoperability bug!"  Sounds more like a packaging bug.
– jjanes
                Dec 23, 2019 at 14:47
                @BerndWechner I used the following command: sudo -u postgres pg_restore --verbose --clean --jobs=4 --disable-triggers --no-acl --no-owner -h localhost -U postgresql -d everest_development dump.psql but that didn't work. I am also getting same error while importing one database.
– LearningROR
                Dec 24, 2019 at 7:04
                Also if I do: muhammad@muhammad-mohsin:~/workspace_ror/everest$ psql -d everest_development -f dump.psql The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database. 
– LearningROR
                Dec 24, 2019 at 7:15
                I confirm your finding. pg_dump uses the latest (v12) executable if host is explicitly specified. This looks like a bug or design flaw.
– oᴉɹǝɥɔ
                Jan 10, 2020 at 17:39
                Not sure of a "solution" but I posted a workaround. The difference to my mind is that it works but shouldn't be needed, pg_wrapper should be smarter than it is. The workaround is simply to specify --host explicitly when using pg_dump and pg_restore and this forces them both to use the same version of postgresql (the latest one installed on your system) and remain compatible. If you use --host on one and not the other and have multiple versions of postgresql installed and an earlier version still in use in local configs, then forgetting this issue arises.
– Bernd Wechner
                Jan 23, 2020 at 10:47
ubuntu guys: most likely your pg_restore is outdated. Just use postgres doc and install newest version of postgres:
Create the file /etc/apt/sources.list.d/pgdg.list and add a line for the repository: deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main where ubuntu versions are:
20.04 - focal
18.04 - bionic
16.04 - xenial
Add keys: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update && sudo apt-get upgrade
It worked for me!
So my experience here was somewhat similar to OP, but not completely.
I had versions 9.4, 9.5, 11, and 12 installed, and all the pg_* tools pointed to the 9.4 version. I tried to pg_restore a dump create by version 12 on a different host, which didn't work because this host used the 9.4 tools, which were incompatible (same error as OP posted).
So, here was my debugging flow:
which pg_restore
/usr/bin/pg_restore
ls -l /usr/bin/pg_restore
/usr/bin/pg_restore -> ../share/postgresql-common/pg_wrapper
vim /usr/share/postgresql-common/pg_wrapper
Read about how it determines version. Just read through the first 20 or so lines
Apparantly, there is a --cluster option, which is helpful in resolving the version. So I simply added --cluster 12/mainto my pg_restore call, and everything works as expected again
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.
  
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.
Updating my PostgreSQL solved the problem
                To what version? v12 clearly had this problem in pg_wrapper. What version did you upgrade to? If there's an update to 12 that fixes it, let us know.
– Bernd Wechner
                May 5, 2020 at 23:24
On my case (on Mac, Postgres managed by Homebrew) the solution was to check the pg_dump version used to create the dump file and install that version of postgresql.
Apparently with Brew you can't install pg_restore separately from postgresql (not sure, didn't really dig into it), so I needed to install postgresql@12 and link it. That did it.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.