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.
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
if we try to upgrade the version in 1st line from 16.4-bookworm
to 17.0-bookworm
, we get the following clear error
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:
- Make sure all your services are shutdown using
docker compose down --remove-orphans
- Rename your
Dockerfile
toDockerfile.pg16
. - Create a
postgres-backup
service that usesDockerfile.pg16
. - Put the service into separate profile to prevent it from starting automatically after
docker compose up
. - Mount a backup volume
pgbackup
intopostgres-backup
. - Create a new
Dockerfile
similar to the old one with the upgraded version, that is,
replaceFROM postgres:16.4-bookworm
withFROM postgres:17.0-bookworm
- Keep
pgdata
volume forpostgres-backup
service, and create a new volume similar to it for the newpostgres
service, in my example I named itpg17data
, this new volume will hold the migrated data and will be the primary volume, whilepgdata
will not be modified and (at your choice) you can delete it anytime in the future. - copy the
backup.sh
script below into your project, the last line comment tells where the file should be put. - 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
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
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.