Automated Backup Recovery Testing

1. Concept Overview & Systemic Problem

Among system administrators, there is an old bitter truth: people are divided into two categories — those who do not yet perform backups and those who already verify their recovery:

• You set up a database backup script via cron 6 months ago. Every night, the script sends a message: "Backup finished successfully".
• A disaster occurs: the server's hard drive fails.
• You download the latest backup and discover that due to a password change six months ago, the script has been creating a 0-byte text file with the error message Access Denied inside the .tar.gz archive every night.
• The database is lost forever.

Automated Backup Recovery Testing eliminates blind faith in checkmarks: a backup is considered successful only when a robot can deploy it on a test machine and successfully read the data.

2. Architectural Taxonomy & Mental Model

┌─────────────────────────────────────────────────────────────┐
│                 BACKUP VERIFICATION PIPELINE                │
├─────────────────────────────────────────────────────────────┤
│ 1. Scheduled Trigger (Weekly Sunday 03:00 AM)               │
│    • GitHub Action or Cron on Secondary Testing VPS         │
├─────────────────────────────────────────────────────────────┤
│                          │                                  │
│                          ▼ STEP 1: RESTORE                     │
│ 2. Pull latest backup from Cloudflare R2 / AWS S3           │
│    • Decrypt archive with production GPG key                │
│    • Spin up isolated PostgreSQL test container             │
│    • Import dump: `pg_restore -d test_db dump.sql`          │
├─────────────────────────────────────────────────────────────┤
│                          │                                  │
│                          ▼ STEP 2: INTEGRITY AUDIT          │
│ 3. Automated Data Sanity Checks                             │
│    • Test 1: Schema integrity check (tables == 42)          │
│    • Test 2: Row count check (Users > 5000, Orders > 10000) │
│    • Test 3: Foreign Key consistency check                  │
├─────────────────────────────────────────────────────────────┤
│                          │                                  │
│                          ▼ STEP 3: ATTESTATION              │
│ 4. Report & Teardown: Pass -> Telegram, Fail -> PagerDuty   │
└─────────────────────────────────────────────────────────────┘

3. Technical Pipeline & Internal Mechanics

01. GitHub Actions Verification Pipeline

Once a week, a GitHub Action downloads the backup, deploys it in a service container postgres:16, and executes a verification script:

python scripts/verify_backup.py
# If exit code != 0, triggers urgent SMS to DevOps lead

02. Recovery Time Objective (RTO) Drill

The team conducts a quarterly simulation of "server burned": timing how many minutes it takes for a new engineer to spin up a project copy from backup on a clean Hetzner server using documentation.

4. Production Engineering Scenarios

01. GitHub Actions Verification Pipeline

Once a week, a GitHub Action downloads the backup, deploys it in a service container postgres:16, and executes a verification script:

python scripts/verify_backup.py
# If exit code != 0, triggers urgent SMS to DevOps lead

02. Recovery Time Objective (RTO) Drill

The team conducts a quarterly simulation of "server burned": timing how many minutes it takes for a new engineer to spin up a project copy from backup on a clean Hetzner server using documentation.

5. Pitfalls, Common Mistakes & Security

• Secret Leakage through Test Servers: If you restore a production backup for testing, the test server contains real personal data of clients. Always perform anonymization or run the verification in a closed local environment without internet access.
• Faking Success by Exit Code: Utilities like pg_dump or tar may exit with code 0 even if non-critical warnings occurred during dump creation that corrupted part of the tables. Check the actual content of the database, not just the exit code of the command.