Backup, Recovery & Durability

Table of Contents#

1. Why Replication Is Not a Backup
2. RPO and RTO — The Two Numbers That Define Everything
3. Full, Incremental, and Differential Backups
4. Point-in-Time Recovery
5. The Backup Strategy Decision Tree
6. ACID — The Foundation Underneath All of This
7. Isolation Levels — The Most Misunderstood ACID Letter
8. Durability, Revisited — How "Durable" Actually Gets Guaranteed
9. The 3-2-1 Backup Rule
10. Testing Backups — The Step Everyone Skips
11. A Full Worked Disaster Recovery Scenario
12. Common Mistakes
13. Worked Practice Problems
14. Summary and What's Next

---

Why Replication Is Not a Backup#

This is, without exaggeration, one of the single most important, most commonly tested distinctions in this entire course — a genuine, real-world source of catastrophic mistakes.

Diagram

A genuinely important, memorable interview line, worth having ready verbatim: "Replication protects you against a machine dying. It does NOT protect you against a human (or a buggy application) deleting or corrupting data — that mistake gets faithfully, instantly replicated everywhere, just as reliably as any legitimate write. Only a real, point-in-time backup protects against THAT category of disaster." This directly connects to the disaster recovery topic (topic 11) in this course, where this exact distinction becomes the foundation for a proper resilience strategy.

---

RPO and RTO — The Two Numbers That Define Everything#

Before designing any backup strategy, two specific numbers need to be explicitly defined — genuinely foundational vocabulary that gets its own full deep dive in the Disaster Recovery topic (topic 11), but is introduced here since it directly drives every backup decision in this Part.

Diagram

Simple analogy: RPO is "how far back in time would we have to rewind if disaster struck right now" — RTO is "how long would the theater be dark while we set the rewound scene back up." Both are genuine, deliberate business decisions (not purely technical ones) that should be made explicitly, in advance, not discovered by accident during a real incident.

A worked numeric example, worth being able to reproduce: if a team backs up their database every 6 hours, their RPO is (in the worst case) nearly 6 hours — a disaster striking right before the next scheduled backup could lose almost 6 hours of data. If restoring from that backup and getting the application fully back online takes 2 hours, their RTO is 2 hours. Tightening either number costs more — a 5-minute RPO requires far more frequent (or continuous) backup activity than a 6-hour RPO, and a 5-minute RTO requires much more automated, rehearsed, and expensive recovery tooling than a "we'll figure it out over a few hours" RTO.

---

Full, Incremental, and Differential Backups#

Diagram

Diagram

The genuinely important tradeoff worth stating explicitly: incremental backups are cheapest to take but slowest and riskiest to restore from (if even ONE incremental file in the chain is corrupted or missing, everything after it in the chain becomes unusable); differential backups trade a bit more storage for a meaningfully simpler, faster, more robust restore process. Most real-world backup strategies use a hybrid: periodic full backups (say, weekly), with differential or incremental backups filling the gaps in between, and continuous WAL archiving (below) filling the smallest gaps of all.

---

Point-in-Time Recovery#

This is where the write-ahead log (WAL) from Part 1 comes back — this time as the mechanism enabling recovery to any specific moment, not just to whenever the last full/incremental backup happened to be taken.

Diagram

Copy
# PostgreSQL: configure continuous WAL archiving (the foundation
# that makes point-in-time recovery possible at all)
# In postgresql.conf:
#   archive_mode = on
#   archive_command = 'cp %p /backup/wal_archive/%f'

# Recovery: restore the full base backup, then tell PostgreSQL
# exactly how far to replay WAL
# In recovery configuration:
#   restore_command = 'cp /backup/wal_archive/%f %p'
#   recovery_target_time = '2026-06-01 14:44:59'

Why point-in-time recovery is such a powerful, genuinely important capability, worth stating explicitly: without it, you're limited to restoring to whatever moment your last backup happened to be taken at — potentially losing hours of otherwise-legitimate work along with the bad delete you're trying to undo. Continuous WAL archiving lets you rewind to the EXACT SECOND right before the mistake happened, and not one moment earlier than necessary.

---

The Backup Strategy Decision Tree#

Diagram

