Replication

The 30-second version

Your app stores its data in a databaseDatabaseA program whose whole job is storing data safely and finding it again quickly — Postgres, MySQL, MongoDB, Redis. Your app usually talks to it over a network connection; embedded databases like SQLite run inside your program instead.: a program on some server whose job is to keep data safe and find it again. If that one server's disk dies, your data is gone. If it gets too busy, your whole app slows down. Replication fixes both by keeping full copies, called replicasReplicaA full copy of a database kept on another machine and updated as the original changes, for reading load and for taking over if the original dies., on other machines.

In the most common setup, one server is the leaderLeader / primaryThe one copy of a database that accepts writes. It records every change in a log and sends that log to the followers. Also called primary or master. (the teacher): it is the only one that accepts changes. The others are followersFollower / replica / standbyA copy of the database that receives the leader's log and replays it, so its data matches the leader's (a little later). It serves reads but refuses writes. (the students): they copy every change the leader makes, and they answer read-only questions. Press the button a few times:

Three reasons to replicate

The catch

Copying takes time. Usually only milliseconds, but sometimes seconds or minutes. During that time, the copies disagree, and a user who reads from the wrong one sees old data. This whole topic is about two questions:

1. When should the leader say “saved”? Before or after the followers have the change? (§05)
2. What do users see while the copies disagree, and how do you hide it? (§06–§07)

Then we'll handle the leader dying (§08), designs with several leaders or none at all (§09–§10), and running all this in production (§11).

Database basics you need

If you've used a database before, skim this section. If not, these are the only four ideas you need.

1. Tables, rows, and SQL

A relational database (one that stores data in tables) like PostgresPostgreSQL (Postgres)A popular open-source relational SQL database, known for correctness and features. Used throughout this site's database examples. stores data in tablesTable / row / columnHow a relational database stores data: a table (users) is like a spreadsheet; each row is one record (one user); each column is one field (name, email) with a fixed type., which look like spreadsheets: a users table has one row per user and columns like id and name. Each row has a primary keyPrimary keyThe column (or columns) that uniquely identifies each row, like users.id. The database refuses two rows with the same primary key and indexes it automatically., a column such as id that is unique per row. You talk to the database in SQLSQLStructured Query Language: the language for asking relational databases questions — SELECT name FROM users WHERE id = 7. Postgres, MySQL and SQLite all speak it.:

SELECT name FROM users WHERE id = 42;                -- read
INSERT INTO users (id, name) VALUES (43, 'Bo');      -- add a row
UPDATE users SET name = 'Ana B.' WHERE id = 42;      -- change rows
DELETE FROM users WHERE id = 43;                     -- remove rows

The first statement only reads. The other three are writes: they change data. That split matters a lot here, because in the usual setup only one server may accept writes, while reads can go to any copy.

2. Commit: the moment a change becomes real

A change becomes official when it is committed. Until then the database can still undo it. Several statements can be grouped into one transactionDatabase transactionA group of database changes that happen all together or not at all (COMMIT keeps them, ROLLBACK undoes them). How much other users can see or interfere with the half-done state depends on the isolation level. that commits all together or not at all (the Transactions topic covers this properly). When the database answers “committed” it promises durabilityDurabilityThe promise that once the database says “committed”, the data survives a crash or power cut — usually because it was written to disk (and fsynced) before replying.: the change will survive a crash or a power cut.

3. The write-ahead log

How can a database keep that promise quickly? Updating the right spots in big data files on every commit would be slow. Instead it first appends a short description of the change to the end of one file, the write-ahead logWrite-ahead log (WAL)An append-only file where a database records each change before applying it. After a crash it replays the log to recover; replicas can follow it to copy changes. (WAL), and forces it onto the disk with fsyncfsyncA system call that forces data the OS has buffered in memory to actually be written to the disk. Slow (often milliseconds), but without it a power cut can lose “written” data.. Appending to the end of one file is fast. The data files are updated later, in the background.

If the machine crashes, the database replays the log on restart and rebuilds whatever the data files were missing. Notice what the log gives us for free: an ordered, numbered list of every change ever made. Each entry has a position, and positions only go up.

4. Why this matters for replication

If another machine starts from the same data and replays the same log entries in the same order, it ends up with exactly the same data. That is the whole trick behind most replication: the leader writes its log as usual, and also streams it to the followers, which replay it. Postgres calls a position in the log an LSNLSN (log sequence number)Postgres's name for a position in the write-ahead log, written like 0/3000148. It only ever grows, so “has this replica replayed past LSN X?” is a simple comparison. (log sequence number); you'll see LSNs like 0/E041D58 in real output later.

Leader and followers

This setup goes by many names: leader/follower, primary/replica, primary/standbyHot standbyA Postgres follower that accepts read-only queries while it replays the leader's WAL. (A “warm” standby only replays and serves nothing.), and in older docs master/slave. They all mean the same shape:

• The leader is the only server that accepts writes (INSERT, UPDATE, DELETE). For each change it appends an entry to its logReplication logThe ordered, numbered list of every change the leader made. Followers replay it in the same order to end up with the same data., applies it, and sends the entry to every follower.
• Each follower receives the entries and applies them in log order. It answers reads, but refuses writes.
• The app sends writes to the leader and may send reads to any follower.

