Shipping the log
One database server is a single point of failure: if it dies, every app stops. Replication keeps a live copy on a second server. The primary takes every write; the standby, also called a replica, follows it.
The library app inserts Piranesi on the primary. As always, the primary first writes the change to its write-ahead log (WAL): record 1 inserts the row, record 2 commits it. Replication simply ships this same log.
A WAL sender process on the primary streams new records over a network connection to a WAL receiver process on the standby, which writes them to its own disk. The standby has now received up to record 2.
The standby’s startup process replays each received record against its own data files, exactly as crash recovery would. Replayed up to record 2: Piranesi now exists on both servers. The standby never runs the SQL; it repeats the logged change.
Using the copy
A hot standby answers read-only queries. The reports app asks it SELECT count(*) FROM books and gets 3, the same answer the primary would give. Reads can move to the standby and take load off the primary.
Only the primary may change data. When the reports app tries an INSERT on the standby, PostgreSQL refuses: cannot execute INSERT in a read-only transaction. Nothing is stored; the standby only follows the log.
Lag, sync and failover
Replication is asynchronous by default: the primary says saved without waiting. The app adds Circe, and a moment later the standby still counts 3. Records 3 and 4 arrive and replay only after that. The gap is the replication lag.
With synchronous_standby_names set, COMMIT also waits until the standby confirms that the commit record is safe on its disk. Wonder is saved on two servers before the app hears saved. The price: every commit waits one network round trip.
The primary’s machine dies, and writes stop. The standby notices the stream has gone quiet, but it keeps every record it received: all 6, because Wonder was committed synchronously. Every saved book is safe.
PostgreSQL does not fail over by itself: an operator or a tool runs SELECT pg_promote() on the standby. It finishes replaying, leaves recovery and accepts writes, and the apps reconnect to it, now the new primary.
What happens
;; step 1/10 · why
primary takes every write (INSERT, UPDATE, DELETE)
standby a second server that follows it
(also called a replica or secondary)
the standby starts as a full copy, a base backup:
pg_basebackup -h primary -U repl -D /srv/standby -R
-R writes standby.signal and primary_conninfo,
so it starts up as a standby of that primary
give the standby a name, in its postgresql.conf:
cluster_name = 'standby1'
(its WAL receiver connects with this name)
both now hold books: Dune, Coraline
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). 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.