172

I just upgraded to postgres 10.2 on mac os which matches 10.2 on heroku. I'm trying to download a copy of the database and restore it locally. Before the upgrade the restore would work fine.

I run

pg_restore --verbose --clean --no-acl --no-owner -h localhost -d database_name backup.dump

but I am getting this error:

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

The database appears to be working OK. It's a rails app and I upgraded the pg gems. I can run rake db:create and db:migrate just fine.

Paolo Forgia
  • 6,572
  • 8
  • 46
  • 58
Mark Swardstrom
  • 17,217
  • 6
  • 62
  • 70

14 Answers14

199

You need to upgrade your local postgres to get the last security patch from the 2018-03-01, like Heroku did the 1st march. You need one of the last releases 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22.

The security patch can be found here https://www.postgresql.org/about/news/1834/.

It seems the patch modified pg_dump, that's probably why we can't use pg_restore anymore without that patch for the dump of Heroku (with the patch applied).

Wilson Vargas
  • 2,841
  • 1
  • 19
  • 28
jumichot
  • 2,016
  • 1
  • 12
  • 8
  • 89
    `brew upgrade postgresql` `brew postgresql-upgrade-database` – gerry3 Mar 02 '18 at 22:49
  • 2
    Using Linux Mint my version is 9.5.11 and it says it is up to date. How do I force it to use the latest version using apt-get? – Kritz Mar 05 '18 at 15:11
  • Postgresql 9.5.12 is now in the official Ubuntu 16.04 xenial repo https://packages.ubuntu.com/xenial/postgresql-9.5 – HarlemSquirrel Mar 06 '18 at 16:18
  • Or you could take a `plain` sql dump that will probably work on the same major version – George Daramouskas Mar 07 '18 at 12:53
  • 6
    @gerry3's comment is two separate commands. Beware copy-and-pasters! – Joe Masilotti Mar 08 '18 at 13:12
  • 4
    heads up the two commands `brew upgrade postgresql && brew postgresql-upgrade-database` do indeed fix the problem but my local databases got dropped along the way. Backup them up if you have critical data! – Jarvis Johnson Mar 10 '18 at 01:59
  • On windows, did the upgrade confirmed 9.3.22 via psql SELECT version(); and I am still getting the error. – edencorbin Mar 31 '18 at 11:06
  • Fixed. didn't work with pgadmin3 but worked on windows with pg_restore -U postgres -d db -1 pathtodump – edencorbin Mar 31 '18 at 11:18
  • I'm facing the same problem. Already updated postgre and pgadmin but I keep getting the same error :( – Viny Machado Apr 05 '18 at 20:32
  • what goes wrong if I upgraded to 9.6.11 and still get this message after a dump from the database? – spankmaster79 Jan 07 '19 at 09:48
  • If it were impossible to retake the backup - for example, if you were using a backup to restore a database, is the only way to get around this problem to download an older version of postgres, restore, redo the backup and then restore again? – Zach Smith Jul 16 '19 at 12:59
19

I ran into this same issue today, I don't know if something changed on Heroku's side, because locally I'm running 9.6 but my database on Heroku is 9.4, so it didn't seem to be about the version point difference (it was working until today.)

