Automating The Upgrade

Using 'One-shot' mode

• The pgautoupgrade image can be used to replace an existing postgres image, to allow for easy in-place upgrades.
• However, sometimes it may be preferable to keep the official postgres image, but use pgautoupgrade to do the upgrade.
• One-shot mode via the PGAUTO_ONESHOT environment variable can be used for this purpose.
• For example, often the upgrade needs to be performed as part of a CI/CD flow.

Example docker-compose.yml config

The following docker compose configuration can be used to upgrade an existing database in an idempotent manner, in addition to performing maintenance tasks.

Prerequisite:

• An existing database container, with a populated docker volume named app-db-data (change this as necessary).
• A .env file with variables POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD, if not using the default postgres user/db.
• A reasonably recent version of docker compose V2 CLI.

# The services run in sequential order, via depends_on

volumes:
  db-data:
    external: true
    name: app-db-data

services:
  # Check if the upgrade has already complete --> v16
  db-check-upgrade:
    image: postgres:16-alpine
    volumes:
      - db-data:/var/lib/postgresql/data
    network_mode: none
    restart: no
    entrypoint: /bin/sh -c
    command:
      - |
        # The new database directory is empty, so continue to init
        if [ ! -f "/var/lib/postgresql/data/PG_VERSION" ]; then
          echo "Database is empty"
          exit 0
        fi

        if [ "$(cat /var/lib/postgresql/data/PG_VERSION)" = "16" ]; then
          # The database is already upgraded, skip
          echo "Database already upgraded"
          exit 1
        else
          # The database is not upgraded, continue
          echo "Database not upgraded yet"
          exit 0
        fi

  # Do the db upgrade
  db-upgrade:
    image: pgautoupgrade/pgautoupgrade:16-alpine
    build: .
    depends_on:
      db-check-upgrade:
        condition: service_completed_successfully
    volumes:
      - db-data:/var/lib/postgresql/data
    env_file: .env
    environment:
      PGAUTO_ONESHOT: yes
    network_mode: none
    restart: no

  # Run maintenance tasks
  db-maintenance:
    image: postgres:16-alpine
    depends_on:
      db-upgrade:
        condition: service_completed_successfully
    volumes:
      - db-data:/var/lib/postgresql/data
    env_file: .env
    network_mode: none
    restart: no
    entrypoint: /bin/sh -c
    command:
      - |
        gosu postgres pg_ctl start -D /var/lib/postgresql/data

        # Upgrade an extension
        PGPASSWORD=${POSTGRES_PASSWORD} \
        psql --host=localhost --username=${POSTGRES_USER} \
        ${POSTGRES_DB} -c '
          ALTER EXTENSION "myextension" UPDATE;
        '

        # Rebuild statistics use vacuum
        PGPASSWORD=${POSTGRES_PASSWORD} \
        vacuumdb \
        --host=localhost \
        --username=${POSTGRES_USER} \
        --all \
        --analyze-in-stages

To use the above compose config, run:

docker compose --file docker-compose.yml \
  --env-file .env up --abort-on-container-failure

Example CI/CD workflow

The above example could be run as part of a CI/CD workflow, or bash script:

# Run db upgrade if necessary
docker compose --file contrib/pg-upgrade/docker-compose.yml \
  --env-file .env up || true

# Cleanup db upgrade containers
docker compose --file contrib/pg-upgrade/docker-compose.yml down

# Start the app as normal
docker compose --file docker-compose.yml --env-file .env up \
  --detach --remove-orphans --force-recreate --pull=always