3

I have a file db.backup, that I would like to restore to PostgreSQL version 11.

When I tried to install via Azure Data Studio I got an error:

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

Then I found that running pg_restore I can actually read the file:

pg_restore /..db.backup -U postgres > db-restore.sql

And I can see that the file is human-readable. However I would much rather just restore the database from the backup.

How can I do this? I tried installing an older version of PostgreSQL server, but I don't know how to install an older version of PSQL (yet). Would this help?

Is there some flag I can add to the restore command so that the restore works?

-- EDIT

The output of pg_restore --version, i.e. the version of pg_restore that gave me the error message mentioned above:

pg_restore (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.04+1)

Improve this question
3
  • You should create the backup using pg_dump from your Postgres 11 installation.
    – user1822
    Commented Jul 16, 2019 at 13:41
  • I was provided the backup from a 3rd party - so I can't do that
    – Zach Smith
    Commented Jul 16, 2019 at 13:42
  • However I would much rather just restore the database from the backup.: that's just what your pg_restore command did. You can either play that SQL file with psql or add flags to pg_restore to do it into a database instead of into a SQL file, but the result is the same.
    – Daniel Vérité
    Commented Jul 16, 2019 at 16:10

1 Answer 1

Reset to default
1

When I tried to install via Azure Data Studio I got an error:

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

Version 1.13 of the pg_dump archive format was introduced in February 2018 as part of the changes for CVE-2018-1058. The version bump was mentioned in the commit message with a specific warning about the problem:

Since this change requires a change in the behavior of pg_restore when processing an archive file made according to this new convention, bump the archive file version number; old versions of pg_restore will therefore refuse to process files made with new versions of pg_dump.

That was six months before PostgreSQL 11 was released, so an installation that supports PostgreSQL 11 is not supposed to use an older pg_restore. I'm not sure what exactly is "Azure Data Studio" in terms of the software stack it provides, but it looks like it has an outdated version of pg_restore that is not suitable for your needs.

Improve this answer
8
  • The problem went away when i used an older version of psql to do the restore. So i did the restore from OTHER than azure data studio
    – Zach Smith
    Commented Jul 17, 2019 at 7:45
  • And this didn't work for PSQL 11. So it seems to me that the new client isn't backwards compatible. Which is a little silly i think... but good to know that postgres database backups will eventually be stale and unusable over time
    – Zach Smith
    Commented Jul 17, 2019 at 7:46
  • @ZachSmith: can you add the output of pg_restore --version for the one that didn't work?
    – Daniel Vérité
    Commented Jul 17, 2019 at 8:58
  • output is added
    – Zach Smith
    Commented Jul 17, 2019 at 9:13
  • @ZachSmith: thanks. I can't understand how it does not work for you though because this exact version of pg_restore 11.4 has no problem for me with archives version 1.13, as expected.
    – Daniel Vérité
    Commented Jul 17, 2019 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.