by: Helge, published: May 12, 2024, in

Upgrading PostgreSQL in a Docker Container

This article describes the steps necessary to upgrade PostgreSQL to a new major version in a Docker environment. There are many articles on the subject, but I couldn’t find any that were complete, correct, and concise. So I wrote my own.

Info & Planning

Minor and Major Release Upgrades

To update between minor releases, e.g., 16.0 to 16.3, simply replace the PostgreSQL executables by incrementing the Docker image version tag and pulling a new image.

Upgrading to a new major release requires a little more work, though, because the internal storage format is subject to change.

Major Upgrade Process

As described in the documentation, there are multiple ways to perform a major upgrade. We’ll use the following method:

  1. Stop the application container to prevent DB writes.
  2. Create a full DB dump.
  3. Stop the PostgreSQL container.
  4. Rename (backup) the old DB data directory and create a new empty one in its place.
  5. Increment the PostgreSQL version number in your Docker compose file and pull the new image.
  6. Start the updated PostgreSQL container.
  7. Import the DB dump.
  8. Start the application container.
  9. Clean up.

Preparation

  1. Make sure that your application supports the new PostgreSQL version.
  2. Inspect the release notes of the new version you’re upgrading to and the intermediate versions.

Perform the Upgrade

Stop the Application Container

I’m assuming you have a Docker compose file with (at least) one application service and the PostgreSQL service. To stop the application container only, run:

docker compose stop APP_CONTAINER_NAME

Create a Full DB Dump

  1. Look up the name of your PostgreSQL user in your Docker configuration. In my case, it’s simply postgres.
  2. Run the dump command:
    docker exec -it POSTGRESQL_CONTAINER_NAME pg_dumpall -U postgres > dump.sql
    
  3. Inspect the dump file to make sure it looks correct and is not just an error message.

Stop the PostgreSQL Container

docker compose stop POSTGRESQL_CONTAINER_NAME

Backup the DB Data Directory

Back up the bind-mounted DB data directory db and replace it with a new empty folder:

mv db/ db-old/
mkdir db

Increment the PostgreSQL Version

Edit the Docker compose file, incrementing the image version.

Start the Upgraded PostgreSQL Container

Pull the new image and start the PostgreSQL container:

docker compose pull
docker compose up -d POSTGRESQL_CONTAINER_NAME

Verify the logs. Look for errors and make sure that the PostgreSQL version has actually changed:

docker compose logs --tail 100 --timestamps POSTGRESQL_CONTAINER_NAME

Import the DB Dump

docker exec -i POSTGRESQL_CONTAINER_NAME psql -U postgres < dump.sql

As above, optionally replace postgres with the name of your PostgreSQL user.

Start the Application Container

docker compose start APP_CONTAINER_NAME

Verify the logs:

docker compose logs --tail 100 --timestamps

Clean Up

  1. Delete the backup directory: rm -rf db-old/
  2. Delete the dump file: rm dump.sql
  3. Delete the old PostgreSQL image: docker image prune -a

Tips

Shell Access in the PostgreSQL Container

Run the following to get an interactive Bash shell in the PostgreSQL container:

docker exec -it POSTGRESQL_CONTAINER_NAME bash

Tested Scenarios

  • Upgrade of PostgreSQL 15.6 to 16.3

Previous Article LogQL: A Primer on Querying Loki from Grafana