How to Monitor Backup Cron Jobs and Prove Each Backup Is Usable

To monitor a backup cron job, check four things on every run, not just the exit code: that the job ran on schedule, that the file it wrote is a complete archive containing your tables, that the off-site copy matches the local one, and, on a schedule of its own, that the backup restores. A missed-run alert, the dead man's switch pattern, covers only the first. This guide builds each check for PostgreSQL 18 with pg_dump, shows the MySQL 8.4 equivalent, and connects the results to alerts.

Four Ways a Backup Fails While Looking Fine#

A pg_dump Script That Fails Loudly#

The script targets Linux with bash, GNU coreutils, the PostgreSQL 18 client tools, and AWS CLI v2 uploading to Amazon S3. It dumps one database, proves the archive is readable, publishes it with an atomic rename, uploads it with a checksum, and prunes old copies. Any failed step stops it with a non-zero exit code.

#!/bin/bash
# /opt/backup/pg-backup.sh: dump, verify, upload, and prune one PostgreSQL database.
set -euo pipefail
export PGHOST=db1.internal PGUSER=backup # the password comes from ~/.pgpass

db=appdb
backup_dir=/var/backups/postgres
bucket=example-db-backups
keep=7

final="$backup_dir/$db-$(date -u +%F).dump"
tmp=$(mktemp "$backup_dir/.$db.XXXXXX")
trap 'rm -f "$tmp"' EXIT

# Dump under a temporary name, with a time limit.
timeout 3h pg_dump --no-password --format=custom --dbname="$db" --file="$tmp"

# Read every block back, then count the tables whose rows were dumped.
pg_restore --file=/dev/null "$tmp"
tables=$(pg_restore --list "$tmp" | awk '/ TABLE DATA /{n++} END{print n+0}')
if [ "$tables" -eq 0 ]; then
  echo "$tmp contains no table data" >&2
  exit 1
fi
bytes=$(stat -c %s "$tmp")
mv "$tmp" "$final"

# Upload with a checksum S3 verifies, then read the stored checksum back.
sha=$(openssl dgst -sha256 -binary "$final" | base64)
key="postgres/$db/${final##*/}"
aws s3api put-object --bucket "$bucket" --key "$key" --body "$final" \
  --checksum-sha256 "$sha" > /dev/null
stored=$(aws s3api head-object --bucket "$bucket" --key "$key" \
  --checksum-mode ENABLED --query ChecksumSHA256 --output text)
upload_verified=false
if [ "$stored" = "$sha" ]; then upload_verified=true; fi

