Upgrading Postgresql deployed on Docker Swarm

I have deployed an app in staging on Docker Swarm, and it uses a postgresql database, using the Docker image with version 15.4. With Postgresql 16.0 published recently, here’s how I upgraded it.

The setup

I’m using a one-node Docker Swarm, but I suspect this should apply to multi-nodes as well. The postgresql container of the stack is pinned to a specific node, and is mounting the host directory /data/myowndb/db at /var/lib/postgresql/data for data persistence.

Here’s an excerpt of the yaml specifying the postgresql container:

  postgres:
    image: postgres:15.4
    environment:
      POSTGRES_PASSWORD: welcome
    volumes:
      - /data/myowndb/db/:/var/lib/postgresql/data
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints:
          - node.hostname == vm5bG3

In the same stack, a container is running the web app.

Upgrading

I can afford to stop the app, so I will upgrade my postgresql by taking a dump and loading it in the new version. The postgresql server will not start if it detect that its data directory holds files from another postgres version, and the postgresql container will initialise a new database if it finds its data directory empty. So the upgrade procedure will consist in:

start the container of the new postgres version with an empty data directory
Load a dump from the previous version in this new version

Stopping the app

I start by stopping the app to prevent data changes during the upgrade. I do this by scaling the app container to 0:

docker service scale myowndb_app=0 myowndb_postgres=1

Taking the dump

I take the dump from inside the postgresql container. This places the dump in the postgresql data directory, which is mounted from the host.

docker exec -it $(docker ps -q -f name=myowndb_postgres) bash -c "pg_dumpall -U postgres | gzip -c > /var/lib/postgresql/data/dump_all.sql.gz

Stopping postgresql

As we will work on the database' files directly, stop the postgres container

docker service scale myowndb_postgres=0

Working on the host

On the host, we move the postgresql data directory and create a new empty one, with the right owner (uid might differ according to your installation).

$ ssh $DOCKER_HOST
$ sudo su
# cd /data/myowndb
# mv db db.old
## Check and set ownsership as previous directory
# ls -ldn db
drwxr-xr-x 2 999 999 4096 Sep 25 14:41 db
# chown 999:999 db

Keep the directory empty! Otherwise no initialisation will take place….

Update postgresql version used in the stack

In the stack yaml (excerpt above), replace version 15.4 by 16.0 and deploy it:

DOMAIN=preview.myowndb.net docker stack deploy -c compose-myowndb.yml myowndb

This does restart the db and app container as I have replicas set to one in the yaml…. I left it as is because the app doesn’t start if it cannot connect to the database.

Watch logs of postgres container

docker service logs myowndb_postgres -f

Logs should start with

    | The files belonging to this database system will be owned by user "postgres".
    | This user must also own the server process.
    |
    | The database cluster will be initialized with locale "en_US.utf8".
    | The default database encoding has accordingly been set to "UTF8".
    | The default text search configuration will be set to "english".

This confirms a new database is being initialised.

Make dump available

We placed the dump in the old postgres data directory, so copy it to the new postgres data directory on the host:

ssh $DOCKER_HOST
sudo cp /data/myowndb/db.old/dump_all.sql.gz /data/myowndb/db/

Load dump in new postgres version

$ docker exec -it $(docker ps -q -f name=myowndb_postgres) bash
root@3cbd3e3b4e5d:/# gunzip -c /var/lib/postgresql/data/dump_all.sql.gz | psql -U postgres

Watch the postgres container log for any error. If it was successfull, restart the app if needed

docker service scale myowndb_app=1

It should run fine on the new version.

Some tech notes

Upgrading postgres on docker swarm