Upgrade Postgres Container to Version 10

For a newer version to this post, have a look at Upgrading Postgres Docker containers

Postgres version 10 is here - well actually it's out for couple months already, but I finally got to use it. In fact I got to upgrade an existing Postgres 9.6 container to version 10. The first (very naive) attempt, to just upgrade the container and re-use the existing data directory yielded the following error:

FATAL:   database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.2.

I sort of expected the error, so luckily I had a backup and could try again. Following is a step by step guide of what I did. This is mostly documentation for future me, since I have some more Postgres 9.x containers laying around, but of course, feel free to use it as well. I was pleasantly surprised, once I knew what I was doing, the upgrade went actually surprisingly smoothly and took only a few minutes.

How to upgrade, a step-by-step guide:

Preconditions

For sake of simplicity, I assume the following things:

• You are using either the official Postgres docker image or an image similar in configuration
• You are mounting /var/lib/postgresql/data onto your disc.

How-To:

1. Stop any application accessing the database, you want to do this, in order to guarantee a consistent state.

2. Create a dump using docker exec POSTGRES_CONTAINER pg_dumpall -U postgres > dump.sql. This will be stored on your local disc, so you'll be safe to stop and possibly remove the container.

3. Stop the container and back up the directory. Perform the back-up only after you stopped the container. This is again needed to ensure consistent data.

4. Start the new container using a postgres 10 image. In case you want to reuse the same directory mount path, you'll need to clean it, otherwise you'd see the aforementioned error message in the docker logs and the container would be crashing.

   FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.2.

5. I assume that now the Postgres 10 container is successfully up and running, but empty. So we need to import the data again.

   1. Copy the dump into the mount directory (cp dump.sql POSTGRES_MOUNT/)
   2. Switch into the shell inside the docker container (docker exec -it POSTGRES_CONTAINER bash)
   3. Switch into Postgre's data directory within the container(cd /var/lib/postgresql/data/)
   4. Import the data (psql -U postgres < dump.sql)

I hope this worked for you as well!