PostgreSQL Deep Dive
by Sergey Okinchuk
When One Server Isn't EnoughThe Write-Ahead Log

Chapter Twelve · WAL, Backup, and Recovery

WAL, Backup, and Recovery

At 14:12 a migration script runs against production with a WHERE clause that matched more than anyone intended, and 90,000 orders rows are gone. Cartwheel has nightly pg_dump files and no WAL archive, so the best available answer is "restore last night and lose the day". Six topics build the thing that would have made the answer "restore to 14:11".

6 topics

The migration got through review. It was an ordinary expand-and-contract step of the kind Chapter 3 made routine, it had been run on staging, and the WHERE clause was wrong in a way that a smaller table would have made obvious and 40 million rows did not. By 14:14 Nadia has the number: 90,000 orders, placed over eleven days, no longer in the database. The last backup is from 02:00 and every order taken since is in it only in the sense that it is not.

What is missing is not a tool but a chain, and Postgres has already written most of it. Every one of those deletions was described in the write-ahead log before it touched a data page, because that ordering is what makes crash recovery possible at all. Keep those log records instead of letting them be recycled, pair them with a copy of the data directory, and "restore to 14:11" stops being a wish and becomes a procedure with a runtime. That is the whole arc of this chapter: the log, what a checkpoint does to it, what a commit actually promises, and the two kinds of backup built on top.

The chapter ends by refusing the comfortable version of the story. A backup job that reports success is evidence about a job, not about a database, and the two numbers that matter, how much is lost and how long it takes to come back, stay unknown until somebody restores a cluster with a stopwatch running. Two boundaries are worth stating up front. A replica is not a backup, and Chapter 13 is where replication belongs; and the settings argued here are the durability ones, chosen against recovery time rather than throughput, so they sit with the log rather than with Chapter 10's configuration work.

Log first, data later — and then keep the log
A change to a data page
→
Its WAL record is written first
→
COMMIT waits for the flush
→
A checkpoint writes the page
→
Segments archived, not recycledrestore to 14:11

Topics in This Chapter

Topic 61
The Write-Ahead Log
Log first, then data — one sequential write that makes a crash a replay instead of a catastrophe. LSNs, 16 MB segments, what each wal_level buys, why the first change to a page after a checkpoint carries the whole page, and the two things that fill pg_wal silently.
Durability
Topic 62
Checkpoints and the Cost of Durability
The periodic flush that bounds recovery time and lets WAL be recycled, and the I/O storm most "the database freezes every few minutes" reports turn out to be. Timed against requested checkpoints, why checkpointing more often produces more WAL, and how the interval ties to an RTO.
Checkpoints
Topic 63
What "Committed" Really Means
By default COMMIT waits for the WAL flush, and every setting that speeds it up weakens that sentence somewhere. The bounded loss of synchronous_commit = off against the unbounded loss of fsync = off, the remote levels, and storage whose flush is a lie.
Commit Durability
Topic 64
Logical Backups — pg_dump and pg_restore
A consistent snapshot of one database, portable across majors and down to a single table, taken without blocking anybody. The four formats and which one dumps in parallel, the globals everyone forgets, and the three properties that disqualify it as Cartwheel's only backup.
Logical Backup
Topic 65
Physical Backups and Point-in-Time Recovery
A base backup plus every WAL segment since, which together reach any moment inside the retention window. What makes an archive_command correct, the recovery procedure step by step, the five kinds of target, and why the server pauses when it gets there.
Point-in-Time Recovery
Topic 66
A Restore You Have Actually Tested
An untested backup is a belief, not a capability, and the only way to convert one into the other is a timed rehearsal on production-sized data. RPO and RTO as written commitments, what a rehearsal catches that verification cannot, and how to put 90,000 rows back.
Recovery Drill