Monitor pg_dump Backups with Cuckoo

A nightly pg_dump in a crontab is often the only backup a PostgreSQL database has. It is also easy to break without noticing: a password change, a full disk, a renamed database, or a pipe that hides pg_dump's error so the job "succeeds" with an empty file. The dump keeps getting written every night, and none of them can be restored.

This guide builds a backup script that checks its own output and reports the result to Cuckoo, including a fail signal when the dump is suspiciously small.

How Cuckoo sees the backup

The script sends https://cuckoo.metastic.ch/ping/YOUR-UUID/start at the beginning and its exit code at the end, for example https://cuckoo.metastic.ch/ping/YOUR-UUID/0 for success. Any non-zero code marks the run as failed and alerts immediately, with the log tail (up to 10 KB) attached. If the job does not run at all, the check goes down after its schedule plus grace time.

The backup script

Save as /usr/local/bin/pg-backup, make it executable, and run it as the postgres user (or any role that can read the database, with credentials in ~/.pgpass):

#!/usr/bin/env bash
set -uo pipefail

HB_URL="https://cuckoo.metastic.ch/ping/YOUR-UUID"
DB="app"
DIR="/var/backups/postgres"
MIN_BYTES=$((5 * 1024 * 1024))     # adjust to your database: ~50% of a normal dump
FILE="$DIR/$DB-$(date +%Y%m%d-%H%M%S).dump"

LOG=$(mktemp)
trap 'rm -f "$LOG" "$FILE.partial"' EXIT
hb() { curl -fsS -m 10 --retry 5 -o /dev/null "$@" || true; }

hb "$HB_URL/start"

(
  set -euo pipefail
  mkdir -p "$DIR"

  pg_dump --format=custom --file="$FILE.partial" "$DB"
  mv "$FILE.partial" "$FILE"

  size=$(stat -c %s "$FILE")
  echo "dump: $FILE ($size bytes)"
  if [ "$size" -lt "$MIN_BYTES" ]; then
    echo "ERROR: dump is smaller than $MIN_BYTES bytes, refusing to treat it as a backup" >&2
    exit 90
  fi

  # The archive must be readable by pg_restore
  pg_restore --list "$FILE" > /dev/null

  # Upload off-site (any tool works: rclone, aws s3 cp, rsync)
  rclone copy --quiet "$FILE" offsite:db-backups/postgres/

  # Keep 7 days locally
  find "$DIR" -name "$DB-*.dump" -mtime +7 -delete
) > "$LOG" 2>&1
rc=$?

tail -c 10000 "$LOG" | hb --data-binary @- "$HB_URL/$rc"
cat "$LOG"
exit "$rc"

How it works:

--format=custom (-Fc) produces a compressed archive that pg_restore can restore selectively and in parallel.
.partial then mv ensures a half-written file never looks like a finished backup.
The subshell with set -e stops at the first failing step; its exit code becomes rc.
The size check catches the classic silent failure: a dump that completes but contains almost nothing, for example because the job connected to an empty database. It exits with 90, which Cuckoo records as a failure.
pg_restore --list reads the archive's table of contents, a cheap check that the file is a valid dump.
Upload before cleanup so local files are only deleted after a successful copy.

Schedule it in the postgres user's crontab (sudo -u postgres crontab -e):

SHELL=/bin/bash
PATH=/usr/local/bin:/usr/bin:/bin
0 1 * * * /usr/local/bin/pg-backup > /var/log/postgresql/pg-backup.log 2>&1

Streaming dumps need pipefail

If you stream the dump instead of writing a file first, the pipeline's exit status must reflect pg_dump:

set -o pipefail
pg_dump -Fc app | rclone rcat offsite:db-backups/postgres/app-$(date +%F).dump

Without pipefail, the pipeline returns rclone's status. If pg_dump fails halfway, rclone still uploads the truncated stream successfully and the job reports success. The same applies to pg_dump | gzip > file with plain-text dumps.

A note on mysqldump

The same pattern works for MySQL and MariaDB (where the tool is also available as mariadb-dump):

set -o pipefail
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --events app | gzip > "$FILE.partial"
zcat "$FILE.partial" | tail -n 1 | grep -q "Dump completed" || { echo "dump incomplete" >&2; exit 91; }
mv "$FILE.partial" "$FILE"

--single-transaction gives a consistent snapshot of InnoDB tables without locking them.
--defaults-extra-file keeps the password out of the process list and the crontab.
mysqldump writes a -- Dump completed comment as its last line (unless --skip-comments is used), so its absence reveals a truncated dump.

Schedule and grace advice

Use the cron expression from the crontab (0 1 * * *) and the server's IANA time zone on the check. Dump time grows with the database, so look at the durations Cuckoo records and set a max runtime of about twice the longest run. A grace time of 20 to 30 minutes is usually enough once the start ping is in place.

Troubleshooting

pg_dump: error: connection to server ... failed: the cron user has no access. Run as postgres or add a ~/.pgpass entry with mode 600.
Exit code 90 every night after a cleanup: the database really got smaller. Lower MIN_BYTES.
pg_dump: error: aborting because of server version mismatch: the client is older than the server. Install the matching client package.
Dumps succeed but upload fails: the log tail in the ping shows the rclone error; check credentials and quota.

FAQ

Is pg_dump enough as a backup strategy?

It gives you logical, point-in-time-of-dump backups, which is fine for many small and medium databases. For point-in-time recovery, use WAL archiving with a tool such as pgBackRest or Barman, and monitor that job the same way.

How do I choose the minimum size?

Look at a few recent dumps and set the threshold to roughly half of a normal one. It is meant to catch empty or truncated dumps, not normal growth or shrinkage.

Should I test restores too?

Yes. A scheduled job that restores the latest dump into a scratch database and runs a simple query is the strongest check. Give it its own Cuckoo check so a failed restore test alerts as well.