If you're using the Postgres.app (https://postgresapp.com/) on a Mac make sure you're running v2.1.3 (as of this writing that is the newest version). When you upgrade the Postgres.app you get the newest patch release of Postgres, (which is likely causing the mismatch you're currently experiencing - Heroku has upgraded for the latest security patch, and your local machine may not have it).

I had to upgrade Postgres.app from 2.1.0 to 2.1.3 and it solved the problem.

Canuk
  • 748
  • 6
  • 19
13

First time I've had this issue, here's how I restored the Heroku Database to my Local:

$ rails db:drop
$ heroku pg:pull DATABASE_URL <insert local DB name> --app <app-name>
Jim Padilla
  • 139
  • 4
  • Nice workaround, I didn't know about `heroku pg:pull`. But this is not a suitable fix, as you still can't restore locally a previous heroku dump, also I guess this utilizes the live database resource while pulling. – demental Mar 02 '18 at 10:54
  • 1
    Not sure what the downvotes are for. OP is literally asking "I'm trying to download a copy of the database and restore it locally" and I am answering that specific question. – Jim Padilla Nov 29 '18 at 21:00
12

Since yesterday (03/01/2018) we also had problems restoring a backup from Heroku running on PostgreSQL 9.5:

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

Upgrading our PostgreSQL version from 9.5.11 to 9.5.12 fixed the issue.

cbx
  • 131
  • 4
7

For those who are trying to upgrade postgresql in Ubuntu without success (like Johan's commented above), try using the PostgreSQL apt repository, as stated in the official PostgreSQL download instructions: https://www.postgresql.org/download/linux/ubuntu/

deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
Diego Aragão
  • 91
  • 3
6

The fix for me involved both updating my Postgres.app and updating my local homebrew PostgreSQL.

Update my Postgres.app via Canuk's answer:

Open Postgres.app > Check for updates...

Update my local homebrew PostgreSQL via gerry3's comment on the accepted answer:

brew upgrade postgresql brew postgresql-upgrade-database

alxpck
  • 61
  • 5
6

Had this problem on Windows using pgAdmin 3.

Upgrading from pgAdmin 3 to pgAdmin 4 solved the problem for me.

If you are using a third-party tool, such as PgAdmin, to restore the provided dump file, the restore may not succeed even with the installed Postgres version being up to date. This is due to third-party tools often bundling their own versions of the pg_restore binary, that may not be up to date. https://help.heroku.com/YNH1ZJUS/why-am-i-getting-pg_restore-archiver-unsupported-version-1-13-in-file-header-error-with-pg_restore

Andrew
  • 18,680
  • 13
  • 103
  • 118
  • Thanks, this worked for me. I'm on PostgreSQL 9.5.12, and I was using pgAdmin 3. pgAdmin 4 was able to perform `pg_restore` successfully. – Alexander Jun 27 '18 at 21:17
5

I don't recommend upgrading your DB to a new major version locally (do apply security patches) just to fix this as you want that to match whatever version you have in production.

This fixed it on Ubuntu 16.04, restoring from a Heroku database.

First, ensure you have the postgresql repo as diego mentioned. Then install the upgraded client.

sudo apt-get update && sudo apt-get install postgresql-client-10

If you are Ubuntu 17.04 or 17.10, note that the PostgreSQL's zesty repo does not have the updated client's you need. You will want to use the xenial Postgres repo instead.

Ivan
  • 59
  • 3
  • I do recommend upgrading the db, since it's a security patch that's quite important if your database runs queries from untrusted users: https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path "The purpose of the release was to address CVE-2018-1058, which describes how a user can create like-named objects in different schemas that can change the behavior of other users' queries and cause unexpected or malicious behavior, also known as a "trojan-horse" attack." – RobM Mar 23 '18 at 00:44
  • I get `Unable to locate package postgresql-client-10` when I do this – Doug Mar 28 '18 at 03:09
  • @RobM Agreed. By "don't recommend upgrading..." I was referring to not upgrading to a major version that you are not using in production. Certainly wise to upgrade to the latest security patch within a major version locally, and on production of course. I will tweak the verbiage in my answer to better reflect my meaning. – Ivan Mar 29 '18 at 18:53
  • 1
    @Doug Don't forget to first add the appropriate postgres repo. Similar to what is described in this comment. https://stackoverflow.com/a/49116272/3975541 – Ivan Mar 29 '18 at 18:58
5

I encountered this problem on pgadmin III and was able to fix it through switching the binaries location:

File menu > Options > Binary Paths, then changed "PG bin path" to postgresql/9.x/bin instead of ProgramFiles/pgadmin. Restore worked fine afterwards.

Raf
  • 186
  • 1
  • 6
4

Here is how I resolved the issue on Ubuntu(16.04 LTS) where pg_restore/pg_dump gave me an older version, from psql 

$ pg_restore --version
pg_restore (PostgreSQL) 9.4.8

$ psql --version
psql (PostgreSQL) 9.5.14

The issue is that I have multiple pg versions installed, and pg_restore was pointing to the earlier version

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

The fix is to create a ~/.postgresqlrc file pointing to the correct version

$ cat ~/.postgresqlrc

9.5 main *

Once this was done, pg_restore points to the correct version, and the command goes through

Details given here: https://serverfault.com/questions/610777/wrong-version-of-pg-dump-on-ubuntu

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Rabi
  • 536
  • 5
  • 5
2

You're using an old version of pg_restore binary that doesn't support restoring the provided dump file. Please make sure that you use the latest Postgres version, which must be higher than the following release: https://www.postgresql.org/about/news/1834/ (10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22).

You can check the pg_restore version you're using by running pg_restore --version.

If you are using a third-party tool, such as PgAdmin, to restore the provided dump file, the restore may not succeed even with the installed Postgres version being up to date. This is due to third-party tools often bundling their own versions of the pg_restore binary, that may not be up to date.

Vagner Dev
  • 21
  • 1
0

I was encountering the same error. I updated to postgresql 10.3 locally, and that fixed the problem for me.

Sean McCleary
  • 3,690
  • 4
  • 33
  • 43
-1

For Windows:

Open Command Prompt (cmd), then go to location C:\Program Files\PostgreSQL\9.6\bin and run these commands:

pg_restore -U username -d dbname -1 filename.dump
pg_restore -U postgres -d app -1 G:\app09-07-2019.backup

Hope it will help you

biruk1230
  • 3,042
  • 4
  • 16
  • 29
Reena khan
  • 1
-3

I ran in the same issue 10 minutes ago, and found out about this thread from 2005: https://www.postgresql.org/message-id/1106163722.780395.266900%40z14g2000cwz.googlegroups.com

Basically the guy is saying the dump file is corrupt.

nfilzi
  • 62
  • 1
  • 7
  • 3
    Strange. I created a dump file from heroku and I got the same issue. This has never happened before. I tried upgrading postgresql from 9.6 to 10 but it did not work. – karantan Mar 02 '18 at 08:01
  • 1
    Are you on 10.2 or 10.3? You have to be on 10.3 now I believe. – Jack Kinsella Mar 02 '18 at 18:28
  • That mailing list thread is from 2005. There is something recently changed going on here that's different from the issue in that thread. – Yetanotherjosh Apr 19 '18 at 20:50