When a successful restore isn't: a Postgresql upgrade

When a successful restore isn't: a Postgresql upgrade

A psql restore streamed an entire dump into a fresh PostgreSQL 17 database, finished, and returned exit code 0. Every signal the tool gives said it worked. The row counts said otherwise.

This happened during a routine PostgreSQL 15 to 17 major-version upgrade on a self-managed Docker Swarm VPS running a Django stack, real infrastructure with real data in a staging environment.

Why dump and restore, not pg_upgrade:

Postgres 17 engine won't start against a version 15 data directory. That forces a choice of a migration method, and I went with a logical dump and restore over in place pg_upgrade because dump and restore rebuilds every index fresh on the new version, which avoids a glibc collation-version mismatch , indexes built under an older collation library can silently sort incorrectly after the swap. It also never touches the source volume, so the data directory sits untouched the entire time, and rollback is a single service update rather than a recovery procedure.

The trade is downtime for the length of the dump plus restore. Acceptable for a database this size. Not a decision I'd make the same way at a few terabytes.

Sequencing the cutover:

Credentials are Docker secrets mounted as files, not environment variables, so every command below reads the database name and user out of /run/secrets/. First, stop writes entirely:

docker service scale app_web=0
docker service ps app_web # to confirm zero running tasks

Then dump version 15 while nothing can write to it:

DBC=$(docker ps -q -f name=app_db | head -n 1)
DB_USER=$(docker exec "$DBC" cat /run/secrets/db_user)
DB_NAME=$(docker exec "$DBC" cat /run/secrets/db_name)
DUMP_FILE="dump_$(date +%Y-%m-%d_%H-%M).sql"

docker exec "$DBC" pg_dump -U "$DB_USER" -d "$DB_NAME" --no-owner --no-privileges > "$DUMP_FILE"

tail -n 2 "$DUMP_FILE"  # must end: -- PostgreSQL database dump complete

That file is now also the backup of record for the whole operation. Then the database service moves to the new image on a new volume, while the web service stays at zero so nothing can migrate into an empty database prematurely:

docker volume create app_pgdata_v17
docker service update \
  --image postgres:17-bookworm@sha256:5c34b355088846dddc8afb7442c20b9433dccdc8d66192dc52c616adeaa106a3\
  --mount-rm /var/lib/postgresql/data \
  --mount-add type=volume,source=app_pgdata_v17,target=/var/lib/postgresql/data \
  app_db

What was actually happening :

My first pass waited a fixed number of seconds after that service update, on the assumption that Postgres 17 would have finished initialising by then, and started the restore. It ran to completion. Exit 0. And the row counts afterward showed only seed data, meaning no data was inserted from the dump file.

The mechanism, from what i found : docker service ps reported the new task as Running, and I treated that as my signal to restore. It isn't the right signal. Running tells you the container process started. It tells you nothing about whether Postgres inside it is actually ready to serve.

That ruled out the obvious suspects fast: it wasn't a bad dump (the file was intact, verified against pg_dump's own completion marker), and it wasn't a wrong-container problem (the restore explicitly targeted the container the service update had just created). What was left was a timing problem between two things that only look synchronous from outside: the container starting, and Postgres inside it becoming ready. On a fresh volume, initdb has to build the cluster from scratch before Postgres is actually up, and my best explanation, consistent with how the official image bootstraps a new volume, is that the restore landed while that initialization was still settling into the serving data directory, so the writes committed against a state that got superseded a moment later. I don't have logs from that exact window to confirm that specific handoff, but I confirmed the diagnosis itself is correct: I dropped the database, reran the identical restore gated on pg_isready instead of a fixed wait, and it held.

A fixed wait cannot fix a race whose duration you don't control and can't observe from outside. What actually closes it is blocking on readiness directly, and re-resolving the container on every check, because the container id itself changes while the Swarm task settles into place:

until [ -n "$NEWDBC" ] && docker exec "$NEWDBC" pg_isready -q; do
    sleep 2
    NEWDBC=$(docker ps -q -f name=app_db | head -n 1) # re-resolve container ID while Swarm reschedules 
done

Polling pg_isready instead of guessing a duration is the obvious half of the fix. Caching the container id once, before the loop starts, is the trap: in an orchestrator like docker swarm that is actively rescheduling the task, the id you captured might be stale by the time the is_ready check finally passes, and you exec a command into a container that Swarm has already replaced.

With that loop in place, the restore runs, and it's told to fail loudly rather than silently half-load:

docker exec -i "$NEWDBC" psql -v ON_ERROR_STOP=1 -U "$DB_USER" -d "$DB_NAME" < "$DUMP_FILE"

Once that happened, exit code 0 stopped being sufficient evidence for anything. Every subsequent run verified real rows before letting the application back online and counted against the dump file itself instead of only exit codes:

# rows for one table, counted straight out of the dump
sed -n '/^COPY public.auth_user /,/^\\\.$/p' "$DUMP_FILE" | sed '1d;$d' | wc -l

NEWDBC=$(docker ps -q -f name=app_db | head -1)

# rows live
docker exec "$NEWDBC" psql -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT count(*) FROM auth_user;"

Two checkpoints. One before the application comes back, one after it comes back and the entrypoint runs migrate, which should report nothing to apply since the dump already carried django_migrations. If the counts differ between the two checkpoints, something in the startup path is mutating data, and that's a separate problem to chase down before calling the upgrade finished...

The other thing that could have destroyed this

None of the above was the only way to lose data that day. The repository's stack definition still declared postgres:15 pointed at the old volume, and the CI pipeline redeploys automatically on merge to the deploying branch. Which means for the entire duration of this manual cutover, an unrelated merge by anyone, for any reason, would have triggered a deploy that reverted the running database to 15 against the old volume, discarding everything the upgrade had just written.

The CICD pipeline that exists to make deploys safe becomes the most dangerous thing in the system the moment declared state and running state disagree, so i froze merges before the cutover starts, and once the upgrade is confirmed good, update the repository's image and volume references and let that merge run as a no-op deploy that simply confirms what's already true on the box. Only then is it safe to lift the freeze.

Rollback cost nothing, by construction

Because the version 15 volume was never modified, at any point before that repository merge, undoing the whole thing was one command: point the database service back at the old image and old volume, scale the web service back up. Nothing in git to revert, because the repository change was deliberately the last step, done only after the upgrade was already verified. The upgrade was safe to attempt precisely because the previous good state never stopped existing

What this is actually about

Postgres major-version upgrades are not rare or exotic. The specific trap is that, a container-swap operation completing successfully from the caller's point of view while the underlying state hasn't caught up yet, is not specific to Postgres either. It shows up anywhere an orchestrator swaps state out from under a process that assumes synchronous readiness: volume mounts, service updates, anything that treats "the command returned" as equivalent to "the system is in the state I asked for." The fix pattern generalizes too: block on an explicit readiness signal, never on elapsed time, and never trust a cached reference to something the orchestrator is free to replace mid-operation.

The cutover itself is still a manual, human-run sequence with a maintenance window, not a scripted and rehearsed one-command migration. For a database this size, upgraded this infrequently, that's a reasonable place to be. If either of those changes, the next version of this is a script with the verification counts built in as hard gates, not steps I remember to run by hand.