Chapter 7 — Data Safety

Replicas protect you from a dead machine. Only backups protect you from yourself.

Prerequisites: SD QA Q13 — Write-ahead logs (why the WAL exists, crash recovery, checkpoints), SD 1 — Replication, WAL and lag (how the WAL becomes a replication stream, base backups, LSNs), SD 2 Ch 5 — Leader-follower replication (Part 9: WAL archiving and PITR in outline), Chapter 5 — Replication and Failover (what an async failover loses).

How to read this chapter

The WAL is covered in depth elsewhere, so Part 1 is a summary of what you need to say about it in the room. The rest of the chapter covers what the reader is thin on: what a backup actually is, how point-in-time recovery works step by step, what RPO and RTO mean in minutes for your design, and why a backup you have never restored is only a hope.

Every Postgres number in Parts 4 and 5 was measured on PostgreSQL 18.6 on a laptop, with a 2-million-row orders table (about 170 MB) and a local directory as the WAL archive. Absolute times will differ on your hardware. The mechanics and the ratios are what matter.

The interviewer's version of this topic is: "Someone ran DELETE FROM orders without a WHERE clause. Your replicas? What do you do?" A strong answer separates the failures that replication handles from the ones only backups handle, gives an RPO and RTO with numbers, and restores the lost rows without throwing away the good writes made since.

Part 1: The WAL in one minute, and what it does not protect

What you must be able to say (Q13 and SD 1 have the detail):

• Every change is written to the write-ahead log first. A commit is acknowledged only after its WAL record is flushed (fsync) to disk. The data pages are written later, in the background.
• After a crash, Postgres replays the WAL from the last checkpoint. Committed transactions come back. Uncommitted ones never become visible.
• The same WAL stream feeds replicas (streaming replication) and the backup archive (WAL archiving). Replicas replay it continuously. The archive stores it for later.

That last point is the trap. A replica replays everything the primary does, including your mistakes. When someone deletes 200,000 rows on the primary, every replica deletes them too, within its replication lag, which is usually under a second. Replication copies changes. It cannot tell a good change from a bad one.

Read the table as two failure families. Losing the machine is what replication handles: fast, automatic, and measured in seconds of lost writes. Losing the data — someone, or some code, changed it wrongly — is what backups handle: slower, and needing a person to choose the moment to go back to.

The replication-lag sim's Failover tab shows the first family. The primary crashes 5 s in and Replica 1 is promoted 1 s later. Run it with Commit set to Async, then Semi-sync, and watch the "Confirmed, lost" count. Replication lag in this tab is 300 ms and 700 ms.

The orders that vanish under Async are the replication RPO: whatever had committed on the primary but not reached the replica. Now picture a DELETE on the primary instead of a crash. With 300 ms of lag, the replicas have applied it before anyone has noticed. Semi-sync only guarantees the replica has the delete before the commit returns. For this family the only way back is the archive.

Part 2: RPO and RTO in minutes

An interviewer who asks "what's your RPO and RTO?" wants two numbers, and the mechanism that produces each one. Each protection mechanism gives a different pair, and the pair also depends on the failure.

RTO is a sum, and people quote only one term of it. For a PITR restore it is:

RTO = detect + decide + provision + copy base backup + replay WAL + verify + switch traffic (or merge rows)

A worked example. A 400 GB database, nightly base backup at 02:00, about 4 GB of WAL an hour, a bad delete at 14:37:

Two things follow. First, the copy step grows with database size and the replay step grows with time since the last base backup, which is why large databases take base backups more often, or incremental ones (Postgres 17 added pg_basebackup --incremental and pg_combinebackup). Second, for a partial bad delete, production does not have to go down at all. You restore to a separate server and copy the lost rows back while the app keeps serving. The RTO that matters to the affected customers is the full 1.5–2 h. The RTO for everyone else is zero. Say that in the room.

Part 3: Logical vs physical backups

Measured on the test database (138 MB: 1.4 million orders, 100,000 customers, after the runs in Part 4):

At this size the difference is small. At 400 GB the physical copy is limited by disk and network throughput, while the logical restore also rebuilds every index from scratch, which can take many hours.

Use both, for different jobs. Physical backups plus the WAL archive are your disaster recovery and your answer to bad writes. Logical dumps are for long-term archives you may need to read in five years on a different Postgres version, for pulling one table out quickly, and for major-version upgrades. A disk snapshot is a physical backup too. It is safe for Postgres if it captures all of the database's volumes at the same instant, because Postgres then treats it like a crash and replays the WAL it contains.

Managed Postgres. Most managed services take daily snapshots and archive WAL for you, and let you restore to any second inside a retention window, typically somewhere between 1 and 35 days. The restore almost always creates a new instance. It does not rewind the existing one. Plan for that: new hostname, new connection strings, and the same merge problem as in Part 4.

Part 4: Point-in-time recovery, run for real

The setup

The primary needs three settings. A restart is required for archive_mode:

# postgresql.conf on the primary
wal_level = replica                  # the default; enough for archiving and PITR
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# %p = path of the finished WAL segment, %f = its file name
# 'test ! -f' refuses to overwrite a segment already in the archive

