Skip to content

Backup Strategies#

Database backup strategies decide how fast you can recover and how much data you might lose. Two numbers anchor every conversation: RPO (Recovery Point Objective, "how much data can I afford to lose") and RTO (Recovery Time Objective, "how long can I be down"). The strategy you pick is whatever satisfies both at acceptable cost.

flowchart LR
  DB[(Production DB)]
  SNAP[Storage snapshot<br/>EBS / ZFS / RBD]
  FULL[Full dump<br/>nightly]
  INC[Incremental<br/>changed blocks since last]
  DIFF[Differential<br/>changed since last full]
  WAL[WAL / binlog<br/>continuous]
  OBJ[(Object storage<br/>S3 / GCS, versioned)]
  COLD[(Cold archive<br/>Glacier)]

  DB --> SNAP --> OBJ
  DB --> FULL --> OBJ
  DB --> INC --> OBJ
  DB --> DIFF --> OBJ
  DB --> WAL --> OBJ
  OBJ -.lifecycle.-> COLD

  classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
  classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
  classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
  class DB datastore;
  class SNAP,FULL,INC,DIFF,WAL service;
  class OBJ,COLD storage;

A typical production setup combines several layers: nightly full backups for the simple restore path, hourly incrementals for finer granularity, and continuous WAL or binlog shipping that lets you replay every committed transaction up to the second of a disaster. The combination gives you point-in-time recovery (PITR) inside a defined window.

The 3-2-1 rule is the survival heuristic: keep 3 copies of data, on 2 different media types, with 1 copy offsite. Cloud-native equivalent: primary DB plus same-region snapshots plus cross-region object storage plus a periodic glacial archive. And a backup you have never restored is not a backup, it is hope.

Problem statement

Your primary Postgres cluster is corrupted by a bad migration at 14:32. The last nightly backup ran at 02:00. A developer asks "can we restore to 14:31 and lose only the last minute?" Walk through what your backup strategy must look like to answer "yes", how big the storage and restore time will be, and how you would actually verify it works before the incident.

RPO and RTO drive the design#

  • RPO (Recovery Point Objective): maximum acceptable data loss. "RPO = 5 minutes" means after a disaster you may lose up to 5 minutes of writes.
  • RTO (Recovery Time Objective): maximum acceptable downtime. "RTO = 1 hour" means service must be back within 60 minutes.

Cheap backups give big RPO/RTO. Real-time replication gives near-zero RPO/RTO but costs more. Every backup strategy is a point on this curve.

flowchart LR
  L1[Nightly full only<br/>RPO ~24h, RTO hours]
  L2[Full + incremental<br/>RPO ~1h, RTO ~1h]
  L3[Full + WAL shipping<br/>RPO seconds, RTO ~30m]
  L4[Sync replica + WAL<br/>RPO ~0, RTO seconds]
  L5[Multi-region active-active<br/>RPO 0, RTO 0]

  L1 --> L2 --> L3 --> L4 --> L5

  classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
  classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
  classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
  class L1,L2 storage;
  class L3 service;
  class L4,L5 datastore;

Backup types#

Full backup. A complete copy of the dataset at a point in time. Simple to restore (one file, one operation). Expensive in storage and IO. Run weekly or daily.

Incremental backup. Captures only what changed since the last backup of any kind. Cheap to take, expensive to restore (must apply full plus every increment in order, and a single missing increment breaks the chain).

Differential backup. Captures everything changed since the last full. Bigger than incremental, smaller than full. Restore is two operations: latest full plus latest differential.

flowchart LR
  F0[Full<br/>Sun]
  I1[Inc<br/>Mon]
  I2[Inc<br/>Tue]
  I3[Inc<br/>Wed]
  D1[Diff<br/>Mon]
  D2[Diff<br/>Tue]
  D3[Diff<br/>Wed]

  subgraph Incremental[Incremental chain]
    F0 --> I1 --> I2 --> I3
  end
  subgraph Differential[Differential chain]
    F0 --> D1
    F0 --> D2
    F0 --> D3
  end

  classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
  class F0,I1,I2,I3,D1,D2,D3 storage;

