What does a solid database backup and disaster-recovery strategy actually look like? (RPO/RTO, point-in-time recovery)

#Start with the business question, not the tool

Before choosing a backup tool, an interviewer wants to hear that you start from two numbers the business must define:

• RPO (Recovery Point Objective) — how much data can we afford to lose, measured in time? If the RPO is 5 minutes, a nightly backup is not a backup strategy, it's a resignation letter.
• RTO (Recovery Time Objective) — how long can the system be down while we recover? An RTO of 15 minutes rules out "restore a 2TB dump from S3 and replay logs," full stop.

Every technical decision — backup frequency, replication topology, standby region — falls out of those two numbers. A senior answer names them explicitly instead of jumping straight to "we use snapshots."

#The backup taxonomy

• Full backup — a complete copy of the database at a point in time (pg_dump, mysqldump, a storage-level snapshot). Simple to restore, expensive to take often on a large database.
• Incremental / differential backup — captures only what changed since the last full (or last incremental) backup. Cheaper to take, more restore steps to reassemble.
• Physical (block-level) backup — copies the actual data files/disk blocks (pg_basebackup, EBS/LVM snapshots, Percona XtraBackup). Fast to take and restore, but tied to the same engine version and architecture.
• Logical backup — a dump of SQL statements or a portable data format. Slower on large datasets, but portable across versions and even engines, and lets you restore a single table.
• Continuous archiving (WAL/binlog shipping) — the piece that turns "backup" into "point-in-time recovery." The database continuously ships its write-ahead log (Postgres? WAL) or binary log (MySQL binlog) to durable storage. Combined with a periodic base backup, you can replay logs up to any timestamp.

#Point-in-time recovery (PITR)

PITR is the feature that answers "someone ran a bad UPDATE without a WHERE clause at 14:32 — get us back to 14:31." The mechanics:

1. Restore the most recent full/base backup taken before the target time.
2. Replay the archived WAL/binlog records from that backup's timestamp up to (but not including) the bad transaction.
3. Promote the restored instance once validated.

This is why continuous log archiving matters even if you also take nightly full backups: the full backup gives you a fast starting point, the logs give you sub-second granularity. Without log shipping, your recovery point is only ever as recent as your last full backup, no matter how disciplined your backup schedule is.

#Where the backup actually lives

A backup stored on the same host, same disk, or same availability zone as the primary is not a disaster-recovery backup — it protects you against DROP TABLE, not against a rack failure, an AZ outage, or ransomware that touches everything the app's credentials can reach. A real strategy layers:

• Same-region durable object storage (S3/GCS) with versioning and, ideally, object lock / immutability for a defined retention window (protects against an attacker or a bug deleting your backups too).
• Cross-region replication of that backup storage, sized to your RTO.
• For the highest tiers, a warm or hot standby in a second region that can be promoted, not just a backup file waiting to be restored.

#Failover: promoting a standby vs. restoring from backup

These are two different tools for two different failure classes:

• Standby promotion (a replica gets promoted to primary) is fast — seconds to a couple of minutes — and is the answer to "the primary died" or "the primary's AZ is gone." It does not protect you from logical corruption, because the replica faithfully replicated the bad DELETE too.
• Restore + PITR is the answer to "the data itself is wrong" — an application bug wrote garbage, a migration went sideways, a bad actor deleted rows. It is slower by nature, because you are rebuilding state from an earlier snapshot plus a log replay.

A mature DR plan has both, and knows which one a given incident calls for.

#The part everyone skips: testing the restore

An interview answer that stands out says this explicitly: an untested backup is a hypothesis, not a backup. Corruption in the backup file, a schema mismatch, an expired encryption key, insufficient IOPS on the restore target — all of these are invisible until you try to restore, and by definition you find out at the worst possible moment if the first restore attempt is the real incident. Mature teams run scheduled restore drills (monthly or quarterly), measure the actual RTO achieved, and track it against the target — and they treat "we've never tested this" as a production incident waiting to happen, not a checkbox.

🧠 Check your understanding
0/3 · 0/3 answered
1. A team takes a full backup every night at 2 AM and has no WAL/binlog archiving. What is their effective RPO?
Near-zero, since backups run dailyExactly 15 minutes, the industry defaultIt depends only on RTO, not on backup frequencyUp to 24 hours, since the last full backup is the only recovery point
2. Why is promoting a replica to primary NOT a substitute for point-in-time recovery?
Replicas use a different schema than the primaryPromotion always takes longer than a restoreA replica faithfully applies the same logical errors (bad DELETE/UPDATE) as the primaryReplicas cannot be promoted without downtime
3. What is the main reason to run scheduled restore drills instead of trusting that backups succeeded?
Restore drills replace the need for cross-region replicationA successful backup job does not guarantee the backup file is restorable, and failures often surface only during restoreDrills reduce the storage cost of backupsDrills are required by most compliance frameworks regardless of technical need