Restore Testing

A backup is a hypothesis until it has been restored. Restore testing converts it into a fact, and it is the single check that most often reveals a broken assumption.

What goes wrong that only a restore reveals

• A backup job reporting success while writing a truncated file.
• Missing WAL or binlog segments, so the chain stops mid-recovery.
• An encryption key nobody can retrieve.
• Roles, grants or extensions absent from the backup, producing a database nobody can connect to.
• A restore that takes eleven hours against a stated four-hour recovery time objective.
• A version mismatch: backups from a newer engine cannot be restored into an older one.

Every one of these is invisible until someone tries.

Automate it

Manual quarterly drills get skipped. A scheduled job does not.

#!/usr/bin/env bash
set -euo pipefail
 
START=$(date +%s)
 
# 1. Provision a clean, isolated target.
provision_verification_instance
 
# 2. Restore the most recent backup.
pgbackrest --stanza=main --pg1-path=/srv/verify restore
pg_ctlcluster 17 verify start
 
# 3. Wait for recovery to finish.
until psql -h verify -c "SELECT NOT pg_is_in_recovery()" | grep -q t; do sleep 10; done
 
# 4. Verify content, not just that the process started.
psql -h verify -f /opt/verify/checks.sql
 
DURATION=$(( $(date +%s) - START ))
echo "restore_duration_seconds $DURATION" | push_to_metrics
 
teardown_verification_instance

Check content, not exit codes

-- Row counts for the tables that matter.
SELECT 'orders' AS t, count(*) FROM orders
UNION ALL SELECT 'customers', count(*) FROM customers;
 
-- Freshness: the newest row should be close to the backup time.
SELECT max(created_at) FROM orders;
 
-- A business invariant that must hold.
SELECT count(*) AS orphans FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id WHERE o.id IS NULL;
 
-- Roles survived the restore.
SELECT rolname FROM pg_roles WHERE rolcanlogin ORDER BY 1;
 
-- Extensions are present at the expected versions.
SELECT extname, extversion FROM pg_extension ORDER BY 1;

The orphan check is the useful kind: it tests that the data is coherent, not merely present.

Record the duration

Restore duration is the only honest measure of your recovery time objective. Publish it as a metric and alert when it approaches the stated RTO — datasets grow, and a restore that met the objective a year ago may no longer.

Beyond the database

A full recovery drill covers what a restore alone does not:

• Can you obtain the credentials and the decryption key when the primary environment is unavailable?
• Does the application start against the restored database?
• Are DNS, firewall rules and connection strings part of the procedure?
• Does someone who did not write the runbook succeed in following it?