# Keep the newest $keep local copies; the names sort oldest first.
backups=("$backup_dir/$db"-*.dump)
for ((i = 0; i < ${#backups[@]} - keep; i++)); do
  rm -f -- "${backups[i]}"
done

# Hand the evidence to the monitoring wrapper, when one is running this script.
if [ -n "${CRONRADAR_OUTCOMES_FILE:-}" ]; then
  printf '{"outcomes":{"backup_bytes":%s,"tables_dumped":%s,"upload_verified":%s}}\n' \
    "$bytes" "$tables" "$upload_verified" > "$CRONRADAR_OUTCOMES_FILE"
fi

What each part guards against:

• set -euo pipefail. -e exits on the first failing command and -u on an unset variable. Without pipefail, a pipeline reports only its last command's status, so a failed pg_restore --list feeding awk would pass (bash(1)).
• --no-password and ~/.pgpass. Under cron nobody can answer a password prompt. --no-password makes pg_dump fail instead of waiting, and libpq reads the password from ~/.pgpass, which it ignores unless the file's mode is 0600 (libpq password file). Cron also gives the job a short PATH and none of your shell's PG* variables; cron environment variables covers what it does set.
• Custom format under a temporary name. --format=custom writes a compressed archive that pg_restore can read selectively (pg_dump). A dump that fails halfway never sits under a finished backup's name, because only a verified file is renamed. rename(2) replaces the target atomically only within one filesystem (rename(2)), so the temporary file lives in the backup directory.
• Two reads of the archive. pg_restore --list prints the table of contents, and the script counts its TABLE DATA entries, one per table whose rows were dumped. pg_dump writes the table of contents before the table data (pg_backup_custom.c), so a listing alone can succeed on a truncated file. pg_restore --file=/dev/null converts the whole archive to SQL and discards it, which reads and decompresses every block and fails at a premature end of file (pg_restore). Zero tables stops the script: that is what dumping the wrong database, or an empty one, looks like.
• timeout 3h. GNU timeout stops pg_dump after three hours and exits 124 (timeout(1)), turning a hang into a failure.
• A checksum S3 verifies. For a single-part upload with a checksum you supply, S3 calculates the checksum itself and rejects the request if the two differ; head-object --checksum-mode ENABLED returns the stored value (S3 upload integrity, head-object). One put-object takes at most 5 GB. Larger files need a multipart upload, where S3's SHA-256 is a checksum of the parts' checksums rather than of the whole file, so this comparison no longer works as written.
• Pruning last, by count. Dated names sort oldest first, so the loop removes copies beyond the newest seven. It runs only after a new backup has been verified and uploaded, and a week of failed runs deletes nothing.

pg_dump covers one database. Roles and tablespaces belong to the whole cluster and need pg_dumpall --globals-only (pg_dumpall). Store that file as carefully as the dump: it includes role passwords unless you add --no-role-passwords.

Retention That Can't Delete the Last Good Backup#

A separate cleanup job such as find /var/backups -mtime +7 -delete goes by age alone: if backups stop for longer than the window, it removes every copy, the newest included. Counting files, as the loop above does, can't. Off-site storage has the same trap. An S3 Lifecycle Expiration rule counts days from each object's creation (S3 Lifecycle elements) and doesn't check whether a newer backup exists. That is safe only if a stopped backup gets noticed well before the rule's window runs out, which is the monitoring's job.

The MySQL Version: mysqldump --single-transaction#

The same structure works for MySQL 8.4. The parts that change:

tmp=$(mktemp /var/backups/mysql/.appdb.XXXXXX)
trap 'rm -f "$tmp" "$tmp.gz"' EXIT

# The option file holds the user and password, and must be the first option.
timeout 3h mysqldump --defaults-extra-file=/etc/backup/mysqldump.cnf \
  --single-transaction --quick --routines --events \
  --result-file="$tmp" appdb
tail -n 1 "$tmp" | grep -q '^-- Dump completed on'
tables=$(grep -c '^CREATE TABLE' "$tmp")
gzip "$tmp"
mv "$tmp.gz" "/var/backups/mysql/appdb-$(date -u +%F).sql.gz"

--single-transaction dumps a consistent snapshot without blocking other connections, but only InnoDB tables are consistent; MyISAM and MEMORY tables can still change mid-dump. The manual also warns that ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, or TRUNCATE TABLE from another connection during the dump can produce incorrect contents or a failed dump, so keep migrations out of the backup window (mysqldump). --result-file creates its file even when the dump fails, hence the temporary name. With comments on, the default, mysqldump ends the file with -- Dump completed on, so the tail check stops a cut-short dump, and grep -c exits non-zero when there are no tables. The option-file position rule is in option-file options.

For SQL Server backups run by Agent, SQL Server Agent job monitoring covers Agent's history and failure mail; the four checks still apply.

Report Each Run and Its Evidence to a Monitor#

The script makes a bad backup fail loudly, but it can't report the night it didn't run, and a failure in a log nobody reads goes unnoticed. An external monitor covers both: it alerts on a missing check-in, a reported failure, and numbers outside their expected range.

With CronRadar, there is no SDK for shell scripts and no auto-discovery for cron, so each job reports with its own HTTP check-ins. This wrapper takes the monitor key, schedule, and grace period as arguments, reports start, runs the job, and reports fail with the exit code or complete with the job's outcome values:

#!/bin/sh
# /opt/backup/monitored.sh KEY SCHEDULE GRACE COMMAND [ARG]...
key=$1
schedule=$2
grace=$3
shift 3
# The key lives in a file only the job's account can read: cronie and Debian's cron
# copy crontab variables into the headers of the mail they send.
CRONRADAR_API_KEY=$(cat /etc/cronradar/api-key 2>/dev/null)
url="https://cron.life/api/v1/ping/$key"
run_id="$key-$(date +%s)-$$"
CRONRADAR_OUTCOMES_FILE=$(mktemp)
export CRONRADAR_OUTCOMES_FILE

report() {
  event=$1
  shift
  curl --fail --silent --show-error --max-time 5 --output /dev/null \
    --request POST --get --user "$CRONRADAR_API_KEY:" \
    --data-urlencode "schedule=$schedule" \
    --data-urlencode "gracePeriod=$grace" \
    --data-urlencode "run_id=$run_id" \
    "$@" "$url/$event" || true
}

report start
"$@"
rc=$?
if [ "$rc" -ne 0 ]; then
  report fail --data-urlencode "message=$1 exited with code $rc"
elif [ -s "$CRONRADAR_OUTCOMES_FILE" ]; then
  # --get would move the body into the URL, so this request builds its own query.
  curl --fail --silent --show-error --max-time 5 --output /dev/null \
    --request POST --user "$CRONRADAR_API_KEY:" \
    --header "Content-Type: application/json" \
    --data-binary "@$CRONRADAR_OUTCOMES_FILE" \
    "$url/complete?run_id=$run_id" || true
else
  report complete
fi
rm -f "$CRONRADAR_OUTCOMES_FILE"
exit "$rc"

Every request gives up after five seconds and ignores errors, and the wrapper exits with the job's own code, so a monitoring outage can't fail a backup or hide one. The start carries the schedule, so the first run creates the monitor and the next run recreates a deleted one. The outcome-carrying complete leaves the schedule out and relies on that start. A fail alerts immediately, and exit code 124 in its message means the timeout fired. The HTTP API reference has the full parameter rules.

30 2 * * * /opt/backup/monitored.sh nightly-db-backup '30 2 * * *' 600 /opt/backup/pg-backup.sh

CronRadar evaluates schedules in UTC. If the server's clock isn't on UTC, pass the UTC equivalent of the crontab time as the schedule.

Configure the Outcome Checks First#

Outcome values are compared only with expectations you have configured; until then CronRadar accepts them without storing them. Let the first run create the monitor, then add outcome checks in the automation's settings:

Set each floor from your own history: well below a normal night, far above a broken one. A completed run that violates an expectation, or omits a configured key, is marked outcome-failed and opens an incident showing the observed and expected values. Each run can report at most five numbers or booleans. Send counts and flags only, never rows, logs, or secrets. A bare heartbeat has no body, so it would fail these checks as missing; outcomes need the complete check-in.

A Backup Isn't Proven Until It Restores#

"The backup succeeded" means pg_dump wrote a file it could read, not that the file holds current data (a dump of a replica that stopped replaying is valid and old) or that it loads. The restore can still fail on things the dump never exercises: an extension whose supporting files aren't installed on the new server (CREATE EXTENSION), objects owned by roles that don't exist there, or an older server version, since pg_dump output isn't guaranteed to load into an older major version (pg_dump). Only a restore proves a restore works.

This drill runs on a scratch server, never production. It downloads today's off-site copy, the one you would use if the primary server were gone, checks it against S3's stored checksum, restores it into a throwaway database, and queries the result:

#!/bin/bash
# /opt/backup/restore-drill.sh: restore today's off-site backup into a scratch database.
set -euo pipefail
export PGHOST=pg-scratch.internal PGUSER=drill

db=appdb
bucket=example-db-backups
key="postgres/$db/$db-$(date -u +%F).dump"
scratch=restore_drill
file=$(mktemp /var/tmp/restore-drill.XXXXXX)
trap 'rm -f "$file"' EXIT

aws s3api get-object --bucket "$bucket" --key "$key" "$file" > /dev/null
expected=$(aws s3api head-object --bucket "$bucket" --key "$key" \
  --checksum-mode ENABLED --query ChecksumSHA256 --output text)
if [ "$(openssl dgst -sha256 -binary "$file" | base64)" != "$expected" ]; then
  echo "$key does not match its stored checksum" >&2
  exit 1
fi

started=$(date +%s)
dropdb --no-password --if-exists "$scratch"
createdb --no-password "$scratch"
timeout 2h pg_restore --no-password --dbname="$scratch" --no-owner --no-privileges \
  --exit-on-error --jobs=4 "$file"
restore_seconds=$(($(date +%s) - started))

sql() {
  psql --no-password --no-psqlrc --tuples-only --no-align \
    --set=ON_ERROR_STOP=1 --dbname="$scratch" --command="$1"
}
restored_tables=$(sql "SELECT count(*) FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema')")
orders_rows=$(sql "SELECT count(*) FROM public.orders")
newest_order_age_hours=$(sql "SELECT coalesce(floor(extract(epoch FROM
  now() - max(created_at)) / 3600), 100000) FROM public.orders")
dropdb --no-password "$scratch"

if [ -n "${CRONRADAR_OUTCOMES_FILE:-}" ]; then
  printf '{"outcomes":{"restored_tables":%s,"orders_rows":%s,"newest_order_age_hours":%s,"restore_seconds":%s}}\n' \
    "$restored_tables" "$orders_rows" "$newest_order_age_hours" "$restore_seconds" \
    > "$CRONRADAR_OUTCOMES_FILE"
fi

--no-owner --no-privileges let the drill run on a server without your production roles; a real recovery loads the pg_dumpall --globals-only file first. --exit-on-error stops at the first failed statement, where pg_restore would otherwise continue and report an error count at the end (pg_restore). The scratch database is dropped only after a successful drill, so a failure leaves it for inspection and the next run starts clean. Replace public.orders with a table your application writes to every day.

Give the drill its own monitor in the scratch server's crontab:

30 5 * * 0 /opt/backup/monitored.sh weekly-restore-drill '30 5 * * 0' 600 /opt/backup/restore-drill.sh

Then configure restored_tables >= 42, orders_rows >= 1000000, newest_order_age_hours <= 27, and restore_seconds <= 5400. The age check catches a dump that is complete but stale: the drill runs three hours after a backup that should hold the previous day's orders. The duration check tells you when a real recovery would overrun its target, 90 minutes here. A silent backup and a failing drill need different fixes, hence separate monitors.

How Often to Run a Restore Drill#

The interval between drills is the longest a broken backup can go unnoticed. For nightly backups, weekly is a sensible default, and daily is better when restores are cheap and a week of unusable backups would hurt. A few times a year, rehearse a full recovery by hand on a clean host: roles, configuration, credentials, and the runbook, none of which the script exercises. Run the drill again after anything that changes the restore path, such as a PostgreSQL major upgrade, a new extension, or a new bucket.

Set the Grace Period and the Timeout#

Backup duration varies with data size and load, and the check-in design decides whether that matters.

With a start check-in, it doesn't. CronRadar expects the start at 02:30, the start arrives within seconds however long the dump takes, and the grace period only has to cover a late start. Ten minutes (600) is plenty on a UTC host. A heartbeat sent only at the end arrives at 02:30 plus the dump's duration, so its grace period must cover the slowest normal run, and every missed night is reported that much later. If the server follows daylight saving time, schedule the earlier of the two UTC times and use a grace period of at least an hour. Choosing a cron grace period works through the numbers.

A start check-in doesn't limit how long a run takes, though. Each check-in moves the expected time to the next scheduled run, so a dump that hangs at 02:31 shows as stuck only if tomorrow's 02:30 plus grace passes with no new check-in, and a normal start tomorrow resets that. CronRadar has no per-job maximum runtime. The timeout in the script is the limit: set it to a few times the longest normal run and below the interval between runs, and a hang becomes a fail that alerts immediately. If one run could still overlap the next, start the wrapper itself under a lock, 30 2 * * * flock -n -E 75 /var/lib/backup/nightly.lock /opt/backup/monitored.sh ..., as described in preventing overlapping cron jobs. A run that finds the lock held exits 75 before reporting anything, and the run still holding it is flagged as stuck if it outlasts the next scheduled time plus grace.

Frequently Asked Questions#

How do I know my backup cron job actually ran?#

Logs and exit codes exist only for runs that happened, so a job that never started leaves nothing to inspect. Have the job check in with an external monitor at the start of every run. The monitor knows the schedule and alerts when the check-in doesn't arrive within the grace period, even if the whole server is down.

How do I monitor a pg_dump cron job?#

Run pg_dump with --no-password under set -euo pipefail and a timeout, and write a custom-format archive to a temporary file. Verify it with pg_restore --list and a full read before renaming it into place. Then wrap the script so it reports start and then complete or fail, sending the archive size and table count with the completion.

How often should I test restoring backups?#

Automate a restore of the off-site copy at least weekly for nightly backups; the gap between tests is how long an unusable backup goes unnoticed. Add an occasional manual recovery on a clean host for the steps a script can't test.

What should a backup alert check besides the exit code?#

That the run started on schedule, the archive holds the expected number of tables, its size is in the normal range, and the off-site checksum matches. A separate monitor should watch the restore drill.

Why did my backup cron job create an empty file?#

The shell creates the target of > before running the command (POSIX shell), so pg_dump appdb > /var/backups/appdb.sql leaves an empty file when pg_dump isn't on cron's PATH, can't authenticate, or can't connect. Piped through gzip, the result is a tiny valid gzip file, and without pipefail the job still exits 0. Write to a temporary name, set pipefail, and refuse to publish an archive with no tables.