Upgrading Postgres a major version inside Docker Swarm

The problem

Many small production estates run Postgres as a Docker Swarm service: one replica, constrained to one node, its data directory bind-mounted from the host, in the same stack as the application, a worker, Redis and nginx. A managed database offers an upgrade button. This setup does not.

Postgres stores its data in an on-disk format tied to the major version. A Postgres 17 server refuses to start on a data directory written by Postgres 14, with a message that the database files are incompatible with the server. Bumping the image tag therefore gives you a database that will not start.

There are three ways across:

• pg_upgrade converts in place and is fast, but needs the old and new binaries side by side. The official images ship one version each, so you build a special image for a one-off job.
• Logical replication to a new server gets close to zero downtime, but it is a project of its own: publications, sequence syncing, a cutover.
• Dump and restore: dump with the old server, start the new one on an empty directory, load the dump. Downtime grows with database size.

For databases of a few gigabytes, which covers most application databases on a single Swarm node, dump and restore is the simplest route to get right. It also rebuilds every index on load, which sidesteps collation problems when the new image ships a different C library.

The pattern

Change the pipeline first. Take a full logical backup. Stop the database, keep the old data directory as the rollback, start the new major on an empty directory, restore, verify, and only later delete the old directory.

We used this in March 2026 to move a clinical application from Postgres 14 to 17 on staging and two UAT environments in one day. Each move ran in a short planned window, with the application scaled down briefly while the database was out.

0. Before you start

• Audit every client of pg_dump. pg_dump refuses to dump a server newer than itself. Backup jobs, export scripts and any service that shells out to the Postgres client tools need a client at least as new as the new server. See the traps below.
• Check disk space. The old data directory, the dump and the new data directory exist at once. Plan for about three times the database size, plus headroom.
• List what connects. Application, worker, cron jobs. Decide whether to scale them down for the window (we did) or let them retry.
• Rehearse on a copy. Dump and restore is offline. A timed rehearsal tells you how long the window needs to be.

1. Pipeline first, held back

Build and push a Postgres image the same way as your other services, with the major version pinned in your own Dockerfile (FROM postgres:17), so the upgrade arrives as a reviewed change and never from a floating tag.

An illustration of the build step and the matching deploy line:

- name: Build and push the database image
  run: |
    docker build -f docker/postgres/Dockerfile -t "$REGISTRY/postgres:$ENV_TAG" .
    docker push "$REGISTRY/postgres:$ENV_TAG"

# deploy.sh, one line per service
# docker service update --with-registry-auth \
#   --image "$REGISTRY/postgres:$ENV_TAG" app_postgres

Order matters. If your deploy force-updates every service on each release, the first deploy after this change will try to start Postgres 17 on the Postgres 14 directory. It fails safe, but you lose the running old server you need for the dump. Take the dump (step 2) before that deploy runs, or keep the database line out of the deploy until the migration is done.

2. Take a full logical backup with the old server

# On the node that holds the data
docker ps --filter name=app_postgres --format '{{.ID}}'

# All databases, roles and grants in one file
docker exec <pg_container> pg_dumpall -U postgres > ~/full_backup.sql
ls -lh ~/full_backup.sql
docker exec <pg_container> psql -U postgres -d <database> -c '\dt'

Use pg_dumpall. Plain pg_dump exports one database and leaves out roles, their passwords and the grants that reference them, so after a pg_dump-only restore your application user may not exist.

We also recommend recording what a correct result looks like before you touch anything. An exact row count per table, saved to a file, gives you something to compare against after the restore:

docker exec <pg_container> psql -U postgres -d <database> -tA -c "
  SELECT format('SELECT %L, count(*) FROM %I.%I;', schemaname||'.'||relname, schemaname, relname)
  FROM pg_stat_user_tables ORDER BY 1" \
| docker exec -i <pg_container> psql -U postgres -d <database> -tA > ~/counts_before.txt

3. Stop the database, and the app for the window

docker service scale app_web=0 app_worker=0 app_postgres=0

Scaling the application and worker down with the database keeps users away from a half-restored database and stops background jobs from writing into it. If you leave them up, expect connection errors until step 6 and read them carefully afterwards.

