pg_dump From a French VPS Kept Truncating on the Last Table

A weekly pg_dump of a 38 GB database on a French VPS (OVH, Debian 12, PostgreSQL 15.4) started failing every Sunday around 03:40. The dump itself was written to the local disk first, then shipped to object storage. The failure looked like a network timeout in the shipping step, but the archive was also 1.2 GB short of the previous week. pg_verifybackup-style checks did not exist for this plain dump, so nobody noticed until a restore test six weeks later. That restore test is how we found out we had been shipping truncated archives for a month and a half.

The first non-obvious part: the truncation always happened on the same table, events_archive, which is the last relation in the dump order. The file ended cleanly on a row boundary. No error in the log, exit code 0. The shipping step used rclone copy and the local file was already short, so the problem was upstream of the network.

What actually happened is that the dump was piped into gzip under a wrapper that enforced a wall-clock limit with timeout. The wrapper was added months earlier to stop a hung dump from blocking the nightly window:

# old wrapper, silently wrong
timeout 4h pg_dump -Fp -Z0 mydb | gzip -1 > /dump/mydb.sql.gz

The timeout sends SIGTERM to pg_dump only. gzip gets EOF, finishes its stream cleanly, and writes a valid gzip file with a truncated payload. Exit status of the pipeline is the status of gzip, which is 0. So the monitoring saw success.

My fix was to make the pipeline fail loudly and to verify size before shipping:

set -o pipefail
timeout --signal=INT --kill-after=5m 4h \
  pg_dump -Fp -Z0 mydb \
  | gzip -1 > /dump/mydb.sql.gz.tmp
rc=$?
if [ $rc -ne 0 ]; then
  echo "dump failed rc=$rc" >&2
  rm -f /dump/mydb.sql.gz.tmp
  exit $rc
fi
gzip -t /dump/mydb.sql.gz.tmp || { echo "gzip integrity failed" >&2; exit 1; }
mv /dump/mydb.sql.gz.tmp /dump/mydb.sql.gz

gzip -t only checks the CRC of the compressed stream, so it will not catch a payload that was cut before gzip closed. The real guard is comparing the uncompressed byte count against pg_database_size(mydb) within a tolerance, or better, restoring into a scratch database weekly. We do the restore now; it takes 22 minutes and it caught a second, unrelated problem (a missing extension on the restore host) three weeks later.

The trade-off: a full restore test costs a slot in the maintenance window, and on this VPS that means the Sunday dump and the restore test cannot overlap without swapping. We moved the restore to Wednesday. The timeout also had to change from SIGTERM to SIGINT, because pg_dump handles SIGINT by aborting the transaction rather than letting the connection drop, which matters when the source is under load.

Where this breaks: --kill-after with SIGKILL leaves the temp file behind if the wrapper itself is killed, so the .tmp cleanup needs a separate sweep. If the dump is larger than the free space on /dump, you get the same truncated-looking artefact for a completely different reason, and pipefail will not save you because gzip never sees an error. And pg_dump -Fc plus pg_restore -l gives you a cheaper integrity check than a full restore, though it does not prove the data is readable.

What does your dump verification actually prove? Ours only proved gzip was happy, which is not the same thing.