Upgrade a Plone RelStorage PostgreSQL Database in Docker Swarm

For over 7 years we are running Plone now on PostgreSQL, primary in a Docker Swarm environment.

Here a short How-To update major versions.

Attention: Always BACKUP FIRST!

Strategy

I name my current running database container in the Docker Stack here db, the new container with the new version is named db-update.

In our stack I have two databases one with the currently running old version and one with the new version of PostgreSQL.

Additional, I have a storage called maintenance mounted to both containers.

Process is:

1. Edit the deployment YAML file and

   • create a new storage i.e. /data/project/pg16

   • create the new service database with the new version as i.e. db-update with the storage above (do not use the current db storage!)

2. deploy the new configuration

3. Stop all Plone containers

4. In the current database container export the database to the maintenance storage

5. In the new database container import the previous export

6. Edit the deployment.yaml,

   • remove the current db service and plus the old storage (also the data on disk)

   • rename the service db-update to db

7. deploy the new configuration

8. DONE

In Practice

First on the server create the new data folder

mkdir -p /data/project/pg16

Then edit the deployment.yaml (or whatever the Swarm deployment is named at your place).

Add volumes:

  # existing
  pgdb16:
    driver_opts:
      type: none
      device: /data/project/pgdb16
      o: bind
  # new
  pgdb16:
    driver_opts:
      type: none
      device: /data/project/pgdb16
      o: bind
  maintenance:
    driver_opts:
      type: none
      device: /data/project/maintenance
      o: bind

and a new service for the new database (here a version 16), i.e. like so:

  # existing
  db:
    image: 'postgres:12'
    command: postgres -c max_locks_per_transaction=1024 -c shared_buffers=512MB -c work_mem=8MB -c max_wal_size=512MB
    environment:
      POSTGRES_USER: plone
      POSTGRES_PASSWORD: plone
      POSTGRES_DB: plone
    networks:
      - backend
    volumes:
      - "pgdb12:/var/lib/postgresql/data"
      - "maintenance:/maintenance"
      - type: tmpfs
        target: /dev/shm
        tmpfs:
          size: 256000000
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2GB
      mode: global
      endpoint_mode: dnsrr
  # new
  db-update:
    image: 'postgres:16'
    command: postgres -c max_locks_per_transaction=1024 -c shared_buffers=512MB -c work_mem=8MB -c max_wal_size=512MB
    environment:
      POSTGRES_USER: plone
      POSTGRES_PASSWORD: plone
      POSTGRES_DB: plone
    networks:
      - backend
    volumes:
      - "pgdb16:/var/lib/postgresql/data"
      - "maintenance:/maintenance"
      - type: tmpfs
        target: /dev/shm
        tmpfs:
          size: 256000000
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2GB
      mode: global
      endpoint_mode: dnsrr

Apply the new configuration, like

docker stack deploy -c deployment.yaml

Now stop the Plone containers docker stop PLONE_CONTAINER_ID (multiple times).

After stopping all Plone containers export the database

docker exec -it CURRENT_DB_CONTAINER_ID pg_dump -u plone -d plone /maintenance/upgrade_export.sql`

And import it in the new version of the database:

docker exec -it UPDATE_DB_CONTAINER_ID cat /maintenance/upgrade_export.sql | psql -u plone -d plone

Then edit the deployment.yaml again,

• remove the whole service db and

• rename the service db-update to db.

• remove the storage pgdb12.

Apply the new configuration, check if all is up and running!

If so, remove rm -rf /data/project/pgdb12 to get rid of the duplicate data. Remove the dump too. Do a docker system prune too.

All done.