Validating Backups

For a year Pedal's backup job reported "success" every night. When the team finally needed a restore, the archive held nothing after March: a full disk had made archive_command fail, and nobody was watching. A backup that has never been restored is a hope, not a backup.

The rule

A backup exists only when you have restored it, checked it and timed it.

Validation has layers, from cheap to conclusive:

1. Watch the job itself. Alert when a backup job fails or is missing. For WAL archiving, pg_stat_archiver shows failed_count and last_failed_time.

2. Check the file. For a base backup, pg_verifybackup compares every file against the backup_manifest checksums and checks the WAL needed for consistency (plain-format backups; tar format is supported from version 18). For a dump, pg_restore -l pedal.dump proves the archive is readable and lists what is inside. Neither proves the data restores.

3. Restore it. On a separate server, regularly — for example weekly, automated — restore the latest backup, and for physical backups recover to a recent point in time.

4. Check the contents. Run sanity queries: row counts of key tables, the newest started_at in rentals, a known member's record. Compare with the numbers recorded when the backup was taken.

5. Time it. Measure how long the restore took. Two targets make the numbers meaningful:

   • RPO (recovery point objective): how much recent data you can afford to lose. Nightly dumps alone mean up to a day; WAL archiving shrinks it to minutes.
   • RTO (recovery time objective): how long you can afford to be down. If the test restore takes six hours and the RTO is one hour, the backup design fails the test even though the data is fine.

Also keep at least one copy away from the server and its account, so one mistake or one attacker cannot delete both the database and its backups.

The shortcut

Every backup plan must answer three questions with evidence, not belief: "When did we last restore it? Was the data right? How long did it take?" If any answer is "we have not tried", that is the next task.

The trap is treating a green "backup succeeded" message as proof. It only says a command exited cleanly, not that the result can rebuild your data.

Drills

Answer fast — trust the rule.

Remember

• A backup exists only when you have restored it, checked it and timed it.
• Watch the job, verify the file, restore, check contents, time it against RPO and RTO.
• Keep a copy away from the server and its account.