Snapshot. A point-in-time view of a storage volume, usually copy-on-write, taken in seconds. EBS, ZFS, LVM, RBD. Fast to create but tied to the storage layer; a corrupt filesystem layout is captured too.

Logical backup. Tool-level dump (pg_dump, mysqldump, mongodump). Portable across versions, slow to restore, no PITR by itself.

Physical backup. Block-level copy of data files (pg_basebackup, Percona XtraBackup). Faster restore, tied to engine version, supports PITR when combined with logs.

WAL shipping and point-in-time recovery#

The trick that lets you restore to "14:31, one minute before the bad migration":

  1. Take a periodic physical full backup (the base).
  2. Continuously ship the database's write-ahead log (Postgres WAL, MySQL binlog, SQL Server transaction log) to object storage.
  3. On restore: load the most recent base older than the target time, then replay WAL segments up to the desired timestamp.
sequenceDiagram
  participant DB as Primary DB
  participant WAL as WAL archive
  participant S3 as Object storage
  participant R as Restore process

  DB->>WAL: writes commit record
  WAL->>S3: ship segment 0001
  WAL->>S3: ship segment 0002
  note over DB: 14:32 corruption
  R->>S3: fetch base backup (02:00)
  R->>R: restore base
  R->>S3: fetch WAL segments
  R->>R: replay until 14:31:59
  R-->>DB: promoted, ready

This gives you RPO equal to your WAL shipping interval (seconds to a minute) and RTO equal to base-restore time plus WAL replay time (often 10 to 60 minutes for a TB-scale DB).

The 3-2-1 rule#

Classic survival rule that still applies in the cloud:

  • 3 copies of the data.
  • On 2 different media (or in the cloud: 2 storage classes / providers).
  • With 1 copy offsite (or in the cloud: a different region).

Cloud translation that real teams use:

Layer Example
Copy 1 Primary DB on a regional cluster
Copy 2 Same-region storage snapshot (EBS, RDS auto-backup)
Copy 3 Cross-region versioned object storage (S3 with versioning + Object Lock)
Cold Lifecycle into Glacier after 90 days

Object Lock or WORM is critical against ransomware that targets backups: even root cannot delete locked objects until the retention expires.

Restore drill (gameday)#

A backup you have never restored is not a backup. Run a quarterly drill:

# Pseudocode for a scripted gameday
def restore_drill():
    # 1. Spin up isolated DB in a sandbox account
    target = provision_sandbox_db()

    # 2. Restore latest base + replay WAL to 1h ago
    restore_base(target, source_bucket="prod-backups")
    replay_wal(target, until=now() - timedelta(hours=1))

    # 3. Verify with smoke checks
    assert target.row_count("orders") > 0
    assert target.checksum("config") == expected_checksum()

    # 4. Record RTO actual
    metrics.emit("rto.actual_seconds", drill_duration)
    teardown(target)

Tracking rto.actual_seconds over time catches silent regressions (snapshot got bigger, restore script broke after a Postgres upgrade, etc).

Choosing retention#

Common pattern:

  • Hourly WAL: 7 days.
  • Daily full: 30 days.
  • Weekly full: 90 days.
  • Monthly: 1 to 7 years (compliance: GDPR, HIPAA, SOX have different rules).

Storage cost grows with retention. Use object storage lifecycle rules to tier into cheaper classes automatically.

Gotchas#

  • Backup is not DR. A cross-region backup is part of DR but not enough by itself; you also need standby compute, network, and runbooks.
  • Encryption keys. A backup encrypted with a KMS key in the same region as the source is useless if the region is down. Replicate keys.
  • Schema drift on restore. Restoring an old logical backup into a newer schema can fail; keep a migration history file alongside backups.
  • Hot vs cold backups. A cold backup (DB stopped) is consistent but takes downtime. A hot backup needs WAL replay to be consistent (pg_basebackup handles this automatically).
  • Snapshot is not a backup. EBS snapshots in the same account are gone if the account is compromised. Copy to a separate account or storage tier.
  • Ransomware targets backups first. Versioning, Object Lock, and an offline copy are mandatory now, not optional.
  • Logical backup of 5 TB takes a day. Plan for physical + WAL for anything beyond ~500 GB.

