design-backup-dr-recovery

name	design-backup-dr-recovery
description	Designs and validates backup, point-in-time-recovery, and disaster-recovery strategy for datastores — sets RPO/RTO targets, configures snapshot plus continuous WAL/binlog/oplog archiving for PITR, 3-2-1 immutable retention, automated test-restores, and cross-region replica failover with split-brain fencing.
when_to_use	When a stateful service needs a credible answer to "what if the database is lost or corrupted" — setting RPO/RTO, wiring snapshots + continuous log archiving for PITR, designing cross-region failover, scheduling tested restores, or auditing a never-restore-tested backup. Distinct from db-migration-safety (forward schema change safety) and incident-response-sre (running the live outage, not designing recoverability).

When to Use

Reach for this skill when the question is "can we get the data back, and how fast" — not how to change the schema:

• "Set RPO/RTO for this database and prove we can hit them"
• "We have nightly snapshots but no way to restore to 2:47pm — add PITR"
• "Stand up cross-region DR / a warm standby we can promote"
• "Our backups have never been restore-tested — audit and fix that"
• "Recover a single dropped table without rolling back the whole DB"
• "Defend backups against ransomware / a fat-fingered DROP DATABASE"

NOT this skill:

• Making a forward schema migration safe/reversible (expand-contract, online DDL) → db-migration-safety
• Running the live incident — paging, comms, mitigation timeline → incident-response-sre
• Protecting/rotating the backup-store credentials and KMS keys → secrets-management
• Alerting that a backup job failed / dashboards for restore lag → observability-instrument
• Trimming snapshot/storage spend → cloud-cost-optimize

Steps

1. Set RPO and RTO per datastore from business impact — these two numbers drive every later choice. RPO = max tolerable data loss (how far back you may rewind). RTO = max tolerable downtime (how long restore may take). Pick a tier, don't invent per-DB:

   Tier	Example data	RPO	RTO	Implied mechanism
   Tier 0 (money/orders)	payments ledger, auth	≤ seconds	≤ minutes	sync replica + continuous WAL, automated promotion
   Tier 1 (core app)	primary OLTP DB	≤ 5 min	≤ 1 hr	snapshot + async WAL archiving (PITR), warm standby
   Tier 2 (supporting)	analytics, search index	≤ 1 hr	≤ 4 hr	hourly snapshot, rebuild-from-source allowed
   Tier 3 (derived/cache)	caches, rebuildable views	n/a	n/a	no backup — document the rebuild procedure instead

   RPO ≤ snapshot interval is a lie unless you also archive logs continuously (step 2). Write the chosen numbers down; an untargeted "we back up nightly" has an implicit 24h RPO nobody agreed to.

2. Two backup layers: periodic base + continuous log archiving. Snapshot-only cannot do PITR. A snapshot gets you to snapshot time; the log stream replays forward to any timestamp in between.

   Engine	Base backup	Continuous log (the PITR engine)	Restore = base + replay
   PostgreSQL	pg_basebackup / disk snapshot	WAL via archive_command → object store (pgBackRest/WAL-G)	restore_command + recovery_target_time
   MySQL/MariaDB	xtrabackup / mysqldump	binlog (log_bin, binlog_format=ROW) shipped off-host	restore base, mysqlbinlog --stop-datetime apply
   MongoDB	mongodump / filesystem snapshot	oplog (replica set required)	restore +

Common Errors

• Never restore-testing. The #1 cause of "we had backups but couldn't recover." A backup that has never been restored is unproven; automate the drill (step 4) so success/failure is observed continuously, not discovered during the outage.
• Snapshot-only, calling it PITR. Nightly snapshots = up to 24h RPO and you can only land on snapshot boundaries. PITR requires continuous WAL/binlog/oplog archiving (step 2). If asked for "restore to any second," snapshots alone cannot.
• Same blast radius. Backups in the same account/region/bucket as prod die with it — one compromised credential, one region outage, one DROP and both the data and its backup are gone. Cross-account + cross-region + immutable is the point.
• No immutability → ransomware/insider wipes the backups too. Mutable backups are deleted in the same attack that hit prod. Use object-lock Compliance mode / retention lock on ≥1 copy.
• Replica treated as a backup. A replica faithfully replicates DELETE FROM users and corruption in milliseconds. Replication is for availability/failover; it is not a backup and gives zero protection against logical errors. You need both.
• Logical dump as the primary backup for a large DB. pg_dump/mysqldump of a multi-TB DB takes hours to restore and strains/locks the live DB while running — blows RTO. Use physical base + log archiving; keep logical dumps as a secondary portable copy only.
• RTO ignores restore and warm-up. Real RTO = provision + transfer + restore + log replay + cache/index warm-up + cutover. Cold-tier (Glacier) retrieval alone can be hours. Measure end-to-end; don't quote the restore command's runtime.
• Failover with no split-brain fencing. Promoting a standby while the old primary still accepts writes forks history irrecoverably. Fence (STONITH) the old primary and use quorum-based promotion before flipping traffic.
• DNS-only cutover with long TTL. A 300s+ TTL means clients keep hitting the dead primary long past promotion. Use TTL ≤30s, or a connection proxy that switches backends instantly.
• Backup job "succeeds" but the file is empty/corrupt. Exit-0 ≠ valid backup. Verify object size > expected floor, checksum, and a test-restore — not just the job's return code.
• Retention shorter than detection lag. Corruption noticed on day 10 with 7-day retention = no clean copy exists. Retain past your realistic time-to-detect, and keep a longer-interval cold copy.

Verify

1. RPO/RTO are written and tiered. Every stateful datastore has an explicit RPO and RTO number tied to a business tier (step 1) — not an implicit "nightly."
2. PITR proven, not assumed. Restore to an arbitrary timestamp between two base backups (e.g. 14:31:59 yesterday) lands the data at that second — proves continuous log archiving works, not just snapshots.
3. Automated restore drill is green and timed. The nightly/per-backup test-restore to scratch passes (structural + content + invariant checks) and its measured wall-clock ≤ RTO budget; a failure or RTO breach pages.
4. 3-2-1 + immutability holds. ≥3 copies across ≥2 accounts/regions, ≥1 with object-lock Compliance/retention-lock that even root cannot delete before expiry — confirm by attempting (and failing) to delete a locked object.
5. Independent blast radius. Deleting/encrypting the prod bucket/account leaves a usable backup intact in another account/region.
6. Each recovery shape has a tested path: full restore, PITR-to-timestamp, single-table logical restore, and corruption-to-new-instance — each with copy-pasteable commands in the runbook.
7. Failover fences and cuts over fast. A drill promotion fences the old primary (it cannot take writes post-promotion) and traffic moves via ≤30s-TTL DNS or a proxy; no split-brain divergence after.
8. Game day actually ran. A dated DR drill within the cadence (≤1 quarter; Tier 0 ≤1 month) failed over end-to-end, measured RPO/RTO vs target, and logged the gaps.

Done = every datastore has written RPO/RTO targets, PITR (base + continuous logs) restoring to an arbitrary timestamp, an automated restore drill that is green and within RTO, ≥1 immutable cross-account/region copy, and a runbook proven by a dated end-to-end DR drill — restore time measured, never merely planned.