How to restore a postgres database created via an older version of postgres?

Question

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)

• user1822: You should create the backup using pg_dump from your Postgres 11 installation.
• Zach Smith: I was provided the backup from a 3rd party - so I can't do that
• Daniel Vérité: 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.

Answer 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.

• Zach Smith: 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: 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
• Daniel Vérité: @ZachSmith: can you add the output of pg_restore --version for the one that didn't work?
• Zach Smith: output is added
• Daniel Vérité: @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.