# Databases & Storage Reliability — Part 3: Backup, Recovery & Durability > **Series:** Databases & Storage Reliability (3 of 8) > **Part 1:** `01-replication-and-failover.md` — Replication & Failover > **Part 2:** `02-sharding-and-partitioning.md` — Sharding & Partitioning > **Part 3:** This file — Backup, Recovery & Durability > **Part 4:** `04-sql-fundamentals.md` — SQL Language Fundamentals > **Part 5:** `05-nosql-database-types.md` — NoSQL Database Types > **Part 6:** `06-mysql-in-depth.md` — MySQL In Depth > **Part 7:** `07-dynamodb-in-depth.md` — DynamoDB In Depth > **Part 8:** `08-mongodb-in-depth.md` — MongoDB In Depth > **Questions:** `questions.md` ## Table of Contents 1. [Why Replication Is Not a Backup](#why-replication-is-not-a-backup) 2. [RPO and RTO — The Two Numbers That Define Everything](#rpo-and-rto--the-two-numbers-that-define-everything) 3. [Full, Incremental, and Differential Backups](#full-incremental-and-differential-backups) 4. [Point-in-Time Recovery](#point-in-time-recovery) 5. [The Backup Strategy Decision Tree](#the-backup-strategy-decision-tree) 6. [ACID — The Foundation Underneath All of This](#acid--the-foundation-underneath-all-of-this) 7. [Isolation Levels — The Most Misunderstood ACID Letter](#isolation-levels--the-most-misunderstood-acid-letter) 8. [Durability, Revisited — How "Durable" Actually Gets Guaranteed](#durability-revisited--how-durable-actually-gets-guaranteed) 9. [The 3-2-1 Backup Rule](#the-3-2-1-backup-rule) 10. [Testing Backups — The Step Everyone Skips](#testing-backups--the-step-everyone-skips) 11. [A Full Worked Disaster Recovery Scenario](#a-full-worked-disaster-recovery-scenario) 12. [Common Mistakes](#common-mistakes) 13. [Worked Practice Problems](#worked-practice-problems) 14. [Summary and What's Next](#summary-and-whats-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. ```mermaid graph TD Repl["Replication: keeps
replicas CONTINUOUSLY,
AUTOMATICALLY in sync
with the primary"] --> Prob["If someone accidentally
runs 'DELETE FROM users'
with no WHERE clause on
the PRIMARY, that DELETE
gets FAITHFULLY replicated
to every replica too —
usually within SECONDS"] Backup["A BACKUP: a SEPARATE,
POINT-IN-TIME copy,
DELIBERATELY disconnected
from ongoing changes"] --> BackupGood["Completely UNAFFECTED by
a mistake made AFTER the
backup was taken — it's
the ONLY thing standing
between a human error
and permanent data loss"] ``` **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. ```mermaid graph TD RPO["RPO (Recovery Point
Objective): 'How much
DATA can we afford to
LOSE?' — measured in TIME
(e.g. 'we can afford to
lose up to 15 minutes
of data')"] --> RPONote["Directly determines HOW
OFTEN you need to back up
(or how tight your
replication needs to be)"] RTO["RTO (Recovery Time
Objective): 'How LONG can
we afford to be DOWN
while recovering?' —
ALSO measured in time
(e.g. 'we must be back up
within 1 hour')"] --> RTONote["Directly determines HOW
FAST your recovery
PROCESS needs to be —
which backup TYPE and
TOOLING you actually need"] ``` **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 ```mermaid graph TD Types[Backup Types] --> Full["FULL: a complete copy of
EVERYTHING, every time"] Types --> Incr["INCREMENTAL: only the
changes SINCE the LAST
backup (whether that was
a full or another
incremental)"] Types --> Diff["DIFFERENTIAL: only the
changes SINCE the LAST
FULL backup (regardless
of any incrementals
taken in between)"] ``` ```mermaid gantt dateFormat X axisFormat Day %d title Backup Schedule Comparison section Full Backups Only Full (large) :crit, a1, 0, 1 Full (large) :crit, a2, 1, 1 Full (large) :crit, a3, 2, 1 section Incremental Strategy Full (large) :crit, b1, 0, 1 Incremental (small) :b2, 1, 1 Incremental (small) :b3, 2, 1 section Differential Strategy Full (large) :crit, c1, 0, 1 Differential (growing) :c2, 1, 1 Differential (larger) :c3, 2, 1 ``` | | Full | Incremental | Differential | |---|---|---|---| | Backup size/time | Largest, every time | Smallest, consistently | Grows each day since the last full | | Restore complexity | Simplest — just restore the one file | Most complex — must replay EVERY incremental in order, from the last full | Simpler than incremental — only need the last full + the ONE most recent differential | | Restore speed | Fast (one file) | Slowest (many files to replay in sequence) | Faster than incremental (only two files needed) | | Storage cost over time | Highest | Lowest | Middle ground | **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. ```mermaid sequenceDiagram participant Backup as Full Backup (Monday 2am) participant WAL as Continuously Archived WAL participant Restore as Recovery Process Note over Backup,WAL: Disaster strikes Wednesday
2:47pm — a bad DELETE
ran at 2:45pm Restore->>Backup: 1. Restore the full
backup from Monday 2am Restore->>WAL: 2. Replay every WAL entry
from Monday 2am UP TO
(but not including)
2:45pm Wednesday Restore->>Restore: Database is now restored
to EXACTLY the moment
right before the bad
DELETE ran ``` ```bash # 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 ```mermaid flowchart TD Start{"What's the RPO
requirement?"} --> Tight{"Very tight
(minutes or less)?"} Tight -->|Yes| WAL["Continuous WAL archiving
+ periodic full backups —
enables point-in-time
recovery to nearly ANY
moment"] Tight -->|"No, hours is OK"| Periodic["Periodic full + incremental/
differential backups on a
schedule matching the RPO"] Start2{"What's the RTO
requirement?"} --> FastRTO{"Very fast
(minutes)?"} FastRTO -->|Yes| Standby["Maintain a WARM STANDBY
(a continuously-updated
replica, ready to promote —
Part 1) rather than relying
purely on restoring from
backup, which takes real time"] FastRTO -->|"No, hours is OK"| RestoreFromBackup["Restoring from backup on
demand is an acceptable
approach"] ``` **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. ```mermaid graph TD ACID[ACID] --> A["Atomicity: a transaction
either FULLY completes,
or has NO effect at all —
never partially applied"] ACID --> C["Consistency: a transaction
can only move the database
from one VALID state to
another VALID state
(respecting all constraints/
rules)"] ACID --> I["Isolation: concurrent
transactions don't
interfere with each
other's INTERMEDIATE state
(covered in depth below)"] ACID --> D["Durability: once a
transaction is COMMITTED,
it survives EVEN a crash
immediately afterward"] ``` **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. ```mermaid graph TD Levels["Isolation Levels
(weakest to strongest)"] --> RU["READ UNCOMMITTED:
can see OTHER transactions'
UNCOMMITTED changes
('dirty reads') — rarely
used in practice"] Levels --> RC["READ COMMITTED:
only sees COMMITTED
changes, but a value CAN
change between two reads
within the SAME
transaction ('non-repeatable
read')"] Levels --> RR["REPEATABLE READ:
the SAME row read twice
within one transaction
ALWAYS returns the same
value — but NEW rows
matching a query CAN
appear ('phantom read')"] Levels --> Ser["SERIALIZABLE:
transactions behave AS IF
they ran ONE AT A TIME,
in some serial order —
the STRONGEST guarantee"] ``` **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:** ```mermaid sequenceDiagram participant T1 as Transaction 1 participant DB as Database participant T2 as Transaction 2 T1->>DB: Read account balance: $100 T2->>DB: Deposits $50, COMMITS T1->>DB: Reads the SAME balance
AGAIN, within the SAME
transaction DB-->>T1: Now returns $150 -
the value CHANGED between
two reads in the SAME
transaction! ``` **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." ```mermaid graph TD Write["A write happens"] --> WAL["Written to the WAL
FIRST"] WAL --> Fsync["fsync() — an explicit
instruction to the OPERATING
SYSTEM: 'actually flush this
to physical disk RIGHT NOW,
don't just buffer it in
memory'"] Fsync --> Confirmed["ONLY THEN is the write
considered durably
committed"] ``` **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. ```mermaid graph TD Rule["The 3-2-1 Rule"] --> N3["3 total COPIES of your
data (the original +
2 backups)"] Rule --> N2["2 DIFFERENT storage
media/systems (not just
2 copies on the SAME disk
or same storage system)"] Rule --> N1["1 copy stored OFF-SITE
(a different physical
location/region entirely)"] ``` **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. ```mermaid graph TD Untested["An UNTESTED backup"] --> Risk["Is a HYPOTHESIS, not a
verified safety net — real,
documented incidents exist
where teams discovered
their backups were
CORRUPTED, INCOMPLETE, or
simply DIDN'T WORK, only
DURING an actual disaster,
when it was already too
late"] Tested["A REGULARLY, ACTUALLY
RESTORED backup
(e.g. monthly, in a
non-production
environment)"] --> Confidence["Is a VERIFIED, genuinely
trustworthy safety net"] ``` **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. ```mermaid flowchart TD A["2:45pm: Bad DELETE runs,
replicates to ALL replicas
within seconds"] --> B["2:47pm: Alerting (Observability
series) fires on an
anomalous drop in orders
table row count"] B --> C["On-call declares a SEV1
(Incident Management
series), assembles response"] C --> D["Decision: this needs a
POINT-IN-TIME RECOVERY,
NOT a failover — every
replica already has the
SAME bad delete"] D --> E["Restore last night's FULL
backup to a SEPARATE,
isolated recovery instance"] E --> F["Replay WAL from that full
backup up to 2:44:59pm —
ONE second before the
bad delete"] F --> G["Verify the recovered
data looks correct"] G --> H["Carefully, selectively
restore the missing orders
data back into production
(NOT a full destructive
swap, to avoid ALSO losing
legitimate orders placed
AFTER 2:45pm)"] H --> I["Blameless postmortem
(SRE Fundamentals series):
root cause was NO
confirmation step for
unscoped DELETE statements
in production"] ``` **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 | Mistake | Why It's Wrong | Fix | |---|---|---| | Treating replication as a substitute for backups | A destructive mistake (bad DELETE, corruption) replicates faithfully everywhere within seconds — replication provides zero protection against this | Maintain genuinely separate, point-in-time backups, disconnected from the live replication stream | | No explicitly defined RPO/RTO | Backup strategy decisions end up arbitrary rather than matched to actual business tolerance for data loss and downtime | Define RPO and RTO explicitly, as deliberate business decisions, before designing the backup strategy | | Relying purely on incremental backups with no periodic full backups | A single corrupted or missing incremental in a long chain can make everything after it unusable | Use a hybrid strategy — periodic full backups, with incremental/differential filling the gaps | | Assuming the database's default isolation level is always the right choice | Weaker isolation levels (like the common READ COMMITTED default) can produce subtly incorrect results for logic that assumes stronger guarantees | Explicitly choose the isolation level appropriate to each specific transaction's actual correctness requirements | | Configuring a database to skip `fsync()` for extra write speed, without understanding the tradeoff | A "committed" transaction can be silently, permanently lost on a crash before the OS flushes its buffer to physical disk | Only disable durability guarantees deliberately, with full understanding of the real data-loss risk being accepted | | Never actually test-restoring a backup | An untested backup is a hypothesis, not a verified safety net — real failures are regularly discovered only during an actual disaster | Schedule regular, real restore drills in a non-production environment | --- ## 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.