Backup & disaster recovery for a database of tens of TB

Question

IT Interview Practice · Accepted Answer

**Combine periodic snapshots with continuous WAL archiving for point-in-time recovery, replicate offsite/cross-region, and prove it with regular restore drills against explicit RPO/RTO targets.** A backup you have never restored is a hope, not a plan.

## Architecture

```text
Primary DB ──snapshot (daily)──▶ Object store (S3) ──cross-region replicate──▶ DR region
     │                               ▲
     └── WAL/binlog stream ──────────┘   (continuous, PITR to any second)
                                          │
                          restore drill ──┴──▶ verified restore ─▶ measure RTO, checksum RPO
```

## How it works

- **Snapshots** are a full copy of the volume/cluster (e.g. daily EBS snapshot or `pg_basebackup`). They're the base you restore *from* — but restoring only to last night loses a day of writes.
- **WAL / binlog archiving** streams every change since the last snapshot to the object store continuously. Restore = snapshot + replay WAL up to any chosen second. This is **PITR (point-in-time recovery)** — recover to 10:32:04, the moment before a bad `DELETE`.
- **Offsite / cross-region.** A backup in the same region as the DB dies with the region. Replicate to a second region (S3 cross-region replication) so a regional outage or ransomware doesn't take both.
- **Restore drills.** Periodically restore into a scratch environment, measure how long it took (**RTO**) and how much data you'd have lost (**RPO**), and checksum against prod.

## RPO vs RTO

- **RPO (Recovery Point Objective)** = how much data you can afford to lose. WAL archiving every 5 min → RPO ≈ 5 min. Synchronous replica → RPO ≈ 0.
- **RTO (Recovery Time Objective)** = how fast you must be back. Restoring 20TB from S3 can take hours; a warm standby replica cuts RTO to minutes.

## Interview perspective

The interviewer probes whether you know **snapshots alone aren't enough** (data loss between them), whether you can **define RPO/RTO in numbers**, and whether you **test restores**. Great answers stress that untested backups fail exactly when needed, and pick a strategy from the *business* RPO/RTO, not from convenience. A trap: assuming a read replica is a backup — replication faithfully copies a `DROP TABLE` too.

## Trade-offs & failure modes

- **Cost vs RTO.** A warm cross-region standby gives minutes of RTO but doubles infra cost; cold S3 restore is cheap but slow. Buy the tier the business actually needs.
- **Backup load.** Snapshotting a hot 20TB DB adds I/O; take snapshots from a replica.
- **Silent corruption.** WAL can archive a corrupt page; periodic full-restore + checksum catches it.
- **Retention & compliance.** Keep enough history (e.g. 35 days PITR + monthly archives) for legal needs; Glacier for cheap long-term.
- **When not to:** a tiny DB is fine with nightly `pg_dump` to S3 — full WAL + cross-region standby is overkill until data loss is measured in real money.

Architecture

How it works

RPO vs RTO

Interview perspective

Trade-offs & failure modes

Related questions