pg_dump | gzip under sh can save a truncated dump as a good one

Without pipefail, a backup pipeline reports the exit status of gzip, not of pg_dump. Dump to a temporary file in custom format, check it with pg_restore -l, and only then rename it.

The first draft of my nightly database backup Job was the line everyone has written at some point:

pg_dump -h db -U app app | gzip > /backup/app.sql.gz

with a size check afterwards. It reads fine. It isn’t, and a review caught it before it ever ran.

The pipeline’s exit status is the last command’s

A shell pipeline returns the exit status of its last command, here gzip. If pg_dump dies halfway (the connection drops, the database restarts, a query times out), gzip happily compresses what it got and exits 0. The Job succeeds. The file isn’t empty, so the size check passes too. You have a backup that stops in the middle of a table, and you’ll find that out when you restore.

Bash and newer shells have set -o pipefail for this. My Job ran under plain sh in a small image, where I couldn’t count on it. I’ve been bitten by that before, when CI steps ran under dash.

Dump, verify, then rename

So the Job doesn’t pipe at all:

set -e
pg_dump -Fc -f /backup/app.dump.tmp -h db -U app app
pg_restore -l /backup/app.dump.tmp > /dev/null
mv /backup/app.dump.tmp /backup/app.dump

Each step is its own command, so set -e stops at the first failure, and a pg_dump that dies halfway never reaches the rename. -Fc writes the custom format, which is already compressed, so gzip isn’t needed. pg_restore -l is an extra sanity check: it reads the archive’s table of contents and fails if the file isn’t a readable dump. It doesn’t read every row, so it’s no substitute for a restore.

The mv comes last, so the name the next step uses (an rsync to the NAS, in my case) only ever points to a dump that passed the check. If anything fails, yesterday’s dump stays in place, and the Job fails visibly instead of reporting success.

A real restore test is still worth doing now and then. Mine restores the dump into a scratch database and counts the documents, which is the number I actually care about.