Quick reference#

Numbers that drive the design#

  • RPO: how much data loss is OK. Tells you backup frequency.
  • RTO: how long can we be down. Tells you restore mechanism.
  • Cheap backups -> big RPO/RTO. Replicas -> near zero, more cost.

Backup types#

Type Speed to take Storage Restore complexity
Full Slow Largest One operation
Differential Medium Medium Full + latest diff
Incremental Fastest Smallest Full + every inc since
Snapshot Seconds COW, cheap Mount and go
Logical dump Slow Compressible Slow restore
Physical Fast Large Fast restore, version locked

WAL shipping / PITR#

  • Continuously stream WAL or binlog to object storage.
  • Restore = base backup + replay WAL up to target timestamp.
  • RPO ~ shipping interval (seconds). RTO ~ base restore + replay (minutes to hours).
  • Postgres: pg_basebackup + archive_command -> S3. Tools: wal-g, pgBackRest, barman.
  • MySQL: xtrabackup + binlog. SQL Server: full + log shipping.

The 3-2-1 rule#

  • 3 copies of data.
  • 2 different media or storage classes.
  • 1 copy offsite or cross-region.
  • Cloud: primary + same-region snapshot + cross-region versioned bucket + Glacier.

Retention defaults#

  • WAL: 7 days hot.
  • Daily full: 30 days.
  • Weekly: 90 days.
  • Monthly: 1 to 7 years for compliance.

Ransomware defenses#

  • Object versioning + Object Lock (WORM).
  • Separate account / project for backup bucket; least-privilege IAM.
  • An offline or air-gapped copy for the last-resort restore.
  • Backup encryption keys replicated across regions.

Restore drill checklist#

  • Provision sandbox identical to prod size.
  • Restore from prod backups (not from a local copy).
  • Replay WAL to a known target time.
  • Run smoke queries (row counts, checksums).
  • Record rto.actual metric and compare to objective.
  • Run quarterly minimum.

Anti-patterns#

  • Backups in the same account / region as the primary only.
  • "We have replicas, we do not need backups" - replicas propagate DROP TABLE.
  • Untested restore scripts.
  • Encrypted backups with a key only the prod cluster can fetch.
  • Logical pg_dump as the only backup for a multi-TB DB.
  • Retaining everything forever -> cost blows up; ignore lifecycle rules.

Interview probes#

  • Q: "Difference between snapshot and backup?" A: Snapshot is a point-in-time storage view, often same medium; backup implies a separate, verifiable copy. Snapshots are a building block.
  • Q: "How would you achieve RPO of 1 minute?" A: WAL shipping with archive_timeout=30s, or sync replica plus PITR.
  • Q: "How do you protect against ransomware?" A: Versioning, Object Lock, separate account, offline copy.
  • Q: "Replicas vs backups?" A: Replicas protect against hardware failure; backups protect against logical errors and ransomware. Need both.

Refs#

  • Postgres docs, Continuous Archiving and Point-in-Time Recovery (16.x).
  • AWS Builders' Library, Building dashboards for operational visibility and Backup and restore whitepapers.
  • W. Curtis Preston, Modern Data Protection (O'Reilly, 2021).
  • NIST SP 800-34 Rev. 1, Contingency Planning Guide (2010).

FAQ#

What is the 3-2-1 backup rule?#

Keep three copies of your data, on two different storage media, with at least one copy offsite. This protects against device failure, site disaster, and most ransomware events.

What is the difference between RPO and RTO?#

RPO is recovery point objective, how much data loss you can tolerate. RTO is recovery time objective, how long you can be down. Backup design picks the cheapest strategy that meets both.

What is point in time recovery?#

Point in time recovery restores a database to any exact moment by replaying write-ahead logs on top of a base snapshot. It is the standard recovery mechanism for Postgres, MySQL, and managed cloud databases.

Full vs incremental backups, which is faster?#

Incremental backups are faster to take but slower to restore because every increment since the last full must replay. Full backups are simpler and faster to restore but take more time and storage to capture.

How often should I test database backups?#

Restore drills should run at least monthly into an isolated environment with checksum verification. Untested backups are not backups, and silent corruption is the most common failure mode.