Performing Postgresql major version upgrades - in Docker

As an advocate of microservices, I am also running various PostgreSQL databases in Docker containers. This works quite well, and updating is easy. As long as one stays within the major version that was initially used when first creating the container.

However, when a major version upgrade is needed, things get a bit more complicated. PostgreSQL provides a documentation page on how to upgrade from one major version to another. It has a link to the pg_upgrade executable, which somehow combines all steps into one command. Yet it still requires a backup, having the executable installed in the first place, and so on.

So after running various containers for some years without issues, it was time to upgrade. After doing some research, I came across tianon/docker-postgres-upgrade¹, which looked promising. And it looked not only promising, but it was also rather easy to use when compared with the official documentation.

So in this post, I am sharing how I recently did a few major version upgrades for various PostgreSQL databases running in Docker.

 The Upgrade Process

Breaking it down, the upgrade process consists of the following steps:

1. Create a backup (always, of course)
2. Copy the old PG data to a new location
3. Perform the upgrade via pg_upgrade
4. Apply some fixes to the new PG data
5. Replace the old PG data with the new one
6. Start the container with the new PG data

Sounds quite easy, right? Well, let’s see how this unfolds. We’ll do it in steps. But don’t worry, I’ll attach a script at the end which combines all individual steps.

First, we set some variables:

Here, the paths refer to the location of the volume mount for the respective PG container. If you have no version indicator in the path, no big deal. However, I recommend it, as doing so will make future updates way easier.

export OLD_VERSION=15
export NEW_VERSION=16
export PGDATAOLD=<some>/<path>/$OLD_VERSION/
export PGDATANEW=<some>/<path>/$NEW_VERSION/

Next, stop the instance to make sure we copy the latest data.

docker stop <container-name>

Then create a local backup of the current PG data. Make sure to use rsync to maintain owner and permissions. Do not use cp!

mkdir pg_bak_$OLD_VERSION
rsync -av $PGDATAOLD pg_bak_$OLD_VERSION

Now we do the upgrade using the tianon/postgres-upgrade image. There are different images for different versions, and not all combinations exist.

docker run --rm -v $PGDATAOLD:/var/lib/postgresql/$OLD_VERSION/data/ -v $PGDATANEW:/var/lib/postgresql/$NEW_VERSION/data/ tianon/postgres-upgrade:$OLD_VERSION-to-$NEW_VERSION

Next, replace the old PG data with the new one and start the container again. If you have everything defined in a docker-compose.yml, you can also use sed for an automated replacement.

sed -i "s#<some><path>/$OLD_VERSION#<some><path>/$NEW_VERSION#g" docker-compose.yml

After the upgrade, some fixes need to be applied to the new PG data, as pg_hba.conf is often malformed. See also this issue, which explains it in more detail.

docker exec <container-name> bash -c 'echo "host all all all md5" >> /var/lib/postgresql/data/pg_hba.conf'

Last, start the container again and inspect the logs. All good? Great! You just performed a major PostgreSQL upgrade in a Docker container.

Here is the full script to c/p and modify as needed:

export OLD_VERSION=15
export NEW_VERSION=16
export PGDATAOLD=<some><path>/$OLD_VERSION/
export PGDATANEW=<some><path>/$NEW_VERSION/

# stop and remove old instance
docker stop <container name>

# create backup
mkdir pg_bak_$OLD_VERSION
rsync -av $PGDATAOLD pg_bak_$OLD_VERSION

# do upgrade
docker run --rm -v $PGDATAOLD:/var/lib/postgresql/$OLD_VERSION/data/ -v $PGDATANEW:/var/lib/postgresql/$NEW_VERSION/data/ tianon/postgres-upgrade:$OLD_VERSION-to-$NEW_VERSION

# Run after new container is up
# fix pg_hba.conf -> https://github.com/tianon/docker-postgres-upgrade/issues/16
docker exec postgresprod bash -c 'echo "host all all all md5" >> /var/lib/postgresql/data/pg_hba.conf'

# (optional) Replace old PG data with new one via sed
# sed -i "s#<some><path>/$OLD_VERSION#<some><path>/$NEW_VERSION#g" docker-compose.yml

# start the container again