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 dump format.
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.

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

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