Read more

PostgreSQL: Importing dumps created with newer versions

Daniel Straßner
September 30, 2020Software engineer at makandra GmbH

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

pg_restore: error: unsupported version (1.15) in file header
Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

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://....

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.

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

# database.yml
development:
  host: localhost
Posted by Daniel Straßner to makandra dev (2020-09-30 11:30)