A strong, senior-level synthesis line, tying Parts 1 and 3 of this series together: "RPO and RTO requirements determine whether replication (Part 1) alone is sufficient, or whether a genuine backup strategy is also required — and they're solving different problems. Replication with fast automatic failover can give you an excellent RTO (a warm standby is ready in seconds), but it gives you ZERO protection against the RPO-destroying scenario of a bad DELETE being faithfully replicated everywhere. A complete strategy needs both."

---

ACID — The Foundation Underneath All of This#

ACID is the classic set of guarantees a relational database transaction provides — worth knowing cold, since it's asked constantly, and directly underlies why backups and recovery even work the way they do.

Diagram

Why "Durability" is the direct link back to the write-ahead log: a transaction is only considered truly "committed" once its change has been safely written to the WAL (durable, on disk) — this is precisely the mechanism that guarantees a transaction genuinely survives a crash the instant after the database confirms it succeeded, and it's the exact same mechanism replication (Part 1) and point-in-time recovery (this Part) both build on.

---

Isolation Levels — The Most Misunderstood ACID Letter#

A genuinely deep, frequently misunderstood topic — worth a real, dedicated treatment, since a shallow "just say ACID means atomic, consistent, isolated, durable" answer doesn't demonstrate real understanding.

Diagram

The core, fundamental tradeoff worth stating explicitly, directly reusing the CAP/PACELC latency-vs-consistency framing from the Reliability & Architecture Patterns series: stronger isolation levels provide stronger correctness guarantees, at the cost of more locking/coordination overhead and therefore lower concurrency/throughput. SERIALIZABLE is the safest but slowest under real concurrent load; READ COMMITTED (the actual default in PostgreSQL, and many other systems) is a practical, widely-used middle ground.

A concrete, worked example of why this matters — the "non-repeatable read" problem under READ COMMITTED:

Diagram

Why this is worth knowing concretely rather than abstractly: an application performing some calculation based on reading the same value twice, assuming it won't change mid-transaction, can produce subtly incorrect results under READ COMMITTED — this is exactly the kind of bug that's rare, hard to reproduce, and genuinely caused by choosing (or defaulting to) too weak an isolation level for a specific use case that actually needed stronger guarantees.

---

Durability, Revisited — How "Durable" Actually Gets Guaranteed#

Connecting directly back to the durability discussion in the SRE Fundamentals series (Part 1) — here's the concrete, mechanical "how."

Diagram

Why the fsync() step matters, and why skipping it is a genuinely dangerous, real-world misconfiguration worth knowing about: operating systems normally buffer disk writes in memory for performance, only flushing to physical disk periodically. A database that skips the explicit fsync() step (sometimes done deliberately, in some configurations, purely for extra write speed) can report a transaction as "committed" while the actual data is still only sitting in a memory buffer — if the machine crashes or loses power before that buffer flushes, the "committed" transaction is silently, permanently lost, despite the database having told the application it succeeded. This is exactly why "durability" is a real, deliberate engineering guarantee that has to be correctly configured, not an automatic property of simply "using a database."

---

The 3-2-1 Backup Rule#

A simple, extremely widely cited, genuinely practical rule of thumb — worth knowing by name and being able to explain immediately.

Diagram

