$RodHat_/search
Rod's Tales

The dump piped into gzip exited zero and the backup was half a database.

Published by

This article may contain Amazon affiliate links. I may earn a commission if you buy through them. Disclosure.

Editorial card: The dump piped into gzip exited zero and the backup was half a database.
Editorial card: RodHat
On this page · 4 sections
  1. How the dump died and nothing noticed
  2. What I found when I restored one
  3. The fix
  4. The rule

The backup job was one line, written in 2017 by someone who has since left and taken the context with them:

pg_dump appdb | gzip > /backup/appdb-$(date +%F).sql.gz && touch /backup/LAST_OK

A monitor watched the mtime on LAST_OK. Every morning it was fresh. Every morning the dashboard was green. For roughly five weeks, every archive in /backup was a fraction of the size it should have been, and nobody looked at sizes because the green light had already answered the question.


How the dump died and nothing noticed

Somebody had rotated the database role’s password and missed the .pgpass on the backup host. Authentication fails, pg_dump prints an error to stderr, and exits non-zero. Stderr went to cron’s mail, which went to an alias that pointed at a mailbox nobody had read since a reorg.

But that is the easy failure. The nastier version showed up later, when auth was fixed and the dump started dying midway through, on a statement timeout against a locked table. pg_dump wrote a valid-looking prefix of SQL, died, and closed its stdout. gzip saw a clean EOF, finished the stream properly, and exited 0.

A pipeline’s exit status is the exit status of the last command. That is POSIX, it is how every Bourne descendant behaves, and it is in the man page you did not read. gzip succeeded at compressing the bytes it was given. The && saw zero and touched the marker file. The result was a perfectly valid gzip file containing a truncated, syntactically unfinished SQL dump. gzip -t passes on it. That is the part that stings: the integrity check people reach for validates the wrong layer.

If this reads like the pipeline that succeeded in silence, same disease, different host. A success signal that reports on the last stage tells you about the last stage.


What I found when I restored one

A drill, not a disaster, thank God. Someone doing a quarterly restore test fed last Tuesday’s archive to psql and it ran for four seconds and stopped dead in the middle of a COPY block with no \. terminator. The tables after it did not exist. Row counts on the ones before it were low.

I went back through the archive directory and sorted by size. The sizes fell off a cliff on one date. Before it, around 4 GB each. After it, between 40 MB and 600 MB, varying with how long the dump survived before the timeout. Five weeks of backups, and the last real one was older than our retention policy was comfortable admitting.

This is the same family as the backup that restored perfectly in reverse: that one proved a restore works, this one proved that a job’s success and a backup’s existence are unrelated facts.


The fix

Three separate fixes, because the failure had three layers.

1. Make the pipeline report the first failure. In bash and ksh, set -o pipefail makes the pipeline return the status of the rightmost command that failed. FreeBSD’s /bin/sh supports pipefail in recent releases, but do not assume it. Check man sh on the box you actually run, and if you are writing for a stock Debian /bin/sh (dash), it depends on the version. A script that says #!/bin/sh and uses pipefail is a script that works on your laptop. Bash also gives you ${PIPESTATUS[@]} if you want every stage’s code. I covered the mechanics in pipeline exit status.

2. Don’t write to the final name until it’s done. Dump to appdb-DATE.sql.gz.partial, check every stage, then mv it into place. A half-written archive should never be indistinguishable from a finished one by filename. This is the same reasoning as the rename that was atomic: rename is how you publish a file, not >.

3. Verify content, not container. After the dump, check that the decompressed stream ends with the terminator your dump format guarantees. pg_dump’s plain format ends with a “PostgreSQL database dump complete” comment line, so zcat file | tail -n 1 is a cheap content check that gzip -t cannot give you. Better still, use the custom format and run pg_restore --list on it, which fails on a truncated archive. And alert on size deviating more than some percentage from the trailing average, because a number in a graph would have caught this in two days.

The monitor itself was the original sin. It watched LAST_OK’s mtime, which measures whether the script reached its last line. That is a heartbeat, not a verdict.


The rule

Never believe a green light you can’t trace back to the thing you care about. I care that a restore produces the data. The marker file cared that a shell reached touch. Those two had nothing to do with each other and I let them stay married for eight years.

More of these, where the dashboard and reality drifted apart quietly, are in the reliability hub and the storage hub.