Migrate Postgres database to next major version

I had to migrate from Postgres 16 to 17, and as you know (or didn’t) binary data formats are not compatible between major engine versions.

To keep things short, I had two docker containers named pg16 and pg17 respectively. There are two steps involved.

Export data from v16

For this, you need the server running, so make sure there are no critical workloads running. Then, execute the following command:

docker exec -it pg16 pg_dumpall -U postgres > backup.sql

This will create a file called backup.sql in your environment (not inside docker container). If the database size is too big, you can consider piping it to a compressor, which wasn’t relevant in my case.

Once this is done, you can shutdown pg16 server.

Import data into v17

Again, the server needs to be running, so make sure nothing critical is running. Then, execute the following command in the same directory:

cat backup.sql | docker exec -i pg17 psql -U postgres

After a few minutes (depending on database size) everything will be imported into the new server.

You can use this method to move data between servers of the same version as well.

What’s transferred

In my case, there were several databases, roles, groups and permissions configured. Everything was moved through to the new instance, and all the services talking to this database just continued working as usual.