Why each specific number matters, worth being able to explain the reasoning, not just recite the rule: "3 copies" protects against losing any single copy to a normal failure. "2 different media/systems" protects against a systemic failure or bug affecting one specific storage technology/vendor entirely (e.g., a bug in one cloud provider's storage backend). "1 off-site" protects against a genuinely catastrophic, location-specific event (a fire, a flood, an entire data center or region going down) — directly connecting to the multi-region discussion from the Reliability & Architecture Patterns series and previewing the Disaster Recovery topic (topic 11).

---

Testing Backups — The Step Everyone Skips#

This closes the loop with a principle already established firmly, twice, elsewhere in this course (the Kubernetes Deep Dive series' etcd backup discussion, and the chaos engineering philosophy from the Incident Management series) — worth restating one final time, specifically for database backups, since it's genuinely that important.

Diagram

The strongest, most concrete interview answer to "how do you know your backups actually work": "I don't just trust that backups are running — I schedule regular, actual restore drills, in a non-production environment, and treat a successful restore as the real definition of 'backups are working,' not just a green checkmark on a backup job's completion status."

---

A Full Worked Disaster Recovery Scenario#

Tying every concept in this entire three-part series together into one complete, realistic story.

The incident: at 2:45 PM on a Wednesday, an engineer accidentally runs an unscoped DELETE FROM orders against production. Within seconds, the delete has replicated to every read replica (Part 1) — replication faithfully propagated the mistake, exactly as designed, and exactly as this Part warned it would.

Diagram

This single worked scenario demonstrates why every concept in this three-part series matters together: replication (Part 1) explains why the disaster spread everywhere instantly; the "replication is not a backup" distinction (this Part) explains why a completely separate recovery path was needed; point-in-time recovery (this Part) provided the actual fix; and the blameless postmortem culture from the SRE Fundamentals series turns this into a systemic improvement (a confirmation gate for unscoped deletes) rather than just a one-time crisis resolved and forgotten.

---

Common Mistakes#

---

Worked Practice Problems#

Problem 1: A company has automatic failover configured (Part 1) with a warm standby that can be promoted in under 30 seconds, and considers this "our disaster recovery plan." An engineer then accidentally drops a critical table in production. Walk through what actually happens, and why the existing plan doesn't help.

Answer: The DROP TABLE replicates to the warm standby within seconds, just like any other write — the standby now has exactly the same missing table as the primary. Automatic failover in this scenario accomplishes nothing useful; promoting the standby to primary just makes the (now also broken) standby the new primary. This is a direct, real-world illustration of "replication is not a backup" — the team needs a genuinely separate, point-in-time backup (ideally with continuous WAL archiving) to recover the table's data from before the drop, which their existing failover-only strategy never provided.

Problem 2: A team's RPO requirement is "no more than 5 minutes of data loss," but their current backup strategy is a full backup taken once every 24 hours, with no WAL archiving. What's the gap, and how would you close it?

Answer: A once-daily full backup gives a worst-case RPO of nearly 24 hours (a disaster striking right before the next scheduled backup could lose almost a full day of data) — wildly short of the stated 5-minute requirement. Closing this gap requires continuous WAL archiving on top of the periodic full backups, enabling point-in-time recovery to any specific moment (not just to the last full backup) — with WAL segments archived frequently (commonly every few seconds to a minute, depending on configuration), the actual achievable RPO drops to roughly that archiving interval, genuinely meeting a 5-minute (or tighter) target.

Problem 3: During a code review, you notice a financial reporting service reads an account's balance twice within the same database transaction to perform a calculation, running under the default READ COMMITTED isolation level. What subtle bug could this cause, and how would you fix it?

Answer: Under READ COMMITTED, another transaction could commit a change to that same balance between the two reads within this transaction — a "non-repeatable read" — meaning the calculation could silently use two different values for what the code assumes is the same, stable number, producing an incorrect result with no error or warning at all. The fix is either using a stronger isolation level (REPEATABLE READ or SERIALIZABLE) for this specific transaction, which guarantees the same row returns the same value throughout, or explicitly reading the value once and reusing that single, captured value for the entire calculation instead of reading it a second time.

---

Summary and What's Next#

• Replication is not a backup — it faithfully propagates every write, including catastrophic mistakes, everywhere, usually within seconds. Only a genuinely separate, point-in-time backup protects against human/application error and corruption.
• RPO (how much data loss is acceptable) and RTO (how much downtime is acceptable) are deliberate business decisions that should be explicitly defined before designing any backup or recovery strategy — they directly determine backup frequency and recovery tooling requirements.
• Full, incremental, and differential backups trade off backup cost against restore speed/robustness — most real strategies use a hybrid, layered with continuous WAL archiving to enable point-in-time recovery to nearly any exact moment.
• ACID underlies everything in this series — and Isolation specifically is the most commonly misunderstood letter, with real, concrete correctness implications (like non-repeatable reads) depending on which level is chosen.
• Durability is a real, deliberate engineering guarantee (built on the WAL plus an explicit fsync() to physical disk), not an automatic property of "using a database" — it can be silently weakened by misconfiguration.
• The 3-2-1 backup rule (3 copies, 2 different media, 1 off-site) is a simple, widely-used, genuinely sound rule of thumb for backup resilience.
• An untested backup is a hypothesis, not a verified safety net — exactly the same principle already established for etcd (Kubernetes Deep Dive series) and chaos engineering (Incident Management series) — regular, real restore drills are what actually earn trust in a backup strategy.

Continue to Part 4 (04-sql-fundamentals.md) to step back from distributed-systems-style reliability concerns and cover the SQL language itself from the ground up — the querying foundation every relational database in this series builds on.