Major Version Upgrades for PostgreSQL Docker Containers

I am running Umami as my analytics service. This needs a PostgreSQL database.

Recently I learned that the official PostgreSQL Docker container is not able to do upgrades automatically when bumping the major version (from 12.16 to 13.12 for example). What a bummer for a lazy guy like me.

Docker Compose Setup

Here is my setup for upgrading PostgreSQL with Docker Compose.

I have the Umami container (or whatever workload) and a specific PostgreSQL container for each major version in my docker-compose.yaml. Each major PostgreSQL container will have its own folder for data. You can also use docker volumes. I just prefer the folders.

version: '3'

services:
  umami:
    container_name: umami
    image: ghcr.io/umami-software/umami:postgresql-v2.5.0
    environment:
      DATABASE_URL: postgresql://umami:${UMAMI_POSTGRES_PASSWORD}@umami-db-12/umami
      DATABASE_TYPE: postgresql
      HASH_SALT: "${UMAMI_HASH_SALT}"
    depends_on: 
      - umami-db-12
    restart: unless-stopped

  umami-db-12:
    container_name: umami-db-12
    image: postgres:12.16-alpine
    environment:
      POSTGRES_DB: umami
      POSTGRES_USER: umami
      POSTGRES_PASSWORD: "${UMAMI_POSTGRES_PASSWORD}"
   volumes:
     - ./umami-db-12:/var/lib/postgresql/data
   restart: unless-stopped

  umami-db-13:
    container_name: umami-db-13
    image: postgres:13.12-alpine
    environment:
      POSTGRES_DB: umami
      POSTGRES_USER: umami
      POSTGRES_PASSWORD: "${UMAMI_POSTGRES_PASSWORD}"
    volumes:
      - ./umami-db-13:/var/lib/postgresql/data
    restart: unless-stopped

As you can see, my umami container has the connection string to and also the depends-on field set to the service umami-db-12.

Upgrade procedure

Here the upgrade procedure for major releases. For patch/minor in PostgreSQL (12.15 to 12.16) you can just bump the container image tag.

Stop the workload

I will stop the workload that is using the database by just commenting out the service and run docker-compose up -d again. That way we can create a clean backup in the next step.

Create a PostgreSQL backup

To create the backup we are using pg_dumpall:

docker exec -it umami-db-12 pg_dumpall -U umami > upgrade_backup_12.sql

Double-check the file with less and see if this looks like SQL. If not fix your issues like permissions, etc.

Import the backup to the new major version PostgreSQL container

cat upgrade_backup_12.sql | docker exec -i umami-db-13 psql -U umami

This will run the SQL statement in the backup file on the new major version container.

Switch database for your workload

Switch the connection string to the new PostgreSQL instance and update the depends-on field.

services:
  umami:
    container_name: umami
    image: ghcr.io/umami-software/umami:postgresql-v2.5.0
    environment:
      DATABASE_URL: postgresql://umami:${UMAMI_POSTGRES_PASSWORD}@umami-db-13/umami
      DATABASE_TYPE: postgresql
      HASH_SALT: "${UMAMI_HASH_SALT}"
    depends_on: 
      - umami-db-13
    restart: unless-stopped

You should also comment out the old PostgreSQL service umami-db-12. That way you can make sure that Umami (your workload) is using the new database for sure since the old one will be offline.

For that you can use the command docker-compose up -d --remove-orphans. This will remove the commented out containers.

Check your workload

Just check if everything is working fine for your workload. Check logs and the application itself.

If everything works just repeat this process.

Upgrade issues for Umami from PostgreSQL 13 to 14

I for example had to upgrade from PostgreSQL 12 to 15. And from 13 to 14 I had this error:

umami          | yarn run v1.22.19
umami          | $ npm-run-all check-db update-tracker start-server
umami          | $ node scripts/check-db.js
umami          | ✓ DATABASE_URL is defined.
umami-db-14    | 2023-10-03 07:58:53.769 UTC [135] FATAL:  password authentication failed for user "umami"
umami-db-14    | 2023-10-03 07:58:53.769 UTC [135] DETAIL:  User "umami" does not have a valid SCRAM secret.
umami-db-14    | 	Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
umami          | ✗ Unable to connect to the database.
umami          | error Command failed with exit code 1.
umami          | info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
umami          | ERROR: "check-db" exited with 1.
umami          | error Command failed with exit code 1.
umami          | info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

Looks like there was a change in the hashing for the user password in PostgreSQL. Found a good guide on how to change the umami password here:

How to Upgrade Your PostgreSQL Passwords to SCRAM

A how to guide for upgrading your PostgreSQL passwords to use SCRAM (SCRAM-SHA-256) for authentication instead of MD5.

Crunchy DataJonathan S. Katz

You might encounter such things between major versions. Just search for those problems and you will be good. And if you do not feel like it or do not have time you can always use your old database container and do the upgrade later.

Let me know in the comments if this made your PostgreSQL upgrades a bit easier. If you have an even better approach let me know as well!

❤️ Enjoy the content?

Forward to a friend and let them know where they can subscribe (it is here).

Have a great day!