Using this with PostGIS / extensions

[spwoodcock]

Following on from #45, which has some outdated info now.

Upgrading Postgres with PostGIS Extension

• The shutdown issues described I think are fixed now, but the original postgres container must obviously be shut down prior to running the upgrade.

• The dependencies for the extension you require must be installed in an extended Dockerfile:

  FROM tianon/postgres-upgrade:14-to-16
  
  RUN set -ex \
      && apt-get update \
      && DEBIAN_FRONTEND=noninteractive apt-get install \
      -y --no-install-recommends \
          "postgresql-14-postgis-3" \
          "postgresql-16-postgis-3" \
      && rm -rf /var/lib/apt/lists/*

  Save this image as pg-14-to-16 for later.

• If you are using a database user other than postgres, this must be specified in two environment variables:

  • PGUSER: ${POSTGRES_USER} is required, otherwise failed: FATAL: role "postgres" does not exist.
  • POSTGRES_INITDB_ARGS: -U ${POSTGRES_USER} otherwise connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL: role "xxx" does not exist

• The pg_hba.conf is replaced with a new one, so bear this in mind you probably need to replace with the original.

Example

• I made a docker-compose.yml that handles the upgrade process, in addition to a bash script to orchestrate it.
• I run this on each deploy of my app (I'm sure there are more efficient ways!)

contrib/pg-upgrade/docker-compose.yml

# The services run in sequential order, via depends_on

volumes:
  pg-14-data:
    name: pg-14-data
  pg-16-data:
    external: true
    name: pg-16-data

services:
  # Check if the upgrade has already be complete --> v16
  db-check-upgrade:
    image: postgis/postgis:16-3.4
    volumes:
      - pg-16-data:/var/lib/postgresql/data
    restart: "no"
    entrypoint: /bin/sh -c
    command:
      - |
        # The new database directory is empty, so continue upgrade
        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-version:
    image: pg-14-to-16
    build: contrib/pg-upgrade
    depends_on:
      db-to-volume:
        condition: service_completed_successfully
    volumes:
      - pg-14-data:/var/lib/postgresql/14/data
      # Volume defined in main docker-compose.yml
      - pg-16-data:/var/lib/postgresql/16/data
    env_file: .env
    environment:
      PGUSER: ${POSTGRES_USER}
      POSTGRES_INITDB_ARGS: -U ${POSTGRES_USER}
    restart: "no"

  # Replace the generated pg_hba.conf access file with the original
  db-config-hba:
    image: postgis/postgis:16-3.4
    depends_on:
      db-upgrade-version:
        condition: service_completed_successfully
    volumes:
      - pg-14-data:/var/lib/postgresql/14/data
      - pg-16-data:/var/lib/postgresql/16/data
    restart: "no"
    entrypoint: /bin/sh -c
    command:
      - |
        cp -f \
          /var/lib/postgresql/14/data/pg_hba.conf \
          /var/lib/postgresql/16/data/
        echo 'Copied pg_hba.conf to new postgres dir'

  # Start the db so we can run maintenance tasks
  db-startup:
    image: postgis/postgis:16-3.4
    depends_on:
      db-config-hba:
        condition: service_completed_successfully
    volumes:
      - pg-16-data:/var/lib/postgresql/data
    env_file: .env
    networks:
      - dtm-network
    restart: unless-stopped
    healthcheck:
      test: pg_isready -U ${POSTGRES_USER:-dtm} -d ${POSTGRES_DB:-dtm_db}
      start_period: 5s
      interval: 10s
      timeout: 5s
      retries: 3

  # Run maintenance, db vacuum
  db-upgrade:
    image: postgis/postgis:16-3.4
    depends_on:
      db-startup:
        condition: service_healthy
    env_file: .env
    networks:
      - dtm-network
    restart: "no"
    entrypoint: /bin/sh -c
    command:
      - |
        PGPASSWORD=${POSTGRES_PASSWORD} \
        vacuumdb \
        --host=db-startup \
        --username=${POSTGRES_USER} \
        --all \
        --analyze-in-stages

docker-compose.yml

volumes:
  pg-16-data:
    name: pg-16-data

services:
  # If error, please upgrade the db `bash contrib/pg-upgrade/upgrade-db.sh`
  db:
    image: postgis/postgis:16-3.4
    volumes:
      - pg-16-data:/var/lib/postgresql/data
    env_file: .env
    networks:
      - dtm-network
    restart: unless-stopped
    healthcheck:
      test: pg_isready -U ${POSTGRES_USER:-dtm} -d ${POSTGRES_DB:-dtm_db}
      start_period: 5s
      interval: 10s
      timeout: 5s
      retries: 3

Note there is a .env file in the repo root with db variables: POSTGRES_USER, POSTGRES_DB, POSTGRES_PASSWORD, POSTGRES_HOST

contrib/pg-upgrade/upgrade-db.sh:

#!/bin/bash

# Pull upgrade container
docker compose \
    -f docker-compose.yml \
    -f contrib/pg-upgrade/docker-compose.yml \
    pull db-upgrade-version

# Get exit code from db version check
docker compose \
    -f docker-compose.yml \
    -f contrib/pg-upgrade/docker-compose.yml \
    up db-check-upgrade --exit-code-from db-check-upgrade
exit_code=$?

# Exit script if upgrade complete
if [ "$exit_code" -eq 1 ]; then
    echo "Database is already upgraded. Skipping."

    docker compose \
        -f docker-compose.yml \
        -f contrib/pg-upgrade/docker-compose.yml \
        rm --force db-check-upgrade

    exit 0
fi

# Stop any existing locks on db
docker compose \
    -f docker-compose.yml \
    -f contrib/pg-upgrade/docker-compose.yml \
    down

# Do the db upgrade
docker compose \
    -f docker-compose.yml \
    -f contrib/pg-upgrade/docker-compose.yml \
    up -d db-upgrade

# View any logs
docker compose \
    -f docker-compose.yml \
    -f contrib/pg-upgrade/docker-compose.yml \
    logs db-upgrade-version

# Shut down db to prior to restart
docker compose \
    -f docker-compose.yml \
    -f contrib/pg-upgrade/docker-compose.yml \
    down

With this setup we can fully automate a postgres/postgis upgrade.

bash contrib/pg-upgrade/upgrade-db.sh
docker compose up ---detach --pull=always

Note this upgrade is using Debian Bookworm based images.

Avoid attempting to upgrade a alpine-based image from this approach else you may risk data corruption!

Hope this helps someone!