Why only one writer? Because then there's exactly one order of events. If two servers could both accept a change to the same row, they'd have to agree on which came first. One leader sidesteps that whole problem (§09 shows what happens when you don't).

Watch one write travel

1-- 1. on the leader
2UPDATE users SET name = 'Ana B.' WHERE id = 42;
3-- 2. on the leader
4INSERT INTO orders (id, user_id, total) VALUES (9, 42, 30);
5-- 3. on follower B
6SELECT name FROM users WHERE id = 42;

Two details from the stepper matter for everything that follows:

1. The leader doesn't wait for the followers here. It replied OK at step 2, before anyone else had the change. That choice, called asynchronous replicationAsynchronous replicationThe leader confirms a write as soon as it is safe on the leader's own disk and ships it to followers afterwards. Fast, but a leader crash can lose writes that were already confirmed., is the default almost everywhere. §05 looks at the alternative.
2. A follower's whole state is one number: the log position it has replayed up to. That makes restarts easy (“send me everything after 102”), and it's how you'll measure lag (§06) and fix stale reads (§07).

Followers refuse writes

Here's what a real Postgres replica says when you try to write to it (this is from the test setup used throughout this page: a primary on port 5543 and replicas on 5544 and 5545):

$ psql -p 5544 -c "INSERT INTO users VALUES (43, 'Bo')"
ERROR:  cannot execute INSERT in a read-only transaction

So your app needs to know which server is the leader, and send writes there. §07 shows the code; §08 covers what happens when the leader changes.

How does a new follower start?

A brand-new follower can't replay the log from the very first change ever made; that log was deleted long ago. Instead:

1. Take a base backupBase backup / snapshotA full copy of the leader's data taken at one moment, used to start a new follower. The follower then replays the log from that moment on to catch up.: a full copy of the leader's data files, made while the leader keeps running. Note the log position at which the copy started.
2. Copy it to the new machine and start the follower.
3. The follower asks the leader for every log entry after that position, replays them, and from then on keeps up with the live stream.

In Postgres, one command does steps 1–2 and writes the follower's configuration:

pg_basebackup -h primary.internal -U replicator -D /var/lib/postgresql/16/main -R -X stream

Here is the whole process, step by step:

A big database can take hours to copy this way. That's one reason teams keep spare followers around instead of building one only when they need it.

What gets shipped: statements, bytes, or rows

In §03 the log entries looked like “users 42: name = 'Ana B.'”. Real systems pick one of three formats for those entries.

1. Statement-based: send the SQL

The simplest idea: the follower re-runs the same SQL the leader ran. This is statement-based replicationStatement-based replicationShipping the SQL text itself (UPDATE … SET x = now()) for followers to re-run. Compact, but anything non-deterministic like now() or random() gives different results on each copy.. A statement that changes a million rows is still one short line to send. But anything whose result depends on when or where it runs gives a different answer on the follower, like now() and random(). So does anything that depends on the order statements run in, like auto-increment IDs when two inserts run at once. The follower quietly ends up with different data, and nothing reports an error. The widget at the end of this section lets you try it.

2. Physical: send the changed bytes

Physical replicationPhysical (WAL) replicationShipping the low-level write-ahead log: which bytes changed in which disk page. Followers become exact byte-for-byte copies; Postgres streaming replication works this way. ships the write-ahead log itself. Our diary in §02 was simplified: real Postgres WAL records don't say “user 42 got a new name”. They say which bytes changed in which disk pagePage (storage)The fixed-size block (often 4–16 KB) a database or OS reads and writes as one unit. Reading one byte means reading its whole page.. Here is the real WAL for one UPDATE, decoded with the pg_waldump tool:

$ pg_waldump -p primary/pg_wal -s 0/D513198 -e 0/D513218
rmgr: Heap   len (rec/tot): 74/74, tx: 61092, lsn: 0/0D513198, desc: HOT_UPDATE
      old_off: 5, new_off: 7, blkref #0: rel 1663/5/16384 blk 0
rmgr: Transaction len (rec/tot): 46/46, tx: 61092, lsn: 0/0D5131E8, desc: COMMIT

(Some fields removed and lines wrapped for width.) Reading it: rmgr says which part of Postgres wrote the record (Heap = table data), tx is the transaction ID, and HOT_UPDATE is an update where the new row version stays in the same page. rel 1663/5/16384 blk 0 means “block 0 of the file with number 16384”, which is the users table on disk, and old_off/new_off are slot numbers inside that page. The follower patches the same bytes and becomes a byte-for-byte identical copy. Values like now() were computed once, on the leader, and are already inside those bytes.

The price: the follower must run the same major versionMajor version (Postgres)The first number of a Postgres version: 15, 16, 17. Different major versions store data on disk differently; minor updates (16.2 → 16.4) don't change the format. of Postgres (16 with 16, not 16 with 15; minor updates like 16.2 → 16.4 are fine), and it copies everything: every table in every database on the server. You can't copy just one table, and no other kind of program can read the stream. This is what Postgres calls streaming replication, and it's what most Postgres replicas use.

3. Logical: send the changed rows

Logical replicationLogical (row-based) replicationShipping one event per changed row with its new values (users id=42: name='Ana'). Lets you copy chosen tables, replicate between different versions, and feed other systems. sends one event per changed row, with the new values. Here is real Postgres output, captured with the built-in test_decoding plugin. First we create a logical replication slot (a bookmark that collects changes, §11) and run four statements:

SELECT * FROM pg_create_logical_replication_slot('demo', 'test_decoding');
UPDATE users SET name = 'Ana Lee' WHERE id = 42;
INSERT INTO users VALUES (7, 'Dee');
DELETE FROM users WHERE id = 100;
UPDATE users SET name = upper(name);   -- no WHERE: every row

Then we ask the slot what it collected:

$ psql -c "SELECT data FROM pg_logical_slot_get_changes('demo', NULL, NULL)"
 BEGIN 61088
 table public.users: UPDATE: id[integer]:42 name[text]:'Ana Lee'
 COMMIT 61088
 BEGIN 61089
 table public.users: INSERT: id[integer]:7 name[text]:'Dee'
 COMMIT 61089
 BEGIN 61090
 table public.users: DELETE: id[integer]:100
 COMMIT 61090
 BEGIN 61091
 table public.users: UPDATE: id[integer]:42 name[text]:'ANA LEE'
 table public.users: UPDATE: id[integer]:7 name[text]:'DEE'
 COMMIT 61091

Look at the last block: one statement, UPDATE users SET name = upper(name), became two row events, one per row in the table. Events carry final values, so now() and random() are safe. And because the format describes rows rather than disk bytes, you can copy only some tables, replicate between different Postgres versions (a common way to upgrade with little downtime), or feed the changes to a different system entirely (§11).

Try it: pick the middle statement (now()) with statement selected, then switch formats.

Who uses what

Sync vs async: when does the leader say “saved”?

In §03 the leader replied OK as soon as the change was on its own disk. Then it shipped the change. If the leader's machine dies in between, the change exists nowhere else, even though your app already told the user “saved”. That's the risk of asynchronous replicationAsynchronous replicationThe leader confirms a write as soon as it is safe on the leader's own disk and ships it to followers afterwards. Fast, but a leader crash can lose writes that were already confirmed..

The alternative is synchronous replicationSynchronous replicationThe leader confirms a write only after a follower has also stored it. No confirmed write is lost if the leader dies, but every write waits for the network and for that follower.: the leader waits until a follower confirms “I have it” before replying. Now a confirmed write is on two machines. But every write pays for a network round trip, and the leader can only go as fast as the follower it's waiting for.

What the app waits for

The widget below runs those three modes. Do this:

1. In async, press write two or three times, then crash the leader quickly (within about half a second). Writes the app already saw as “OK” get lost.
2. Switch to sync (all) and repeat. Nothing confirmed is lost, but each write waits for the faraway follower.
3. Tick follower 2 unreachable in sync mode and write. The app waits forever.
4. Try semi-sync (any 1) with follower 2 unreachable: writes still succeed, through follower 1.

Real numbers

On the test machine (primary and replica on the same computer, so the network costs almost nothing), one client committing small writes in a loop measured roughly:

Even with zero distance, waiting for a second machine to write to its disk about doubled commit time. Across regions, add the network round trip to every commit: roughly 10–20 ms between nearby regions, 70–150 ms across an ocean (approximate; depends on the route). A request that makes five writes one after another would pay that five times.

A dead sync follower stops all writes

Sync mode waits for the follower forever by default. Here is what happened when the only sync replica was stopped and a Node app (with the pg driver) inserted one row. After 5 s, a second connection ended the wait by calling pg_cancel_backend(pid), Postgres's “cancel that session's current query” function:

still waiting after 5006 ms; cancelling…
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT returned after 5017 ms

Read the DETAIL line carefully. The INSERT succeeded, with only a warning: the row is committed on the leader, it just isn't on any follower. Both ways out of this wait are dangerous:

• If the wait is cancelled (as here), the app gets “success” and believes the write is safely on two machines. It isn't.
• If the app gives up first (a Go context deadline, a driver timeout), the app sees an error, but the row is committed anyway. Retrying may insert it twice.
• statement_timeout doesn't help. On the test setup, SET statement_timeout = '2s' did not end this wait; the INSERT was still hanging after 8 s. The wait for replication isn't counted as part of running the statement.

The real fix is not to get here: never make writes depend on one follower (next).

The usual answer: semi-sync

The common compromise is semi-synchronousSemi-synchronous replicationA middle setting: wait for at least one follower (not all) to confirm before telling the client OK. MySQL calls its version this; Postgres gets the same effect with ANY 1 (…). replication: have two or more followers, and wait for any one of them. You pay the round trip to your nearest follower, a confirmed write is always on at least two machines, and one dead follower doesn't stop anything. In Postgres:

ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (replica_a, replica_b)';
SELECT pg_reload_conf();

Postgres also lets each transaction choose how much to wait for, with the synchronous_commit setting:

The same thing as a picture. Move the “OK” flag with the setting, then pick a crash:

With no synchronous_standby_names configured, on, remote_write and remote_apply all behave like local: plain async. remote_apply is interesting for §06: once it returns, a read from that follower is guaranteed to see the write.

Replication lag and what users see

With async replication, every follower is always at least a little behind. Reading from a follower gives you eventual consistencyEventual consistencyA promise that if writes stop, every copy or derived view of the data (replicas, caches, read models, other services) will end up agreeing — but for a while, different readers may see different (stale) versions.: if writes stopped, all copies would end up the same, but right now they might differ. The gap is replication lagReplication lagHow far a follower is behind the leader: the changes the leader has made that the follower hasn't applied yet. Measured in time (0.2 s) or log bytes. Usually milliseconds; can grow to minutes., measured in time (“0.8 s behind”) or in log bytes (“1,776 bytes behind”).

How big is it?

On a quiet system on a fast network: well under a millisecond. That sounds harmless. So here's a real experiment from the test setup: update a row on the primary, then immediately read it back from a replica on the same machine, 1,000 times:

// primary, replica: two pg.Pool objects, one per server (shown in §07)
let stale = 0;
for (let i = 1; i <= 1000; i++) {
  await primary.query("UPDATE users SET name = $1 WHERE id = 42", [`v${i}`]);
  const { rows } = await replica.query("SELECT name FROM users WHERE id = 42");
  if (rows[0].name !== `v${i}`) stale++; // ← the replica hasn't applied it yet
}

$ node stale.mjs
723 of 1000 reads right after a write were stale
$ node stale.mjs
784 of 1000 reads right after a write were stale

Three out of four reads saw the old value, with no network distance at all. The app's read simply arrived faster than the change. Any “save, then reload the page” flow that reads from a replica will hit this.

And lag isn't always small. It grows when:

• The leader writes faster than a follower can replay. A big batch job or a migration that rewrites a whole table can put followers minutes behind.
• A follower is busy or slow. It might have weaker hardware, or be serving heavy read queries. Replay in Postgres runs in a single process, so one slow step holds up everything after it.
• The network is slow or broken. A follower in another region is at least one trip across the ocean behind, and a network cut makes the lag grow without limit until it heals.
• A follower is paused on purpose. Long queries on a replica can make replay wait (§11).

Three anomalies

When reads go to followers, lag shows up as three kinds of “that can't be right” bugs. Each has a name, because each needs a different fix. Step through them:

Notice that none of these require anything to be broken. No crash, no bug in the database. They are the normal behavior of async replicas, and the database won't hide them from you. Your application has to.

Routing reads and reading your writes, in code

Step 1: two pools

The app keeps two connection poolsConnection poolA set of open database (or HTTP) connections that your program keeps and reuses instead of opening a new one per request. Pools usually have a size limit; when every connection is busy, the next caller waits for one to be returned.: one to the leader for writes, one to the replicas for reads. (A pool is a set of open database connections the app reuses, so it doesn't reconnect for every query.) Every query has to be sent to one or the other on purpose.

import (
	"database/sql"

	_ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver
)

type DB struct {
	primary *sql.DB // all writes
	replica *sql.DB // reads that can tolerate a little lag
}

func Open(primaryURL, replicaURL string) (*DB, error) {
	primary, err := sql.Open("pgx", primaryURL)
	if err != nil {
		return nil, err
	}
	replica, err := sql.Open("pgx", replicaURL)
	if err != nil {
		return nil, err
	}
	return &DB{primary: primary, replica: replica}, nil
}

Step 2: a read-your-writes helper

The idea, from the “with the fix” demo in §06:

1. After a write, ask the leader for its current log position, its LSNLSN (log sequence number)Postgres's name for a position in the write-ahead log, written like 0/3000148. It only ever grows, so “has this replica replayed past LSN X?” is a simple comparison., and keep it in the user's session (for example, a cookie). Call it the user's token.
2. Before a read that should include that user's writes, ask the replica: “have you replayed up to the token yet?” If yes, read there. If not, send this read to the leader.

Postgres gives us both numbers: pg_current_wal_lsn() on the leader, and pg_last_wal_replay_lsn() on a replica.

// Write runs a statement on the primary and returns the log position (LSN)
// the primary had reached afterwards: the user's "token".
func (d *DB) Write(ctx context.Context, q string, args ...any) (string, error) {
	if _, err := d.primary.ExecContext(ctx, q, args...); err != nil {
		return "", err
	}
	var lsn string
	err := d.primary.QueryRowContext(ctx, "SELECT pg_current_wal_lsn()::text").Scan(&lsn)
	return lsn, err
}

// QueryRowAfter reads from the replica only if it has replayed at least up to
// lsn; otherwise it asks the primary. It also says which one it used.
func (d *DB) QueryRowAfter(ctx context.Context, lsn, q string, args ...any) (*sql.Row, string) {
	if lsn != "" {
		var caughtUp sql.NullBool // NULL on a server that isn't (and wasn't) a replica
		err := d.replica.QueryRowContext(ctx,
			"SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn", lsn).Scan(&caughtUp)
		if err != nil || !caughtUp.Bool {
			return d.primary.QueryRowContext(ctx, q, args...), "primary" // ← fall back to the leader
		}
	}
	return d.replica.QueryRowContext(ctx, q, args...), "replica"
}

To see the helper work, the test replica on port 5545 was made to lag on purpose (recovery_min_apply_delay = '2s' holds each change back 2 s). A short script then wrote a new name and read it back with each helper. Real output:

$ go run .
after 0s: Ana G. (from primary)
after 2.5s: Ana G. (from replica)

The naive read straight from the replica returned the old name. The helper noticed the replica was behind the token and asked the primary. Once the replica caught up, the same token sent reads back to the replica.

Other fixes, simplest first

• Read from the leader for a while after any write. Store lastWriteAt in the session; for, say, 10 seconds afterwards, send that user's reads to the leader. No LSNs needed. Pick the window bigger than your normal lag, and accept that a lag spike beyond it still shows stale data.
• Read from the leader for anything the user can edit. Their own profile and settings come from the leader; everybody else's come from replicas. Simple, and covers the common “I saved it and it's gone” case.
• Wait a little instead of falling back. If the replica is behind the token, re-check every few milliseconds for up to, say, 50 ms before giving up and asking the leader. That protects the leader during a burst of writes.
• Sticky replicasSticky routingAlways sending the same user (or session) to the same server, usually by hashing the user ID. Here it keeps one user on one replica. for monotonic reads (§06): pick the replica with hash(userId) % replicas.length, so one user always reads the same replica.
• Make the write wait instead. SET synchronous_commit = remote_apply for one transaction (§05) makes the commit return only after the sync follower has applied it. Reads from that follower are then fresh, at the cost of a slower write.

Measuring lag yourself

On a replica, three questions answer “how far behind am I?”:

SELECT pg_is_in_recovery()          AS is_replica,     -- true on a follower
       pg_last_wal_receive_lsn()    AS received_up_to, -- arrived over the network
       pg_last_wal_replay_lsn()     AS replayed_up_to, -- applied, visible to queries
       now() - pg_last_xact_replay_timestamp() AS behind_by;

behind_by is the time since the last transaction this replica replayed was committed on the leader. It's the easiest number to read, with one trap: if the leader is idle, nothing new arrives, so behind_by grows even though the replica is fully caught up. On the leader, pg_stat_replication shows every follower's position and lag at once; §11 shows real output and what to alert on.

Failover: when the leader dies

When a follower dies, not much happens: send its reads elsewhere, and when it comes back it asks for the log from where it left off (§03). When the leader dies, nobody can write. failoverFailoverSwitching to a standby (a replica) when the primary server fails, so the system keeps working — automatically or by an operator. means turning a follower into the new leader. You can do it by hand (an engineer gets paged and runs a command) or let a tool do it automatically.

The four steps

1. Notice. A monitor checks the leader with heartbeatsHeartbeat (application)A small “are you still there?” message your own protocol sends every few seconds. If a few heartbeats go unanswered, the program treats the other side as gone., small “are you alive?” messages every second or so. A dead machine and a slow or unreachable one look the same from outside, so the only tool is a timeout: “no answer for 10 s means dead”. Load balancers face exactly the same choice; see health checks.
2. Choose. Pick the follower that is furthest ahead in the log, so the fewest writes are lost.
3. Promote. Tell it to stop following and start accepting writes: promotionPromotionTurning a follower into the leader: it stops replaying someone else's log and starts accepting writes itself.. In Postgres that's one function call, SELECT pg_promote();.
4. Re-point. Other followers must follow the new leader, and apps must send writes to it.

Lost writes

In the first story, write #102 was confirmed to the app but existed only on the old leader. With async replication, failover always risks this: you lose whatever the old leader hadn't shipped yet, usually less than a second of writes. Picking the most up-to-date follower keeps the loss small; only sync or semi-sync replication (§05) makes it zero.

Lost writes can do damage beyond the missing rows. A famous GitHub incident (2012) involved a promoted MySQL follower that was behind: auto-increment IDs that the old leader had already handed out were handed out again by the new one. A cache in another system (Redis) still held the old meaning of those IDs, so some users saw other users' private data.

Split brain, for real

In the second story the old leader wasn't dead, only cut off, and kept accepting writes. Here's that situation reproduced with the test setup: promote the replica while the primary is still running, then write a different value to each.

$ psql -p 5544 -c "SELECT pg_promote()"     # make the replica a leader
 pg_promote
------------
 t
$ psql -p 5543 -c "UPDATE users SET name='written on OLD primary' WHERE id=42"
UPDATE 1
$ psql -p 5544 -c "UPDATE users SET name='written on NEW primary' WHERE id=42"
UPDATE 1

Both succeeded. Two leaders, two different truths. That is split brainSplit brainTwo nodes both believe they are the leader and both accept writes, so the data forks into two histories. Usually caused by a failover while the old leader was still alive.. When the old primary was then told to become a follower of the new one, Postgres refused to stream from it:

LOG:  new timeline 2 forked off current database system timeline 1 before current recovery point 0/EA4D9B0

Each promotion starts a new timelineTimeline (Postgres)A numbered branch of history. Each promotion starts a new timeline, so Postgres can tell which WAL belongs to the old leader and which to the new one., a numbered branch of history. The old primary had written past the point where the branch split, so its log and the new leader's log disagree, and Postgres won't guess which one is right.

The tool pg_rewind can throw away the old primary's extra writes so it can rejoin; those writes are then gone unless someone copies them back by hand.

Fencing: one leader at a time

The fix is fencingFencingMaking sure an old leader can no longer write before (or while) a new one takes over: powering it off, cutting its access, or having storage reject its requests.: make sure the old leader cannot write before or while a new one takes over. Three common ways:

• Power it off. STONITHSTONITH“Shoot the other node in the head”: fencing by forcibly powering off the old leader through its management interface or power switch, so it can't cause split brain. (“shoot the other node in the head”): the monitor cuts the old leader's power or shuts it down through its management interface or a cloud API, and only then promotes.
• Let it step down. The leader keeps a short lease (“I'm leader for the next 30 s”) in a coordination serviceCoordination serviceA small, highly available key-value store (etcd, Consul, ZooKeeper) that several machines use to agree on facts like “who is the leader right now”. It uses consensus internally, so its answers can't contradict each other. (a small, highly available key-value store that several machines agree on) and must renew it. If it can't reach the service, it demotes itself before the lease expires, and the monitor waits until the lease has expired before promoting anyone else. PatroniPatroniA popular open-source tool that runs Postgres clusters with automatic failover. It stores “who is the leader” in a consensus store (etcd, Consul or ZooKeeper). works like this, with etcd, Consul or ZooKeeper as the coordination service.
• Reject old leaders. Each new leader gets a higher number, called a term or epoch. (Postgres's timeline number plays a similar role inside Postgres, but other systems don't check it.) Storage or downstream services remember the highest number they've seen and reject writes carrying an older one: a fencing tokenFencing tokenA number that increases every time a new leader is chosen or a lock/lease is granted. Storage remembers the highest number it has seen and rejects writes carrying an older one, so a paused old holder can't overwrite the new holder's work..

Deciding “who is the leader now” safely, so that two nodes can never both believe it, is the problem that consensus algorithms solve. The Consensus & Raft topic shows how; tools like Patroni lean on such a system instead of trusting their own timeouts.

Re-pointing the apps

The new leader is at a different address. Common ways apps find it:

The last option is built into Postgres's own client library: a connection string like host=db1,db2,db3 target_session_attrs=read-write makes the client try each host and keep the one that accepts writes. Recent pgx versions (Go) support it too; check what your driver supports.

Whichever you choose, apps must expect connection errors during failover and retry, with backoff, for the 10–60 s it typically takes. Managed databases (for example AWS RDS Multi-AZ) do all four steps for you and typically fail over in about a minute; your app still needs the retry.

Multi-leader: writes in several places

With one leader, a user in Sydney writing to a leader in Virginia pays a round trip of roughly 200 ms on every write, and if the link between regions breaks, Sydney can't write at all. Multi-leaderMulti-leader replicationSeveral nodes (often one per region) accept writes and replicate to each other. Writes are fast everywhere, but two leaders can change the same data at once: a conflict. replication puts a leader in each region. Each one accepts local writes and replicates them asynchronously to the other leaders, which apply them like any other change.

Where you meet it

• Multi-region apps that need fast writes everywhere, or must keep accepting writes when one region is cut off.
• Offline-first apps. Every phone with a calendar app is effectively a leader: you add events on a plane, and they sync when you land.
• Collaborative editing. In a shared document, every open browser tab accepts edits immediately and syncs with the others.

Most relational databases don't do this out of the box (for Postgres, extensions such as EDB's BDR / PGD add it); it's more common in systems designed for it, like CouchDB and sync engines in mobile apps. Leaderless systems such as Cassandra (§10) face the same conflicts.

Conflicts

With one leader, two edits of the same row happen one after the other, in log order. With two leaders, both can accept an edit before hearing about the other one. Neither is “later”; they happened concurrently. That's a write conflictWrite conflictTwo leaders accepted different changes to the same data concurrently, neither knowing about the other. Something must decide the final value., and it shows up long after both users were told “saved”, so you can't just return an error.

The most common rule is last write winsLast write wins (LWW)A conflict rule: attach a timestamp to every write and keep the one with the biggest timestamp. Simple, but it silently discards the other write, and clocks on different machines disagree.: stamp every write with the current time and keep the one with the bigger timestamp. It always converges, it's easy, and it's the default in several databases. It also silently throws away the other write. And “bigger timestamp” isn't the same as “later”, because different machines' clocks disagree: clock skewClock skewThe difference between two machines' clocks. Even with time sync (NTP) it is often milliseconds, and can be much more after a fault, so timestamps from different machines can't reliably order events. of a few milliseconds is normal even with time sync, and much more happens after a fault. Drag the US clock slider:

Better ways to resolve

• Avoid conflicts. Give each record a home leader: all writes to user 42's data go to user 42's home region. Conflicts only happen if the home changes. This is the most common practical answer.
• Keep both. Store every concurrent version (often called siblings) and let the app or the user merge them, like a git merge conflict. To tell “concurrent” from “one after the other” without trusting clocks, systems attach a version vectorVersion vectorA small set of counters, one per replica, attached to a value. Comparing two vectors tells you whether one version came after the other or whether they were written concurrently.: one counter per replica.
• Use data types that merge. A CRDTCRDTConflict-free replicated data type: a data structure (counter, set, text…) designed so concurrent changes can always be merged automatically, in any order, to the same result. (conflict-free replicated data type) is designed so concurrent changes always merge to the same result in any order: counters that add up each replica's increments, sets that remember both additions and removals, even text for collaborative editors (libraries such as Automerge and Yjs).

Leaderless: quorums (Dynamo style)

Leaderless replicationLeaderless replicationAny replica accepts reads and writes; the client (or a coordinator) sends each request to several replicas and uses quorums. The Dynamo style: Cassandra, Riak, ScyllaDB. was popularized by Amazon's Dynamo paper (2007) and is used by CassandraCassandraA distributed database built for huge write volumes spread across many machines. Data is grouped by a partition key and sorted within each partition, which suits “latest messages in this chat” queries. Internally it stores data in LSM trees., ScyllaDB and Riak. There's no leader to fail over: any replica accepts any request. With no single log to put writes in order, the client (or whichever node it talks to, acting as coordinatorCoordinator (leaderless)Whichever node receives a client's request in a leaderless database. It forwards the request to the replicas that hold the data, counts their answers, and replies to the client.) sends each request to several replicas and counts answers.

N, W, R

Three numbers, the N, W, RN, W, RIn leaderless systems: N copies of each value, a write must be confirmed by W of them, and a read asks R of them. If R + W > N, every read overlaps the latest write. settings, describe the setup:

• N: how many copies of each value exist (often 3).
• W: how many replicas must confirm a write before it counts as successful.
• R: how many replicas a read asks; it keeps the newest version it gets back.

How does it know which is newest? Each write carries a version. In Cassandra that's a timestamp set by the client or coordinator, so “newest” inherits all the clock problems from §09; Riak uses version vectors instead.

Worked example: N = 3, W = 2, R = 2. A write reaches replicas 1 and 2; replica 3 is slow and misses it. A read asks two replicas. There are only three, so any two must include replica 1 or replica 2: at least one answer has the new value. 2 + 2 > 3, so the sets overlap. With R = 1 instead, the read might ask only replica 3 and get the old value. That's the R + W > N rule, and it's a quorumQuorumA majority (or another agreed minimum) of replicas that must answer before a read or write counts — e.g. 2 of 3. Any two majorities overlap, so a read quorum always includes at least one replica that saw the latest write quorum. in action.

The same numbers decide availability. With N = 3, W = 2, R = 2, one replica can be down and both reads and writes still work. Choosing W = 3 makes reads cheap (R = 1 is enough to overlap) but means one dead replica blocks writes. Cassandra lets you pick per query with consistency levels such as ONE, QUORUM (a majority of N) and LOCAL_QUORUM (a majority within the local data center).

How replicas catch up

A replica that missed a write is stale until someone fixes it. Three mechanisms do that:

• Read repairRead repairWhen a read in a leaderless system notices a replica returned an old version, it writes the newest version back to that replica.: when a read gets different versions from different replicas, the coordinator writes the newest back to the stale ones. Tick “read repair” in the widget and read again. Rarely read data is rarely repaired this way.
• Hinted handoffHinted handoffIf a replica is down during a write, another node keeps the write with a note (“hint”) and hands it over when the replica comes back.: if a replica is down during a write, another node stores the write with a “hint” and hands it over when the replica comes back.
• Anti-entropyAnti-entropyA background process that compares replicas (often using hash trees) and copies over whatever is missing, so even rarely read data eventually converges.: a background job compares replicas, using hash trees so it doesn't have to compare every row, and copies whatever is missing. (The widget's “background sync” button.)

Quorums are not magic

R + W > N sounds like it guarantees fresh reads. In practice there are gaps:

• Concurrent writes to the same key still need conflict resolution, usually last write wins with its clock problems (§09).
• A write that fails (fewer than W confirmations) isn't rolled back on the replicas that did take it. Later reads may or may not see it.
• A write in progress may be visible to one read and not to the next, so even quorum reads can appear to go backwards.
• A sloppy quorumSloppy quorumCounting writes accepted by stand-in nodes (via hinted handoff) toward W. It keeps writes available during outages, but then R + W > N no longer guarantees fresh reads. lets stand-in nodes accept writes for down replicas (via hinted handoff) and count toward W. Good for availability, but the read set and write set may no longer overlap.

So leaderless systems with quorums are best thought of as eventually consistent with good odds, not as behaving like one copy. That stronger guarantee is called linearizabilityLinearizabilityThe strongest consistency guarantee: the system behaves as if there were one copy and every operation happened at a single instant. Once any read sees a new value, no later read sees the old one., and getting it across machines needs more machinery; the CAP & PACELC topic and Consensus & Raft pick up from here.

Running replicas in production

Replicas are not backups

A replica copies every change, including the bad ones. Here's a DELETE with a forgotten WHERE, on the test setup:

$ psql -p 5543 -c "DELETE FROM users"          # oops: no WHERE clause
DELETE 2
$ psql -p 5544 -c "SELECT count(*) FROM users"  # the replica, a moment later
 count
-------
     0

Within milliseconds the mistake was on every replica. The same goes for a buggy migration, an app bug that overwrites data, or an attacker with write access. You still need real backups:

• Point-in-time recovery. Take regular full backups and archive the WAL continuously (tools: pgBackRest, WAL-G, Barman, or your cloud's managed backups). Then point-in-time recoveryPoint-in-time recovery (PITR)Restoring a database to any chosen moment by taking an old full backup and replaying archived WAL up to just before, say, the bad DELETE. can restore to “one second before the DELETE”.
• A delayed replica. A delayed replicaDelayed replicaA replica deliberately kept, say, 1 hour behind (recovery_min_apply_delay in Postgres), so a mistaken DELETE can be caught and copied back before it arrives there. applies changes, say, one hour late (recovery_min_apply_delay = '1h'). If someone notices the mistake within the hour, you pause that replica and copy the lost rows back from it.
• Test restores. A backup you've never restored is a hope, not a backup.

Watch the lag

On the leader, pg_stat_replication has one row per connected follower. Real output while a benchmark was writing; replica r2 was set to apply changes 2 s late:

$ psql -p 5543 -c "SELECT application_name AS replica, state, sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behind,
    write_lag, flush_lag, replay_lag FROM pg_stat_replication"
 replica |   state   | sync_state |   behind   |    write_lag    |    flush_lag    |   replay_lag
---------+-----------+------------+------------+-----------------+-----------------+-----------------
 r1      | streaming | async      | 2576 bytes | 00:00:00.000201 | 00:00:00.000589 | 00:00:00.001147
 r2      | streaming | async      | 2530 kB    | 00:00:00.00014  | 00:00:00.000499 | 00:00:01.99979

The three lag columns follow a change along its path: received and written by the follower (write_lag), safely on its disk (flush_lag), and applied so queries can see it (replay_lag). For r2, the first two are tiny and replay_lag is 2 s: the network is fine, the replica is just holding changes back. When replay_lag is high but write_lag is low, look at the replica (busy disk, long queries); when all three are high, look at the network. These are the same stages as the commit pipeline in §05:

What to alert on (starting points to tune, not rules):

• Replay lag above what your app tolerates, e.g. more than 30 s for 5 minutes. Pair it with automatic routing: take a replica out of the read pool while it's more than a few seconds behind, the way a load balancer drops a failing backend.
• A follower missing from pg_stat_replication, or not in state streaming.
• WAL kept for replication slots (next).

Replication slots can fill the disk

A replication slotReplication slotA bookmark on the Postgres primary that remembers how far a replica (or CDC consumer) has read, so WAL isn't deleted before it's consumed. A forgotten slot keeps WAL forever and can fill the disk. is a bookmark on the leader that says “this follower has read up to here; keep all WAL after it”. It means a follower that disconnects for a while can resume instead of needing a new base backup. But the leader keeps that WAL forever if the follower never comes back. Real output after creating a slot for a replica that was then switched off, and running a data load:

$ psql -p 5543 -c "SELECT slot_name, active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_kept
    FROM pg_replication_slots"
       slot_name       | active | wal_kept
-----------------------+--------+----------
 r1_slot               | t      | 6063 kB
 r2_slot               | t      | 6063 kB
 old_analytics_replica | f      | 262 MB

262 MB after one small load; a busy database writes that every few minutes. A forgotten slot (an old analytics replica, a CDC tool someone uninstalled) is a classic cause of a leader's disk filling up and the leader going down. Alert on inactive slots and on wal_kept, and set max_slot_wal_keep_size so Postgres gives up on a slot instead of the disk.

Long queries on replicas

Replicas are a great place for heavy reports and analytics, because those queries can't slow down the leader. But they collide with cleanup. When a row is updated or deleted, Postgres keeps the old version around for a while, in case a running query still needs it; VACUUMVACUUMPostgreSQL's clean-up job: it finds row versions that no running or future transaction can see any more (“dead” versions) and frees their space for reuse. Autovacuum runs it automatically in the background. later removes old versions nobody needs. The leader's VACUUM doesn't know about queries on the replica, and its cleanup arrives through the WAL like any other change. So the replica has a choice: pause replay (lag grows) or cancel the query. By default it waits up to max_standby_streaming_delay (30 s), then cancels. With that set to 3 s, a long query on the test replica got:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
Time: 5086.106 ms (00:05.086)

Turning on hot_standby_feedbackhot_standby_feedbackA Postgres replica setting: the replica tells the primary which old row versions its queries still need, so VACUUM on the primary doesn't remove them. Prevents query cancellations, at the cost of table bloat. makes the replica tell the leader which old versions its queries still need. The same query then finished, and VACUUM on the leader reported:

tuples: 0 removed, 50000 remain, 16667 are dead but not yet removable

In that output, “tuples” are row versions and “dead” ones are old versions left behind by the DELETE: dead rowsDead row (dead tuple) / bloatIn Postgres, an UPDATE or DELETE leaves the old row version behind (a “dead tuple”) until VACUUM removes it. Too many dead rows is called bloat: wasted space and slower scans.. No more cancellations, but the leader now keeps dead rows for as long as the longest replica query runs: a six-hour report means six hours of table bloat on the leader. The usual setup: a dedicated analytics replica with feedback on (or a long delay, accepting lag), never used for failover or for lag-sensitive app reads.

Topology and cost

• Cascading replicasCascading replicationA replica that feeds other replicas, so the primary only sends its log once per region instead of once per replica.: a replica can feed other replicas. With five replicas in another region, the leader sends the WAL across the ocean once, to one replica there, which feeds the other four. Each extra hop adds lag.
• Cross-region traffic costs money. Cloud providers bill data sent between regions, typically a few cents per GB (check your provider's current pricing). A leader writing 50 GB of WAL a day to three remote replicas sends 150 GB a day. Cascading and WAL compression help.
• Failover across regions means the promoted replica is far from your app servers. Plan which replica may be promoted (synchronous_standby_names and failover-tool priorities), not just which exist.

Change data capture: replication for other systems

Logical replication (§04) produces a stream of row changes. Nothing says the receiver has to be a database. Change data capture (CDC) tools like DebeziumDebeziumAn open-source change data capture tool: it reads a database's change stream (Postgres logical decoding, MySQL binlog) and publishes each row change as an event, usually to Kafka. read that stream (Postgres logical decoding, the MySQL binlog) and publish each change as an event, usually to KafkaKafkaApache Kafka: a durable, ordered log of events, split into partitions, that many programs can publish to and read from at their own pace.. From there, other systems keep themselves in sync: a search index, a cache, a data warehouseData warehouseA separate database built for big analytical queries over lots of historical data (BigQuery, Snowflake, Redshift), usually fed copies of data from the app's databases., other services.

That beats writing to the database and to Kafka from your app, where one write can succeed while the other fails, leaving them inconsistent. With CDC, the database's log is the single source of truth. It pairs well with the outbox pattern: the app writes events to an outbox table in the same transaction, and CDC publishes them. Remember that a CDC tool holds a replication slot, with everything that implies.

Pitfalls

Check yourself

Predict the answer before you click. Q1 and Q8 come from §06–§07, Q2 from §05 and §08, Q3 from §05, Q4 from §10, Q5 from §04, Q6 from §11, and Q7 from §09.

await primary.query("UPDATE users SET name = $1 WHERE id = $2", ["Ana B.", 42]);
const { rows } = await replica.query("SELECT name FROM users WHERE id = $1", [42]);
res.render("profile", { name: rows[0].name });
// async replication, replica in the same data center

replication: async
t=0 ms    leader writes order #9 to its disk, replies OK to the app
t=50 ms   leader's machine loses power
          (order #9 hadn't been sent to any follower yet)
t=10 s    follower promoted

ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (r1)';
-- r1 is the only follower listed. Now r1 is restarted
-- for an upgrade, which takes 2 minutes.

N = 5 copies, W = 3, R = 2

-- statement-based replication ships this text to the follower:
UPDATE users SET last_seen = now() WHERE id = 42;

DELETE FROM users;   -- run on the primary by mistake
-- 3 async replicas, a few milliseconds behind

EU leader (clock correct):   title = "Q3 plan"     at 10:00:00.200
US leader (clock 300 ms slow): title = "Q3 roadmap" at real time 10:00:00.350
Conflict rule: last write wins (biggest timestamp)

Reads are spread round-robin across replicas A and B.
B is ~3 s behind. A user refreshes a page repeatedly
and sees a new comment appear, vanish, and appear again.

Cheat sheet

-- On the leader: who is following, and how far behind?
SELECT application_name, state, sync_state, replay_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;

-- On a replica: am I a replica, and how far have I got?
SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS behind_by;