Backups

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:

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.

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.

textCopy code
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_command can't keep up under write load, pg_wal/ fills the disk, and Postgres stops accepting writes. Use async/parallel archiving (archive-async=y, --process-max tuned 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.

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.

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 verify re-read and checksum chunks. A backup that won't pass check won'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

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.