Upgrade from PostgreSQL 16.x to 17.0 in Docker Compose

Upgrading your PostgreSQL database from version 16.x to 17.0, especially within a Docker environment, is a crucial step to ensuring your system benefits from the latest features, performance enhancements, and security fixes. However, as with any major version upgrade, careful planning is necessary to avoid loss to your database data.

The starting point

To cut the chase, we jump straight to code, Here is a PostgreSQL 16.4 docker-compose.yaml

services:
  postgres:
    build:
      context: .
      dockerfile: ./docker/postgres/Dockerfile
    image: tojema-rest-postgres:latest
    pull_policy: build
    container_name: tojema-rest-postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      PGUSER: ${POSTGRES_USER}
      PGDATABASE: ${POSTGRES_DB}
    ports:
      - 127.0.0.1:${POSTGRES_PORT:-5432}:5432
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./docker/postgres/entrypoint-initdb.d:/docker-entrypoint-initdb.d/
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 10s
      timeout: 5s
      retries: 10
    networks:
      - private_network

volumes:
  pgdata:
    name: tojema_rest_pgdata

networks:
  private_network:
    name: tojema_rest_network

And here a Dockerfile example for PostgreSQL 16.x

FROM postgres:16.4-bookworm

RUN localedef -i en_GB -c -f UTF-8 -A /usr/share/locale/locale.alias en_GB.UTF-8
ENV LANG=en_GB.utf8

RUN apt update -yqq --no-install-recommends && \
    apt install curl ca-certificates -yqq --no-install-recommends && \
    rm -rf /var/lib/apt/lists/*

# <project>/docker/postgres/Dockerfile

The problem

if we try to upgrade the version in 1st line from 16.4-bookworm to 17.0-bookworm, we get the following clear error

PostgreSQL 17.0 error after detecting Data from PostgreSQL 16.x

Solution

A golden advice to always keep in mind, “Create backup!”.

To migrate your data to PostegreSQL 17.0 along with safe backup, checkout the updated docker-compose.yaml file below

services:
  postgres:
    build:
      context: .
      dockerfile: ./docker/postgres/Dockerfile
    image: tojema-rest-postgres:latest
    pull_policy: build
    container_name: tojema-rest-postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      PGUSER: ${POSTGRES_USER}
      PGDATABASE: ${POSTGRES_DB}
    ports:
      - 127.0.0.1:${POSTGRES_PORT:-5432}:5432
    volumes:
      - pg17data:/var/lib/postgresql/data
      - ./docker/postgres/entrypoint-initdb.d:/docker-entrypoint-initdb.d/
      - pgbackup:/pgbackup
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 10s
      timeout: 5s
      retries: 10
    networks:
      - private_network

  postgres-backup:
    build:
      context: .
      dockerfile: ./docker/postgres/Dockerfile.pg16
    image: tojema-rest-postgres-backup:latest
    pull_policy: build
    container_name: tojema-rest-postgres-backup
    restart: "no"
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      PGUSER: ${POSTGRES_USER}
      PGDATABASE: ${POSTGRES_DB}
    profiles:
      - pgbackup
    ports:
      - 127.0.0.1:${POSTGRES_PORT:-5432}:5432
    volumes:
      - pgdata:/var/lib/postgresql/data
      - pgbackup:/pgbackup
      - ./docker/postgres/scripts:/pgscripts
    networks:
      - private_network

volumes:
  pgdata:
    name: tojema_rest_pgdata
  pg17data:
    name: tojema_rest_pg17data
  pgbackup:
    name: tojema_rest_pgbackup

networks:
  private_network:
    name: tojema_rest_network

Highlighting the steps and worth-noting changes:

1. Make sure all your services are shutdown using docker compose down --remove-orphans
2. Rename your Dockerfile to Dockerfile.pg16.
3. Create a postgres-backup service that uses Dockerfile.pg16.
4. Put the service into separate profile to prevent it from starting automatically after docker compose up.
5. Mount a backup volume pgbackup into postgres-backup.
6. Create a new Dockerfile similar to the old one with the upgraded version, that is, replace FROM postgres:16.4-bookworm with FROM postgres:17.0-bookworm
7. Keep pgdata volume for postgres-backup service, and create a new volume similar to it for the new postgres service, in my example I named it pg17data, this new volume will hold the migrated data and will be the primary volume, while pgdata will not be modified and (at your choice) you can delete it anytime in the future.
8. copy the backup.sh script below into your project, the last line comment tells where the file should be put.
9. the scripts folder is then mounted into the postgres-backup container

PGBK_FILE=pgbk-"`date +"%FT%T"`".sql
pg_dumpall -U postgres > /pgbackup/$PGBK_FILE
ln -sf /pgbackup/$PGBK_FILE /pgbackup/pgbk-latest.sql

# <project>/docker/postgres/scripts/backup.sh

Backup

If everything is set correctly for you, you can move to the phase now where we create backup

# shutdown all running services to prevent unexpected errors
docker compose down --remove-orphans

# run backup container (uses PostgreSQL 16)
docker compose up -d postgres-backup

# generates pgbk sql file in pgbackup volume
docker compose exec postgres-backup bash /pgscripts/backup.sh

# just to display generated files
docker compose exec postgres-backup ls -alh /pgbackup

The last command should result in an output similar to this

pgbk-latest.sql is a symbolic link pointing towards the latest backup file.

Make sure pgbk-latest.sql is generated, then shutdown backup service

docker compose down postgres-backup

Restore

Now we can run postgres container and import the backup file

# uses PostgreSQL 17
docker compose up postgres -d

# import the backup file
docker compose exec postgres psql -U postgres -f /pgbackup/pgbk-latest.sql

After this, data should be imported successfully into your PostgreSQL 17 instance.

Originally published on Medium.