Upgrading dockerized postgresql

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 docker-compose.yml:

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.

docker-compose down

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 the -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.

docker