Backups that lie, and the freshness alarm that catches them

On 7 July 2026 I needed a database backup, and I found out that 13 of 17 databases on my main PostgreSQL instance had never been backed up. Not once. The backup job had run every night for months and reported OK every time.

That was the first of several lies my backups told me in 2026. Some dumps were truncated but passed every check. Two cron jobs had been dead since March while still looking scheduled. One replication job had been stale for five weeks. The home folder backup had stopped 11 months before I needed it. This post is about those failures, and about the one check that now catches all of them: a freshness alarm that looks at the backups themselves, not at the jobs that make them.

Contents · 10 sections

TL;DR #

• A backup job that says OK only proves that the job thinks it succeeded. Check the artifacts, not the job.
• Hard-coded lists of databases go stale the day someone creates a new database. Enumerate what exists at backup time.
• A gzip file that tests clean can still hold a truncated dump. Check the dump’s own completion marker, and hash the file at both ends of every copy.
• A cron job whose output redirect fails never runs at all, and logs nothing.
• One freshness deadman for every backup set, wired into Prometheus and a daily message, catches all of the above, including jobs that were deleted.
• Nothing counts until you have restored it.

Lie 1: “OK” meant “the one database I chose is not empty” #

The incident that started this was an AI agent deleting data from my web-threat database. I went to restore and there was nothing to restore from.

The audit that day covered all six backup layers in the lab: nightly PostgreSQL dumps, nightly MySQL dumps, a “critical data” tar job, Velero cluster backups, an hourly dump of the cluster’s own database, and a handful of per-app cron jobs. They were compared against a census of every database actually running: four PostgreSQL instances, three MySQL servers, a vector database, Elasticsearch, four Redis instances and some SQLite files on the host.

What the census found:

• The PostgreSQL script dumped a hard-coded list of three databases. One of them was a near-empty default database: its compressed dump had been 375 bytes every night for more than 30 days.
• The databases created after the script was written were never added. That was 13 of 17, about 7.4 GB, including every threat-intelligence database I care about.
• The success test was “the dump I chose is non-empty”, so the log said OK.
• Velero’s file-system backup does not capture hostPath volumes. For this PostgreSQL instance it backed up only one volume: a RAM disk used for temporary files.
• Two SQLite databases on the host, one of them 184 MB, were not mentioned by any backup script.
• The backup dashboard listed only *.tar.gz files, so every database dump (.sql.gz, .db.gz) had shown a count of 0 since March. Nobody noticed, because nobody looked.

Every layer enumerated its targets from a list written by hand, at one moment in time. The same day, a triage sweep dumped all 16 non-empty databases plus the SQLite files and copied 18 files (414 MB) to the NAS, each verified. Then the PostgreSQL script was rewritten to discover databases at run time:

# enumerate every non-template database on an instance, at backup time
kubectl exec -n <namespace> <postgres-pod> -- sh -c \
  'psql -U "$POSTGRES_USER" -Atc "SELECT datname FROM pg_database WHERE NOT datistemplate"'

The dashboard gained a coverage endpoint: a live census of every database diffed against fresh dumps. After the rewrite it showed 23 of 23 databases fresh and 0 missing.

Lie 2: a clean gzip with half a table inside #

A month later, the nightly runs had a strange pattern. The biggest database failed validation on 4 of 30 nights, about 13%.

The script streamed the dump through kubectl exec straight into a file on the host. On a long transfer the stream sometimes lost its tail in the middle of the largest table’s COPY block. pg_dump inside the pod finished normally and the command exited 0. The file on the host was a valid gzip of an incomplete dump.

So gzip -t is not an integrity check for a backup. It proves that the compression is intact, not that the content is complete. The fix moves every step that can fail into the pod, and verifies the copy:

# 1. dump to a file inside the pod
kubectl exec -n <ns> <pod> -- sh -c 'pg_dump --clean --if-exists -d <db> | gzip > /tmp/<db>.sql.gz'
# 2. check the completion marker inside the pod
kubectl exec -n <ns> <pod> -- sh -c 'zcat /tmp/<db>.sql.gz | tail -n 20 | grep -q "PostgreSQL database dump complete"'
# 3. hash inside the pod, copy out, hash again on the host
remote=$(kubectl exec -n <ns> <pod> -- sha256sum /tmp/<db>.sql.gz | cut -d" " -f1)
kubectl cp <ns>/<pod>:/tmp/<db>.sql.gz ./<db>.sql.gz
[ "$(sha256sum <db>.sql.gz | cut -d' ' -f1)" = "$remote" ] || echo "copy corrupted" >&2

kubectl cp uses the same streaming channel as exec, so it gets the same treatment: verified, never trusted.

Two smaller traps appeared while writing these checks.

The footer moved. PostgreSQL 17.5 and later print a \unrestrict line after the “dump complete” footer. My first validation checked only the last three lines, and 18 dumps “failed” on the first run. Check the last 20 lines.

pipefail turns a match into a failure. With set -o pipefail, zcat dump.gz | grep -q X can fail when it matches: grep -q exits at the first hit, zcat dies of SIGPIPE, and pipefail reports that. Count instead, which reads the whole stream:

[ "$(zcat dump.sql.gz | grep -c 'dump complete')" -ge 1 ]

After the fix, 16 databases dumped with 0 failures, and a restore test reached 100% of live row counts.

Lie 3: the cron job that never ran #

In August a bug hunt found two cron jobs, a disk-health monitor and a container-manager backup, that had been dead since 13 and 26 March. The crontab had their lines. The system journal showed cron starting them. Nothing ever executed.

The cause: both commands redirected their output to a log file the user could not write. One log had been created by root during an earlier run; the other lived in a directory the user could not create files in. When a shell cannot open the target of a redirect, it aborts before running the command. No output, no error in the job’s own log, nothing.

The sweep checked all 14 redirect targets in the crontab; only those two were broken. Both now write to logs their own user owns. The general rule: a job’s log must be writable by the job’s user, and something outside the job must notice when the log stops growing.

Lie 4: the backup scripts were not backed up #

In October my home folder was deleted (that story is in the test that deleted my home folder). Four host backup scripts went with it. They had never been committed anywhere.

I rebuilt them from what survived: the naming, retention, layout and symlinks of their outputs on the NAS. While doing that I found that the hourly replication of the cluster’s own database to the NAS had been stale since 25 August. Five weeks, no alarm.

The same day the home folder got what it never had: an encrypted, deduplicated hourly snapshot to the NAS, with 24 hourly and 30 daily snapshots kept. The scripts now live in Git and the cron paths are symlinks into the repository.

The fix that covers all four: a freshness deadman #

Every failure above has the same shape. A job stopped producing good artifacts, and no part of the system was looking at the artifacts. A failing job can alert on its own error. A job that is deleted, never starts, or quietly writes the wrong thing cannot.

So one small script, run by a systemd timer every 30 minutes, checks the newest file of every backup set on the NAS against a maximum age:

# set name     | directory on the NAS         | max age (hours)
SETS="
cluster-db     | backups/cluster-db           | 3
host-restic    | restic/host/snapshots        | 3
pg-main        | backups/pg-main              | 30
mysql-main     | backups/mysql-main           | 30
elasticsearch  | backups/es-snapshots         | 192
"
while IFS='|' read -r SET DIR MAXH; do
  SET=$(echo $SET); [ -z "$SET" ] && continue; DIR=$(echo $DIR); MAXH=$(echo $MAXH)
  NEWEST=$(find "/mnt/nas/$DIR" -maxdepth 3 -type f -printf '%T@\n' | sort -n | tail -1 | cut -d. -f1)
  AGE=$([ -n "$NEWEST" ] && echo $(( $(date +%s) - NEWEST )) || echo -1)
  echo "backup_newest_age_seconds{set=\"$SET\"} $AGE"
  echo "backup_max_age_seconds{set=\"$SET\"} $(( MAXH * 3600 ))"
