PostgreSQL: Importing dumps created with newer versions

When loading a database dump created with pg_dump into your database, you might run into an error like

Copypg_restore: error: unsupported version (1.15) in file header

This is because your local pg_restore version is too old to match the format created by pg_dump. The version of the PostgreSQL server doesn't matter here.

For example, the official Ubuntu 20.04 sources include only PostgreSQL 12, so your pg_restore version will also be v12. Ubuntu 22.04 includes version 14 in its sources.
Both seem to be incompatible with dumps created by PG 16's pg_dump, for example.

If you cannot upgrade to the latest PostgreSQL version, you can find a workaround below.

Background

Creating and restoring a database dump has four components: the source server, pg_dump, the target server, and pg_restore. pg_dump will create a dump, and pg_restore will restore it. But, there are version constraints by design:

pg_dump was designed to transfer data from older to newer versions of PostgreSQL. Source Show archive.org snapshot

• pg_dump can dump from servers as old as v9, but it will use the latest syntax (i.e. its own) for the dump file. pg_dump v17 will create a dump file in v17 syntax, no matter the source server version. Also, it can not dump from a server newer than itself: pg_dump version ≥ source server version.
• pg_restore will refuse to restore a dump to an older server, because the older server will not understand newer syntax. However, it will easily restore older dumps to newer servers. So: pg_dump version ≤ target server version.
• pg_dump and pg_restore are both installed as part of the postgresql-client package

Thus, for a successful restore, pg_dump and the source server should have a version ≤ the target server. In practice, this means you cannot use a staging dump if the staging Postgres (or pg_dump) are newer than your local Postgres server.

Workaround Step 1: Install a modern PostgreSQL client

Add the official PostgreSQL APT sources Show archive.org snapshot as described in the official PostgreSQL documentation.

Then, run sudo apt update and sudo apt full-upgrade. This might already install the latest version.

After adding the official sources, you can install any specific version. For example,

• sudo apt install postgresql-client-16 installs only the PG 16 client, including version 16 of pg_restore
• sudo apt install postgresql-16 installs PG 16 server and client.

Notes

• You can run different versions of the PG server in parallel. Whatever version you had installed already likely stays on the default port 5432, any further versions should be automatically configured to subsequent ports. Check your /etc/postgresql/*/main/postgresql.conf for that.
• psql (client) and postgres server versions do not have to match in order for the import to work. For example, can use client version 16 to connect to server version 12.
• Similarly for pg_restore, you can use newer versions to load dumps created by older versions of pg_dump.
• If you encounter a dump which was created with a very old version (< 11), we have a separate card on that.
• If apt update prints a warning like Skipping acquire of configured file 'main/binary-i386/Packages' as repository, edit /etc/apt/sources.list.d/pgdg.list (as root!) and add your architecture. Most likely, you need to put [arch=amd64] after deb so that it reads deb [arch=amd64] https://... or deb [arch=amd64 signed-by=....

Workaround Step 2: Connect with host option

The latest PostgresQL client will only be used when you connect to your database using the host option.

When using pg_restore, pass a --host=localhost parameter:

Copypg_restore -d my_database --host=localhost my_dump.dump

When using geordi dump Show archive.org snapshot , you need to modify your config/database.yml to include a host option:

Copy# database.yml
development:
  host: localhost