In production archive_command is a backup tool (pgBackRest, Barman and WAL-G are common) that pushes segments to object storage, compresses and encrypts them, and enforces retention. A plain cp is enough to see the mechanism.

The run

1. Base backup: pg_basebackup -D base -Fp -X stream -c fast. It took 0.24 s and produced 204 MB.
2. Normal traffic: 60,000 new orders after the backup. 2,060,000 orders in total.
3. The bad write: a cleanup job meant for one trial customer runs DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE plan = 'trial'). 205,784 rows gone in 217 ms. Transaction ID 759.
4. Good traffic afterwards: 1,000 more orders. Production now has 1,855,216.
5. Restore into a new data directory, on another port, without touching production:

cp -R base restore && chmod 700 restore
cat >> restore/postgresql.conf <<'CONF'
port = 55433
archive_mode = off                       # the copy must not write into the real archive
restore_command = 'cp /archive/%f %p'    # how to fetch each archived segment
recovery_target_time = '2026-10-19 17:45:27.745+00'
recovery_target_action = 'pause'         # stop at the target and wait, read-only
CONF
touch restore/recovery.signal            # "start in recovery mode"
pg_ctl -D restore start

On Postgres 12 and later these settings live in postgresql.conf and an empty recovery.signal file starts recovery. Older guides use a recovery.conf file, which no longer exists.

The log tells you exactly where it stopped:

LOG:  starting point-in-time recovery to 2026-10-19 17:45:27.745314+00
LOG:  redo starts at 0/17000028
LOG:  restored log file "000000010000000000000018" from archive
LOG:  recovery stopping before commit of transaction 759, time 2026-10-19 17:45:29.790138+00
LOG:  pausing at the end of recovery

Three details that matter in practice:

• Replay is decided by commit time, not start time. A transaction that started before the target but committed after it is not applied. If you know the bad transaction exactly, recovery_target_xid = '759' with recovery_target_inclusive = false stops right before it. You can also stop at an LSN, or at a named point: before a risky migration, run SELECT pg_create_restore_point('before_migration_042'), and later restore with recovery_target_name = 'before_migration_042'. The named point was tested with a migration that dropped a column: the log said "recovery stopping at restore point before_migration_042", and the column was still there.
• Pause, then decide. With recovery_target_action = 'pause' (the default), the server stops at the target and accepts read-only queries. If you went too early or too late, change the target and start again from the base backup. Once you promote (SELECT pg_wal_replay_resume()), the restore leaves recovery for good.
• Promotion starts a new timeline. The log said "selected new timeline ID: 2" and wrote 00000002.history. New WAL is named 00000002…, so it can never be confused with the original history in a shared archive. Timeline numbers are how Postgres tells "after the restore" from "the original world".

Merging the lost rows back

Restoring the whole database to 17:45:27 would also throw away the 1,000 good orders written after the delete. In a real system that is hours of orders, payments and signups. So you don't point the app at the restore. You copy the lost rows out of it:

-- on the paused restore (read-only is enough for COPY ... TO)
\copy (SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id
       WHERE c.plan = 'trial') TO 'recovered_orders.csv' CSV
-- COPY 205784, 68 ms

-- on production
CREATE TABLE orders_recovered (LIKE orders);
\copy orders_recovered FROM 'recovered_orders.csv' CSV
INSERT INTO orders
SELECT * FROM orders_recovered r
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.id = r.id);
-- INSERT 0 205784, 0.6 s
SELECT count(*) FROM orders;   -- 2,061,000 = 2,060,000 before the delete + 1,000 after

This worked cleanly because deleted rows only need to come back. Bad UPDATEs are harder. If good code has since changed some of the same rows, you have three versions of each row: before the bug, after the bug, and now. A script has to decide per row, and usually per column, which one wins. That decision is a product question as well as an engineering one, so the incident lead should get someone who owns the data involved.

Replay speed, and what fills a disk

A second run generated 4.9 GB of WAL after a fresh base backup: six full-table UPDATEs, then a bad delete. The restore replayed all 307 segments and paused at the target in 21.8 s, about 225 MB/s, with a local archive on a laptop SSD. Use it to estimate the replay term in Part 2, and remember that fetching segments from object storage adds latency per segment. Backup tools help here by prefetching segments in parallel.

Then the archive was broken on purpose: archive_command = 'false'. Postgres does not drop WAL it has not archived, so it kept every segment:

When the command was fixed, the backlog was archived within seconds and pg_wal shrank after the next checkpoint. Two lessons. A broken archive silently ends your PITR window: backups still "run", but the WAL that makes them useful is missing. And it fills the primary's disk. A full pg_wal stops the primary. Alert on pg_stat_archiver.failed_count increasing and on the age of last_archived_time.

Part 5: Retention, encryption and the 3-2-1 rule

Retention is a time-to-detect decision. You can restore to any moment between your oldest kept base backup and now, if all the WAL in between is kept. Data loss that is found quickly (a DELETE noticed in 10 minutes) needs a few days of retention. Silent corruption — a bug that wrote wrong values for three weeks — needs retention longer than your worst detection time. A common layered setup:

Retention has a cost on the other side. Users who asked to be deleted still exist in old backups. The usual answer is to document the retention period and let backups expire on schedule. Some teams encrypt each tenant's data with its own key, so deleting the key makes every copy unreadable.

Encryption. Backups contain everything, so encrypt them at rest and in transit. Then treat the key as part of the backup. If the key lives in the same account as the database and that account is lost, the backups are unreadable. Keep keys and backups outside the blast radius of the system they protect.

3-2-1 is the old rule: three copies of the data, on two different kinds of storage, one of them off-site. In cloud terms: the live database, a backup in object storage in the same region, and a copy in another region in a different account. Many teams add two more numbers: one copy that is immutable (object storage with a write-once lock, so nobody can delete it before its retention ends, not even an admin), and zero errors in the last restore test.

The separate account and the lock exist for one failure: someone with production credentials deleting the backups too. That person might be an attacker, a buggy cleanup script, or a tired engineer. The role that writes backups should not be able to delete them.

Part 6: Restores you have actually tested

A backup is a claim that you can get the data back. Only a restore checks the claim. Things that go wrong in the first real restore after months of "successful" backups:

• archive_command has been failing for weeks. The base backups exist but the WAL between them doesn't, so PITR can only reach the moment each base backup was taken.
• The dump covers the database but not the roles, so the restore fails on GRANT statements and the app can't log in (pg_dumpall --globals-only was never run).
• The backup is encrypted with a key that only one departed engineer's account could read.
• The restore takes 9 hours. The RTO the company promised customers is 1 hour.
• The runbook describes a tool version, bucket name or hostname from two migrations ago.
• Nobody on call has permission to create servers in the disaster-recovery account.

None of these can be found by checking that the backup job is green. Automate the restore, and treat its result as a metric:

1. Every night, a job creates a fresh server and restores the latest base backup with a recovery target of "now minus 10 minutes".
2. It runs checks against the restored copy:
   • SELECT max(created_at) FROM orders is within 15 minutes of the target, which proves the WAL chain is complete.
   • Row counts of key tables are within 1% of production at the same moment.
   • pg_amcheck --heapallindexed reports no corruption. It checks tables and B-tree indexes for structural damage. On the test database it took 0.6 s and exited 0.
   • One read-only query the app depends on returns rows.
3. It records how long the restore took. That number is your measured RTO for the copy and replay steps. Alert when it grows past the budget, not only when the job fails.
4. It deletes the server.

Then, every quarter, run a human restore drill. Pick a realistic scenario ("restore the invoices table to 10:14 yesterday"), give it to someone who did not write the runbook, time every step, and fix whatever they got stuck on. The automated job proves the backups are good. The drill proves that people can use them under pressure.

Part 7: The real cause is usually you

Disks rarely destroy a well-run database these days. Replication and managed storage handle that. The data loss you are most likely to deal with is a correct system doing exactly what it was told: a cleanup job with a wrong WHERE, a migration that drops a column the old code still writes, a backfill that overwrites good values, an admin script run against production instead of staging. Design for that:

Bad migrations get their own treatment in Chapter 8: expand/contract keeps every step reversible, so a migration should never need PITR to undo.

Play it: orders are missing

When the Interviewer Breaks It

Try each one out loud before opening the answer.

Break 1: "At 14:37 someone ran DELETE FROM orders with a wrong WHERE clause. You have two replicas. What now?"

Break 2: "What are your RPO and RTO? Prove them."

Break 3: "Your backups run every night and the job is green. When did you last restore one?"

Break 4: "A bug has been writing wrong prices into 1% of orders for three weeks. Your retention is 14 days."

Break 5: "An attacker got your cloud admin credentials, dropped the database and deleted the backups."

The Causal Chains

Checklist: add this to any design

1. Two RPO/RTO pairs: one for losing a machine (replication) and one for bad data (backups), each with a number and how it was measured.
2. WAL archiving to object storage, continuous streaming if the RPO is seconds, and alerts on pg_stat_archiver.failed_count, archive age and pg_wal disk use.
3. Base backups often enough that replay time fits the RTO: daily, or incremental on large databases.
4. Retention longer than your worst realistic time to detect, layered: 14–35 days of PITR, plus monthly logical dumps kept for a year.
5. 3-2-1-1-0: three copies, two kinds of storage, one in another region and account, one immutable, zero errors in the last restore test.
6. Encrypted backups, with the keys outside the account they protect.
7. An automated nightly restore that checks freshness, counts and pg_amcheck, and records its duration as the measured RTO. A quarterly human drill.
8. A restore runbook that ends in a merge: restore to a side server, pause at the target, copy the affected rows back. Production stays up.
9. Guardrails against your own writes: read-only roles for humans, row limits on batch jobs, a restore point before every migration, a delayed replica, soft deletes where users expect undo.

What's next

Most of the data losses in this chapter started with a deploy: a job, a migration, a script. Chapter 8 — Observability and Recovery covers how you see them early, how you roll back safely, how to change a schema under live traffic without locking the table, and how to practise all of this with fault injection and game days before it happens for real.