4. Keep the old cluster, start clean

# Move it aside: this directory is your rollback
sudo mv /var/lib/postgresql/data /var/lib/postgresql/data_old
sudo mkdir -p /var/lib/postgresql/data

Use mv here, never rm. Until the new server is verified, the old directory is the only copy of the database in its original form.

5. New image, one replica, check the version

docker service update --with-registry-auth --image "$REGISTRY/postgres:<new_tag>" app_postgres
docker service scale app_postgres=1
sudo cat /var/lib/postgresql/data/PG_VERSION    # should print the new major, e.g. 17

PG_VERSION confirms in one line that the new major initialized the empty directory. If it prints the old number, the service did not pick up the new image, or it mounted a different directory than you expect.

6. Restore

# The container id changed after the rescale: look it up again
docker exec -i <new_pg_container> psql -U postgres < ~/full_backup.sql

Expect role "postgres" already exists, because the new cluster created its superuser on first start. That one is harmless. Read every other error before moving on.

7. Verify, then bring the app back

docker exec <new_pg_container> psql -U postgres -c 'SELECT version();' -c '\l'
docker exec <new_pg_container> vacuumdb -U postgres --all --analyze-in-stages
docker service scale app_web=1 app_worker=1

Check the version and the database list. We recommend running the row-count query from step 2 again into counts_after.txt and diffing the two files, and refreshing planner statistics with vacuumdb --analyze-in-stages, because a logical restore does not carry them over. Then bring the application back and exercise it: sign in, read, write.

8. Rollback, if you need it

docker service scale app_web=0 app_worker=0 app_postgres=0
sudo rm -rf /var/lib/postgresql/data
sudo mv /var/lib/postgresql/data_old /var/lib/postgresql/data
docker service update --with-registry-auth --image "$REGISTRY/postgres:<old_tag>" app_postgres
docker service scale app_postgres=1 app_web=1 app_worker=1

Revert the Dockerfile change at the same time, or the next deploy will undo the rollback. Keep data_old until the application has run on the new version long enough that you would no longer go back, then delete it and the dump on purpose.

The traps

Client tools older than the server. Every image that runs pg_dump needs a client at least as new as the server, and each base image has its own answer. On Debian or Ubuntu, add the PostgreSQL project's apt repository:

RUN apt-get update && apt-get install -y curl ca-certificates gnupg \
 && curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
    | gpg --dearmor -o /usr/share/keyrings/pgdg.gpg \
 && echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt bookworm-pgdg main" \
    > /etc/apt/sources.list.d/pgdg.list \
 && apt-get update && apt-get install -y postgresql-client-17

On Alpine, the client you need may exist only on the edge branch. Adding edge works, but every later apk command then sees a rolling branch, so any image built that way can pick up unpinned packages. Next time we would choose a stable Alpine release that carries the client, or a Debian base for the images that need it.

A stateful service in a "force update everything" loop. Many Swarm deploy scripts run docker service update --force on every service on every release. For the database that means a restart per deploy, and it is how step 1's ordering problem happens. Consider deploying the database service only when its image changes.

A copied service line that names the wrong service. Adding the database to a deploy script usually means copying another service's line. If the service name at the end is not changed, the next deploy puts Postgres into some other service. We recommend a check after each deploy that lists the image each service runs:

for s in app_web app_nginx app_worker app_redis app_postgres; do
  printf '%-14s %s\n' "$s" "$(docker service inspect --format '{{.Spec.TaskTemplate.ContainerSpec.Image}}' "$s")"
done

Placement. A bind-mounted data directory exists on one node. The service must be constrained to that node, or a rescale can start an empty Postgres elsewhere. Check the constraint before step 3.

The data path in newer images. From Postgres 18 the official image changed its default data location to a version-named directory under /var/lib/postgresql. If your mount targets /var/lib/postgresql/data, read the image's release notes before the next upgrade.

Extensions. The new image must provide every extension the old database used, at versions built for the new major. The restore will report a missing one, but it is better to know before the window.

We used this in Infrastructure for a medical-device company's clinical platform.