Upgrading PostgreSQL: A Step-by-Step Guide with Docker

A crucial task that database administrators often face is upgrading databases to a newer version. PostgreSQL, being a powerful and highly scalable database system, is no exception to this. This article walks through that process with Docker.

The original move was PostgreSQL 9.4 to 13. Those versions are now unsupported, but the useful idea remains: never start a new major PostgreSQL image against an old data directory. Major upgrades need dump/restore or pg_upgrade. PostgreSQL version policy and upgrade documentation

I stop writers and take a volume or storage snapshot before beginning. The old volume stays untouched until the application and restored data have been verified.

Method 1: Dump and Restore

This is still my default because it follows the same simple steps as the original 9.4-to-13 move: run the old version, export the database, initialize a separate directory with the new version, import, then point Compose at the new cluster. It is easy to understand, portable across platforms, and gives the new cluster a clean layout. Replace OLD_IMAGE and NEW_IMAGE with supported, pinned major versions.

export OLD_IMAGE=postgres:16
export NEW_IMAGE=postgres:18
export OLD_VOLUME=existing-postgres-data
export NEW_VOLUME=postgres-18-data
docker volume inspect "$OLD_VOLUME"
docker volume create "$NEW_VOLUME"

With the old container running and application writes stopped, dump roles/tablespaces and the database separately:

docker exec pg-old pg_dumpall -U postgres --globals-only > globals.sql
docker exec pg-old pg_dump -U postgres -Fc --no-owner --file=/tmp/app.dump appdb
docker cp pg-old:/tmp/app.dump ./app.dump

Start the new major version on its own empty volume:

docker run -d --name pg-new \
  -e POSTGRES_USER=upgrade_admin \
  -e POSTGRES_PASSWORD='replace-for-local-bootstrap' \
  -v "$NEW_VOLUME":/var/lib/postgresql \
  "$NEW_IMAGE"
until docker exec pg-new pg_isready -U upgrade_admin; do sleep 1; done

The mount target matters here: the official PostgreSQL 18 image moved its volume to /var/lib/postgresql and uses a major-version subdirectory. Versions 17 and earlier use /var/lib/postgresql/data. I keep the old container’s existing mount unchanged and use the new target only for the PostgreSQL 18 volume. Docker Official Image PGDATA note

Restore globals, create the database, and restore the custom-format dump:

docker exec -i pg-new psql -U upgrade_admin -v ON_ERROR_STOP=1 < globals.sql
docker exec pg-new createdb -U upgrade_admin -O appuser appdb
docker cp app.dump pg-new:/tmp/app.dump
docker exec pg-new pg_restore -U upgrade_admin -d appdb \
  --exit-on-error --no-owner /tmp/app.dump

I then verify connectivity and data before pointing the application at the new container:

docker exec pg-new pg_isready -U upgrade_admin -d appdb
docker exec pg-new psql -U upgrade_admin -d appdb -c '\\dx'
docker exec pg-new psql -U upgrade_admin -d appdb -c \
  "SELECT schemaname, relname, n_live_tup FROM pg_stat_user_tables ORDER BY 1,2;"

The rollback is simple: stop the new container and start the old image with pg-old; do not reuse pg-new as the old server’s data directory.

Method 2: pg_upgrade for a Large Database

pg_upgrade is faster because it upgrades the physical cluster instead of logically rewriting every row. It is less forgiving: old and new server binaries must be available together, both clusters must use compatible build options, and every required extension must exist for the new major version.

I run the preflight before the real upgrade:

pg_upgrade \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --check

After resolving every reported extension or compatibility problem, I run the same command without --check. I use --link only when I accept that starting the old cluster after the upgrade can make rollback impossible; copy mode costs more disk but preserves that boundary. PostgreSQL’s pg_upgrade documentation lists the exact preparation and post-upgrade analysis steps.

For my smaller databases, dump/restore remains the easiest method. For a large cluster with a tested snapshot rollback, pg_upgrade can reduce downtime substantially.