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
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.
As you can see, my umami container has the connection string to and also the
depends-on field set to the service
Here the upgrade procedure for major releases. For patch/minor in PostgreSQL (
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
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  FATAL: password authentication failed for user "umami" umami-db-14 | 2023-10-03 07:58:53.769 UTC  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:
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!