Upgrade Docker PostgreSQL without export/import

I have a custom Mastodon instance, installed via three Docker containers (Mastodon itself, PostgreSQL, and Redis; I use Podman to run them, and a custom script to manage them).

Updating the PostgreSQL container is tricky because when the major PostgreSQL version change, a manual database upgrade process is needed, which requires:

1. Separate database storage directories for the old and new versions
2. Access to the PostgreSQL binaries of the old and new versions

The Docker PostgreSQL image did neither of these requirements (storage directories are now separated as of version 18), so most “how to upgrade a PostgreSQL Docker image” just say “export the database, fully rebuild the container, restore the database”, but I wanted to perform a proper upgrade (and document it so I could do it again later).

Upgrade process

In the following commands:

• pg_data is the Docker volume containing the PostgreSQL data
• OLD is the old PostgreSQL version, NEW is the new one
• Replace podman with docker if that’s what you are using

(The upgrade process assumes the database install user is postgres; if it is not, use the -U option to pass the correct user to pg_upgrade)

1. (17->18 migration only) Modify the docker-compose configuration/deploy script so that the PostgreSQL data volume is mounted to /var/lib/postgresql/ instead of /var/lib/postgresql/data/.
2. Shut down the previous postgres container. Export the data storage volume, just to be sure.
3. Start an ephemeral container to perform the migration (replace pg_data with the PostgreSQL data volume):

$ podman run --rm -ti -v pg_data:/var/lib/postgresql/ docker.io/postgres:NEW \
    /bin/bash

4. Install the previous version of PostgreSQL:

# apt update && apt --no-install-recommends -y install postgresql-OLD

3. Switch to postgresql user for the next commands (unset HISTFILE to avoid creating a .bash_history file)

# su - postgres
$ unset HISTFILE

4. (17->18 migration only) Move all PostgreSQL data files into a <version>/docker subfolder:

$ mkdir -m 700 /var/lib/postgresql/17/ /var/lib/postgresql/17/docker/
$ shopt -s extglob
$ mv /var/lib/postgresql/!(17) /var/lib/postgresql/17/docker/

5. Initialize the new database, keep the old configuration

$ /usr/lib/postgresql/NEW/bin/initdb --no-data-checksums \
    /var/lib/postgresql/NEW/docker/
$ cp -p /var/lib/postgresql/OLD/docker/*.conf /var/lib/postgresql/NEW/docker/

6. Perform the migration

$ /usr/lib/postgresql/NEW/bin/pg_upgrade -b /usr/lib/postgresql/OLD/bin/ \
    -d /var/lib/postgresql/OLD/docker/ -D /var/lib/postgresql/NEW/docker/

7. Exit the temporary container (press Ctrl-D twice)
8. Start the upgraded container normally (with docker-compose, etc). Check that everything works.
9. Open a shell to the running container:

$ podman exec -ti -u postgres -e HOME=/tmp <container name> /bin/bash
$ unset HISTFILE

10. Cleanup the old database

$ /usr/lib/postgresql/NEW/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
$ /usr/lib/postgresql/NEW/bin/vacuumdb --all --analyze-in-stages
$ /var/lib/postgresql/delete_old_cluster.sh 
$ rmdir /var/lib/postgresql/OLD
$ rm /var/lib/postgresql/delete_old_cluster.sh 

Done!

Note that data checksums (disabled by default in PostgreSQL 17 but enabled by default in PostgreSQL 18) are not enabled by this method. To enable them, run /usr/lib/postgresql/NEW/bin/pg_checksums -e /var/lib/postgresql/NEW/docker/. When creating the new database during the next upgrade, do not pass the --no-data-checksums option.

Upgrade check

Since upgrading between major PostgreSQL versions require manual intervention, I modified my Docker upgrade script to (1) only upgrade PostgreSQL to a fixed major version, (2) notify me if a newer version is available (i.e. docker.io/postgres:latest is different from docker.io/postgres:<specified version>).

PG_VER=17

podman pull -q "docker.io/postgres:$PG_VER" >/dev/null
podman pull -q "docker.io/postgres:latest" >/dev/null

pg_latest_id="$(podman image inspect "docker.io/postgres:latest" --format "{{.Id}}")" 
pg_cur_id="$(podman image inspect "docker.io/postgres:$PG_VER" --format "{{.Id}}")"

if [ "${pg_latest_id}" != "${pg_cur_id}" ]; then
	echo ""
	echo "** PostgreSQL major version changed, perform a manual upgrade and change PG_VER"
	echo ""
fi

# Rebuild PostgreSQL container using docker.io/postgres:$PG_VER
# Rebuild other containers…