For personal use, I run a lot of service with docker-compose on a single host. In most cases this means having one docker-compose file per service and a reverse proxy on the host for tls termination.
Over time, a number of services started to depend on a quite old version of postgresql - I found two services using postgresql 9.6 for with the last and final version was just released a few days ago.
This means it was time to upgrade the database instances to a later version. I decided to use the latest version, postgresql 14.
In order to not have to lookup how it works, I documented all steps in this blog post.
As an example, I use the miniflux rss reader service
with the following
version: "3.6" services: miniflux: image: miniflux/miniflux:2.0.33 ports: - "127.0.0.1:<high_port_on_host>:8080" depends_on: - db restart: always environment: - DATABASE_URL=postgres://miniflux:<db_password>@db/miniflux?sslmode=disable - RUN_MIGRATIONS=1 - CREATE_ADMIN=1 - BASE_URL=<url> - ROOT_URL=<url> - ADMIN_USERNAME=<admin_user> - ADMIN_PASSWORD=<admin_password> - POLLING_FREQUENCY=10 db: image: postgres:9.6 restart: always environment: - POSTGRES_USER=<user> - POSTGRES_PASSWORD=<password> volumes: - miniflux-db:/var/lib/postgresql/data volumes: miniflux-db: external: true
Upgrading postgresql usually involves taking a backup of the data and restoring it in a fresh database of the desired version. This is exactly what we will do.
First, we need to make sure all services using the database are stopped to prevent onging access to the postgresql instance.
In my case this means stopping the miniflux service:
docker-compose stop miniflux
Taking the backup can then be done using pg_dumpall:
docker-compose exec db pg_dumpall -U miniflux > dump.sql
Now we do not need the old database instance anymore and can stop/remove all container.
The change of the
docker-compose.yml are list below:
db: - image: postgres:9.6 + image: postgres:14 restart: always environment: - POSTGRES_USER=miniflux - POSTGRES_PASSWORD=<db_password> volumes: - - miniflux-db:/var/lib/postgresql/data + - miniflux_postgres_14:/var/lib/postgresql/data volumes: - miniflux-db: - external: true + miniflux_postgres_14: + external: true
I prefer using external volumes to not accidentally remove the volumes by passing
-v flag to the
docker-compose down command. But this means we have to
manually create the new volume:
docker volume create miniflux_postgres_14 docker-compose up -d db
After the database is up, we can restore the data:
cp dump.sql /var/lib/docker/volumes/miniflux_postgres_14/_data/ docker-compose exec db bash root@<container-id>:/# psql -U miniflux -d miniflux < /var/lib/postgresql/data/dump.sql
Unfortunately, we are not finished yet. As described in this blog post, newer versions of postgresql use a different password encryption scheme.
Affected users can be identified using the following sql:
SELECT rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded FROM pg_authid WHERE rolcanlogin;
For all users for which the
has_upgraded column is
f (false), the password
has to be set again. You can use the same password as before.
root@<container-id>:/# psql -U miniflux [..] miniflux=# SELECT rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded FROM pg_authid WHERE rolcanlogin; rolname | has_upgraded ----------+-------------- miniflux | f (1 row) miniflux=# \password Enter new password: Enter it again:
Now we can start all services again and cleanup all backup dumps and unused volumes
docker-compose up -d rm /var/lib/docker/volumes/miniflux_postgres_14/_data/dump.sql docker volume remove miniflux-db
Even though these are some manual steps, I am very happy with how far you can go with a simple docker-compose setup.