ericrisco Backups & disaster recovery
Start here
One question decides whether you have backups or a hope: if you restored right now, would it work — and how do you know?
If the answer is "we have a nightly job and it hasn't errored," you do not have backups. You have a job. A backup you have never restored is an untested assertion about the future. This skill exists to turn that assertion into evidence: pick the numbers, lay out the copies so ransomware can't reach them, and run a real restore on a schedule so the answer becomes "yes, we restored it last Tuesday in 41 minutes."
This is the cross-engine strategy + verification skill. Engine-specific SQL knobs (the actual archive_command, VACUUM, schema migrations) belong to ../postgresdb/SKILL.md. Encryption-key management as a general security control belongs to ../secure-coding/SKILL.md.
1. Decide RPO and RTO first — numbers, not adjectives
Everything downstream derives from two numbers. Never start from "what does the tool do."
- RPO = maximum acceptable data loss, the gap between the disaster and your last good copy. It sets backup frequency. "RPO 1 hour" means a copy at least hourly.
- RTO = maximum acceptable downtime until you're serving again. It sets topology: a snapshot restore is hours; a warm standby you promote is minutes.
Get a real number from whoever owns the revenue, not "as little as possible." Then map it:
| RPO target | Required cadence + mechanism |
|---|---|
| 24 h | Nightly full/snapshot is enough |
| ~5 min | Continuous change-log shipping: WAL / binlog / transaction-log → PITR |
| ~0 | Synchronous replica plus PITR (the replica covers hardware loss, PITR covers the bad DELETE) |
| RTO target | Required topology |
|---|---|
| Hours | Restore from snapshot / object-storage backup is fine |
| Minutes | Warm standby or read-replica you promote; pre-provisioned restore target |
| Seconds | Multi-AZ / failover cluster (and you still need backups for logical corruption) |
Rule: a replica is not a backup — it faithfully replicates your DROP TABLE in milliseconds. You need point-in-time recovery to step back before the mistake.
2. The 3-2-1-1-0 layout
The classic 3-2-1 rule grew two digits because ransomware now targets the backup infrastructure itself in ~96% of attacks — an attacker who can delete your backups has no reason to fear your backups. The current rule:
- 3 copies of the data (production + 2 backups). Why: one extra copy still dies with a correlated failure.
- 2 different media / storage types. Why: a single storage class has a single failure mode.
- 1 offsite. Why: fire, flood, region outage, or a billing-locked cloud account kills everything in one place. Offsite ≠ another folder on the same host.
- 1 immutable or offline. Why: a mutable copy an attacker (or a buggy script) can delete is not a safety net. See §5.
- 0 verification errors. Why: an unverified backup is Schrödinger's backup — both good and corrupt until you restore it. See §6–7.
Map it concretely: production Postgres (copy 1) → pgBackRest repo on local disk, different media (copy 2) → same repo replicated to an S3 bucket in another region with Object Lock (offsite + immutable) → pgbackrest verify + a scheduled test restore (the 0).
3. Pick the mechanism per data store
PITR availability is the column that decides whether you can hit a sub-hour RPO.
| Data store | Tool | PITR? | The one gotcha |
|---|---|---|---|
| Managed DB (RDS, Cloud SQL, Supabase, Neon) | Built-in automated backups | Yes | Set retention to your real window; know the ceiling (RDS caps at 35 days). Add a cross-region/cross-account copy you control. |
| Self-hosted PostgreSQL | pgBackRest or WAL-G + WAL archiving | Yes | Single-threaded archive_command falling behind = WAL storm (see §4). Use archive-async=y. |
| MySQL / MariaDB | Percona XtraBackup + binlog | Yes (binlog) | mysqldump alone has no PITR — you also need --single-transaction and binlog shipping. |
| Redis | RDB snapshot + AOF | Partial | Cache-first caveat: if Redis is just a cache, a backup may be pointless; if it's a system of record, enable AOF everysec and treat it like a DB. |
| Files / app data + object storage | restic or BorgBackup → Object-Lock bucket | n/a (versions) | Both dedup + AES-256 encrypt. restic = concurrent shared repos + faster restore; Borg = smaller repos but one exclusive lock per repo. |
| App config / secrets | Versioned + encrypted store | n/a | Back these up too, or your restored DB has nothing to connect to. Key must live somewhere the disaster doesn't. |
Concrete copy-paste config (pgBackRest stanza, RDS CLI, XtraBackup, restic/Borg) lives in references/engine-recipes.md.
4. PITR, generically
The model is the same for every engine that supports it: a base/full backup + a continuous change log replayed forward to a target time.
base backup (T0) ──► change log: WAL / binlog / txn-log ──► replay to "2026-06-02 13:59:00"
You restore the base, then replay the log up to one second before the bad event. That second is why PITR beats snapshots for logical corruption.
- Amazon RDS: a daily automated snapshot plus transaction logs shipped to S3 every 5 minutes, restorable to any second within a retention window of up to 35 days. Snapshots after the first are incremental. Trap: AWS Backup does not support a PITR restore into another region — you can copy the backup cross-region, but the restore-to-a-point-in-time happens in the original region. Plan failover accordingly.
- Self-hosted Postgres traps (both kill PITR silently):
- WAL storm: a single-threaded
archive_commandcan't keep up under write load,pg_wal/fills the disk, and Postgres stops accepting writes. Use async/parallel archiving (archive-async=y,--process-maxtuned to disk count — it's I/O-bound, not CPU-bound). - Retention trap: expiring WAL too aggressively means the chain to your target time is gone and PITR fails. Retain WAL at least as long as your oldest restorable full backup.
- WAL storm: a single-threaded
Engine commands are in references/engine-recipes.md. For the Postgres-internals side of this (writing the archive_command, tuning), use ../postgresdb/SKILL.md.
5. Offsite + immutability
- Immutable = Object Lock / WORM on the backup bucket: copies cannot be edited or deleted until the retention window expires, even by a root key. Enable it on a dedicated backup bucket.
- Retention ≥ 90 days. A 30-day default is often too short: malware commonly dwells for weeks before triggering, so a 30-day lock can expire on the very copies that predate the infection. 90+ days outlives typical dwell time.
- Offsite means a different blast radius, not a different folder. Different region, and ideally a different account/project with separate credentials — so a compromised application key cannot reach in and delete the backups. The app that writes backups should not hold the key that can delete them.
6. The tested restore — this is the part everyone skips
The number-one reason PITR fails in a real disaster is that it was never tested. Schedule restores like you schedule backups.
| Test | Cadence | Scope |
|---|---|---|
| File / single-object restore | Monthly | Pull one file/table back, in an isolated env, confirm integrity |
| Application recovery | Quarterly | Stand up the app against the restored data, run smoke queries |
| Full-environment failover | Annually | Rebuild the whole stack from backups in an isolated account/region |
Rules:
- Always restore into an isolated environment — never over production, never sharing its credentials.
- Record the actual recovery time every run and update your RTO to that reality. An RTO of "4 hours" that you've never measured is fiction.
- A restore test that "looks fine" isn't done until you've run a validation query / row-count / checksum that proves the data is correct, not just present.
The fill-in-the-blank restore runbook, the scheduled-test checklist, and the actual-RTO log format are in references/restore-runbook.md.
7. Verify & monitor
- Integrity-verify the backups themselves:
restic check/borg check/pgbackrest verifyre-read and checksum chunks. A backup that won't passcheckwon't restore. - Alert on three conditions: a backup job failed, the newest good backup is older than your RPO (backup-age check — silence is the dangerous failure mode), and a restore test is overdue.
- Wiring those alerts into a metrics/paging stack is a monitoring concern — own the what to alert on here, hand the how to
../monitoring/SKILL.md.
Anti-patterns
| Anti-pattern | Why it bites | Do instead |
|---|---|---|
| Backups on the same host/disk as the source | One disk/host failure takes the source and the backup together | Offsite copy, different blast radius (§2) |
| Never test-restored | The restore fails for the first time during the disaster | Monthly/quarterly/annual scheduled restores (§6) |
| "The replica is our backup" | It replicates your DROP TABLE faithfully |
Replica for RTO, PITR for the bad write (§1) |
| Mutable bucket the app key can delete | Ransomware/compromised key wipes backups too | Object Lock + separate credentials (§5) |
| 30-day retention only | Malware dwell time outlasts the lock | Retention ≥ 90 days (§5) |
pg_dump/dump to /tmp |
Reboot or full disk silently loses it; no PITR | Dedicated repo + WAL/binlog shipping (§3–4) |
| RPO promised, cadence can't meet it | "RPO 5 min" with a nightly job = up to 24 h loss | Derive cadence from RPO first (§1) |
Single-threaded archive_command under load |
WAL storm fills pg_wal, writes stop |
Async/parallel archiving (§4) |
| Encrypted backup, key only in the vault that's gone | Backup is recoverable but unreadable | Store the key in a separate blast radius (§3) |
| Trusting managed snapshots without knowing the ceiling | RDS caps at 35 days; longer needs an exported copy | Know the retention ceiling, export beyond it (§3) |
| Counting "job succeeded" as success | The job wrote a corrupt/empty archive | check/verify + a real test restore (§6–7) |
scripts/verify.sh <path-to-policy-or-runbook> lints a produced backup-policy/runbook artifact for the five pillars (RPO, RTO, offsite, immutability, scheduled-restore + verification). It is a completeness lint, not a backup executor.