Backup tooling, restore testing, and proving recoverability

Part XIII opened with the claim that backup success is not recovery capability. This lesson is how you close that gap.

Three levels of confidence, and what each actually proves

The job exited zero. Proves a program ran. Lesson XIII-03 measured a case where the exit status was zero and the backup was void, because a session ended before pg_backup_stop() and the only complaint was a WARNING in the server log.

Verification passed.

$ pg_verifybackup -n /tmp/vb

backup successfully verified

Proves the files are the files that were written, with the sizes and checksums recorded. Detects truncation, bit rot, corruption in transit, and files lost between backup and storage. Proves nothing about whether the cluster starts, whether backup_label was written, whether the archive contains the WAL the backup needs, or whether the data is what you think.

A restore ran and answered a query. This is the only level that proves recoverability, and it is the only one worth reporting to anyone who will make a decision based on it.

What a restore test must do

A restore test that always passes is telling you about the test, not the backup. A useful one:

1. Uses the real artefacts from the real backup destination. Restoring from a copy that never left the primary tests nothing about your storage or transport.
2. Runs from the documented procedure, not from memory. If the runbook is wrong, the test should discover that.
3. Restores to a point, not just “the backup” — exercise the archive and restore_command, not only the base backup.
4. Verifies content, with a query whose answer is known independently. Row counts, a checksum over a stable table, the presence of a known recent row.
5. Measures the time, from decision to answering queries. That number is your real RTO, and it is usually longer than anyone’s estimate.
6. Runs unattended and reports failures loudly, because a manual quarterly test is a test that has not run since the last audit.

-- a content check whose answer you know independently
SELECT count(*)                       AS rows,
       max(created_at)                AS newest,
       sum(hashtext(id::text))        AS content_signature
  FROM orders;

The newest value is the one that matters most: it tells you how close to the failure the recovery actually got, which is your measured RPO rather than your intended one.

Native tools or a backup manager

pg_basebackup plus archive_command plus a retention script is a complete, supported solution. It is the right answer for a great many clusters, and reaching for a tool you do not need adds a component that can fail.

The list from lesson XIII-04 is when it stops being enough: deduplication, retention management, parallel copy, encryption, resume, and a restore command. Backup managers in the PostgreSQL ecosystem — pgBackRest, Barman, WAL-G among others — exist to supply those. Choose on the specific capabilities you need rather than on reputation, and evaluate at least these:

• Does it verify restores, or only backups? Some can perform an automated restore test.
• How does it handle the archive? Its own archive_command, its own retention, its own integrity checking.
• What happens when it is not there? A backup you can only restore with a specific tool version is a dependency in your recovery path.
• Does it support incremental, and how? Its own block tracking, or PostgreSQL’s WAL summarization.
• What does its restore look like at 3 a.m.? One command, or a procedure.

Part XIII, in one place

What to take from this

• Exit zero proves a program ran. Verification proves the bytes are intact. Only a restore proves recoverability.
• Test from the real destination, from the documented procedure, to a point in time, with a content check and a stopwatch.
• Your measured RPO is the newest row that survived. Your measured RTO is the clock.
• Native tools are a complete solution. Choose a backup manager for specific capabilities you need, not by reputation.
• Restore tests fail on configuration files, extensions, tablespaces, roles, collations and disk space — none of which any backup check looks at.

Cross-course references

• Linux for Production Sysadmins — Part XLVIII (Backup tools) covers evaluating a tool against restore properties rather than feature lists, and Part L (Disaster recovery) covers the drill that produces the only real evidence.
• Ansible for Production Sysadmins — Part L (Automation disaster recovery) covers rebuilding the automation that rebuilds the cluster, which is the dependency most restore plans omit.
• Observability for Production Sysadmins — Part XCII (Disaster recovery) covers monitoring that survives the failure it is meant to report.

Quiz