done <<< "$SETS" > /var/lib/node_exporter/textfile_collector/backup_freshness.prom.tmp
mv /var/lib/node_exporter/textfile_collector/backup_freshness.prom.tmp \
   /var/lib/node_exporter/textfile_collector/backup_freshness.prom

node_exporter’s textfile collector publishes the file, and one Prometheus rule fires when any set is too old or has no artifacts at all:

- alert: BackupStale
  expr: backup_newest_age_seconds > backup_max_age_seconds or backup_newest_age_seconds < 0
  for: 30m
  labels: {severity: warning}
  annotations:
    summary: "Backup set {{ $labels.set }} is stale"

The script also sends one chat message per day listing the stale sets, so a broken alert pipeline does not hide a broken backup. Its notification credentials sit in a root-only file outside the home folder, so the alarm survives the kind of incident it is meant to catch.

It started with 33 sets in October and now watches 38 backup sets, from the hourly cluster database (3 hours allowed) to weekly Elasticsearch snapshots (8 days). Every new backup gets a line here on the day it is created. The mechanism is deliberately dumb: it does not care which job, script or timer made the file. It only asks “is there something recent where the backup should be?”

Restore, or it did not happen #

Freshness proves that something recent exists. It does not prove that it restores. My first real restore test was on 8 August, after auditing 49 backup sets: a Git server’s database restored into a scratch database and compared with production. 112 of 112 tables, 9 of 9 repositories, 0 errors.

There is a trap here too. My dumps use --clean --if-exists, so restoring one into the wrong database drops that database’s objects first. The restore-verify script pins the target to a scratch name and refuses to run if the scratch name equals the real one.

Checklist #

• Enumerate databases and volumes at backup time; never maintain the list by hand.
• Compare a census of what exists with what was backed up, and alert on the difference.
• Validate the dump’s completion marker, not just the compression.
• Hash files before and after every copy, especially through kubectl exec or kubectl cp.
• Make every job’s log writable by the job’s user.
• Run one freshness check over the artifacts of every backup set, with a maximum age per set.
• Send the freshness result through two channels (metrics and a daily message).
• Keep backup scripts in Git, not only on the host they protect.
• Restore into a scratch database on a schedule and compare row counts.

Frequently asked questions #

Why is a successful backup job not enough? #

Because the job defines its own success. Mine called a 375-byte dump of an empty database a success for a month. A job that does not run at all, or was deleted, reports nothing. Only a check of the stored artifacts, run by something independent, sees those cases.

Is gzip -t a valid integrity check for a database dump? #

No. It proves that the archive is well formed. A dump cut off in the middle compresses into a perfectly valid gzip. Check for pg_dump’s “database dump complete” footer in the last 20 lines, and compare checksums across every copy.

Why can a cron job silently never run? #

If the command’s output redirect points to a file the user cannot open, the shell fails before starting the command. Cron still logs that it launched the job, so the job looks alive. Make sure log paths are owned by the job’s user, and watch the outputs.

How do I monitor backup freshness with Prometheus? #

Write the age of the newest artifact of each backup set, and its allowed maximum, to a file for node_exporter’s textfile collector. Alert when the age exceeds the maximum or when no artifact exists. Run it from a timer, separate from the backup jobs.

How often should I test restores? #

Regularly, and always after changing a backup script. Restore into a scratch database, compare row counts or table lists with production, and drop the scratch copy.

The lesson #

Every backup failure in this post was silent, and every one was found by accident. The jobs were not lying on purpose; they were answering a narrower question than the one I cared about. “Did my script exit 0?” is not “can I get my data back?”. The freshness deadman asks a question close to the real one, from outside the jobs, every 30 minutes. Then the restore test asks the real one. Ask both, automatically, before the day you need the answer.