Lesson 18 · At scale

Replication

One database server is a single point of failure. Follow PostgreSQL streaming replication step by step: every change becomes write-ahead log (WAL) records, a WAL sender streams them to a standby, and the standby replays them into its own copy. See reads served by the standby, writes refused there, replication lag, synchronous commit, and a failover with pg_promote().

14 min10 steps5 questionsAdvanced

Fig 18 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01In PostgreSQL streaming replication, what does the primary send to the standby?

A WAL sender streams the WAL records as they are written; the standby's startup process replays them against its own data files, as crash recovery would. The standby never re-runs the SQL.

02The reports app runs INSERT INTO books ... on a hot standby. What happens?

A standby is in recovery: it only follows the primary's WAL. It answers read-only queries, and every write must go to the primary.

03Replication is asynchronous. The app inserts Circe; the primary says saved. A moment later the reports app counts the books on the standby. What can it see?

By default COMMIT does not wait for any standby, so the standby can lag. Until it replays records 3 and 4, its count stays at the old value: a stale read.

04With synchronous_standby_names naming the standby and synchronous_commit = on, when does COMMIT return to the app?

With on, the primary waits for the standby to flush the commit record. remote_apply also waits until the standby has replayed it, so reads there see it straight away. Either way each commit costs a network round trip.

05The primary's machine dies. What does PostgreSQL do on its own?

PostgreSQL does not detect a failed primary by itself. After pg_promote() the standby leaves recovery, accepts writes and the apps reconnect to it. With asynchronous replication, commits that never reached it are lost.

Key terms

Glossary

Replication: Keeping a live copy of a database on another server by sending it every change.
Primary: The server that takes every write and produces the write-ahead log.
Standby (replica): A server that receives the primary's WAL and replays it into its own copy. A hot standby also answers read-only queries.
WAL record: One entry in the write-ahead log describing a change, such as a row insert or a commit. Its position is its log sequence number (LSN).
Streaming replication: The WAL sender process on the primary sends WAL records to the WAL receiver on the standby as they are written.
Replication lag: How far the standby is behind the primary: records written but not yet received or replayed.
Synchronous commit: A commit that also waits until a named standby confirms it has the commit record, so a failover does not lose it.
Failover: Making a standby the new primary when the old primary fails; in PostgreSQL with pg_promote() or pg_ctl promote.

Fine print

Notes and sources

The figure numbers WAL records 1, 2, 3 from the base backup. A real server marks each place in the log with a log sequence number (LSN), a byte position written like 0/3000110; pg_waldump lists the records (Heap INSERT, Transaction COMMIT).
The books table here has no primary key, so each insert logs one heap record. With a key, every insert also logs an index record, and the first change to a page after a checkpoint logs a full-page image.
With asynchronous replication a failover can lose the last commits the primary had confirmed but not yet streamed. Synchronous commit closes that gap, but only for the standbys it waits for.
If the only synchronous standby is down, commits on the primary wait until it comes back or synchronous_standby_names is changed. Real setups therefore name more than one standby, for example ANY 1 (s1, s2).
This lesson shows physical streaming replication: the standby is a block-for-block copy of the whole server. PostgreSQL also has logical replication (CREATE PUBLICATION and CREATE SUBSCRIPTION), which copies chosen tables row by row.
Replication is not a backup: a mistaken DELETE or DROP TABLE on the primary is streamed and replayed on the standby too. Backups and point-in-time recovery cover that.
The Python toy only shows the idea: log first, then replay the log on the copy. What PostgreSQL really guarantees is that a change’s log record reaches disk before the changed data page does. It prints "3 2" then "3 3 True".

Quiz Unlocks after the last step.