Postgres Upgrades

Our self-hosted workloads include multiple Postgres databases that need to be upgraded occasionally. Upgrades between minor versions are trivial and just require the container image to be upgraded (usually via Renovate) but major version upgrades require manual actions, described below.

The actual process isn’t really a migration between versions; rather, we take a full backup of the old version and restore it into the new version. Performing a true “migration” requires having the old and new binaries available at the same time, which is hard to do with the official Postgres container images.

Assumptions

The steps below make some assumptions about paths, environment variables, etc. These should hold true for all deployed databases but are worth verifying. The main assumptions are the following:

  • The Postgres container has its persistent data volume mounted at /data.

  • The data volume has enough capacity to store the current DB, a compressed dump of it, and a new DB. If this is going to be hard, you could create separate volumes and moung them at different paths - you’ll need to adjust the paths in the steps below if you do this.

  • $PGDATA is pointing at a major-version-specific subfolder of /data, e.g. $PGDATA=/data/15 if the container is running Postgres 15.x.

  • Envionronment variables exist for the user and database name, i.e. $POSTGRES_USER and $POSTGRES_DB.

Steps

  1. Prepare

    1. Stop any workloads that connect to the database. This ensures that nothing tries to make changes to the database while we’re in the middle of upgrading it.

    2. Check that recent backups have been made of the database.

  2. Dump the database contents

    1. Connect to the current database container (using kubectl -n <namespace> exec -it <postgres-pod> -- bash) and run the following commands to create a full dump of the database contents.

    pg_dump -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" -c --if-exists -F c > /data/dump

  3. Start the new database

    1. Update the Kubernetes manifest to change the Postgres image version.

    2. Update the $PGDATA path for the container to the new major version (e.g. from /data/15 to /data/16).

    3. Comment out the health checks in the manifes, because they will fail until the restore finishes.

    4. Deploy the new manifest and wait for the pod to be started and healthy before continuing.

  4. Restore the database contents

    1. Connect to the new database container using the same kubectl command and run the following commands to restore the dump you created earlier.

    pg_restore /data/dump -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"

    1. Run the following commands (still in the Postgres container) to update the DB collation version and perform a full re-index.

    psql -U "${POSTGRES_USER}" -c "ALTER DATABASE ${POSTGRES_DB} REFRESH COLLATION VERSION;"
psql -U "${POSTGRES_USER}" -c "REINDEX DATABASE ${POSTGRES_DB};"

  5. Check everything

    1. Use psql to poke around the DB and check that the tables you expect are there.

    2. Start the workload for this database and check that everything still works

  6. Roll back?

    1. If anything has gone wrong, you can still roll back at this point by undoing your changes to the manifest (image version and data path) and redeploying it. This will get you back to running the old version, pointing at the old data.

    2. When you’re ready to try again, remove the dump file and start again from the top.

  7. Clean up

    1. Restore the health checks that you commented out earlier, deploy the updated manifest, and make sure the health checks succeed.

    2. When you’re certain that everything is working okay (including backups on the new version), you can connect to the container a final time and use the following commands to clean up the temporary files you created:

    rm /data/dump
rm -r /data/<old version>  # be careful here!