Database Replication: Primary-Backup, Multi-Primary and Quorums

One server is one machine, so a single power cut, dead disk or bad update can stop every payment or lose the newest entries. Database replication fixes the first two by keeping copies on other machines. Only a backup fixes the third.

Picture a common setup. A payments ledger for small shops in Gurugram records what each shop is owed whenever a customer pays by UPI, India's instant bank-transfer system, or by card. Every evening it settles those amounts into each shop's bank account. The setup is made up, but the problem is real.

A ledger is a list of money entries: who owes whom, how much, and when. It must never drop an entry or count one twice. About 22,000 shops use this one, and on a busy evening it writes 60 entries a second.

For two years the team ran it on one PostgreSQL server, plus a second copy that only fed reports. Then, on a Saturday evening in Diwali week, the main server's power supply failed, and its disk could not be trusted afterwards. The team switched the app over to the copy in six minutes.

On Monday, the team matched the ledger against the payment provider's settlement file. Thirty-seven entries, worth Rs 18,460, were missing. The main server had saved them and told the shops so, but they never reached the copy before the power went.

Each entry had to be rebuilt by hand. That left two questions this guide answers. How many copies should the ledger keep? And when is it honest to tell a shop that its payment is saved?

Database replication means keeping the same data on more than one database server and copying every change from one to the others. Each server that holds a copy is called a replica.

Teams replicate for three main reasons:

• Survive failures. If one machine dies, another already has the data and can take over.
• Spread reads. Reports and dashboards can read from a replica, which leaves the main server free for payments.
• Stay close to users. A replica in another city can answer nearby users faster.

Replication is not a backup. A backup is a copy frozen at one moment, kept so you can go back in time. A replica copies every change, including mistakes. If someone deletes a table by accident, every replica deletes it too.

Copying data is the easy part. The hard part is deciding who may accept writes, and how long a write waits for the copies. There are three main answers:

• Primary-backup. One server takes every write and sends its changes to the others.
• Multi-primary. Several servers take writes and send their changes to each other.
• Quorum, also called leaderless. Any replica can take a write, and the write counts once enough replicas confirm it.

In primary-backup replication, one server called the primary accepts every write, and one or more backups copy its changes in the same order. The backups are also called standbys, replicas or followers.

Many databases, PostgreSQL among them, copy changes through a log. PostgreSQL first writes each change to its write-ahead log (WAL), an ordered list of every change, and only then to the data files. The primary streams that list to each standby, which replays it.

Every standby replays the same changes in the same order, so it ends up with the same data. Writes have only one home, so two servers never disagree about which payment came first.

Here is the ledger's setup before the outage. Follow the WAL arrow from the primary to the standby, and notice that payments and reports go to different servers.

A standby that also answers read-only queries is called a hot standby. The ledger's settlement reports run on it.

Cricket gives a handy picture. The Laws of Cricket ask for two scorers, and they must check often that their records agree. Imagine the second scorer cannot see the pitch and copies every entry from the first scorer's book.

• The first scorer's book is the primary. Every ball goes there first.
• The second scorer's book is the standby. It holds the same balls, in the same order.
• Copying ball by ball is the WAL stream.
• How many balls the second book is behind is the replication lag.

Where the picture breaks: real scorers both watch the match, so each could rebuild the score alone. A standby sees nothing on its own. If the primary's log never reaches it, that change is simply not there.

The copy is always a little behind. The gap between a write landing on the primary and showing up on a standby is called replication lag. The PostgreSQL documentation says it is typically under one second, if the standby can keep up.

In asynchronous replication, the primary confirms a write without waiting for any standby. In synchronous replication, the primary waits until at least one standby confirms it has the write too.

A transaction is a group of changes that succeed or fail together. A commit is the moment a database promises that a transaction is saved. With asynchronous replication, that promise rests on one machine.

PostgreSQL streaming replication is asynchronous by default. Its documentation says that if the primary crashes, some committed transactions may never reach the standby. The amount lost grows with the lag at the moment of failover. That is what hit the ledger.

In the scorer picture, asynchronous scoring puts the new total on the board as soon as the first scorer writes it. If rain soaks the first book before the copy catches up, those runs are gone. Synchronous scoring waits for the second book first.

Here is one synchronous write, step by step. To flush means to force data onto the disk. The detail line on each box says who talks to whom.

PostgreSQL turns this on with two settings. The setting synchronous_standby_names lists the standbys that must confirm, and it is empty by default. The setting synchronous_commit chooses how far a standby must get before the commit returns:

• on, the default: the standby has flushed the record to disk. A confirmed entry is lost only if the primary and every synchronous standby lose their storage.
• remote_write: the standby has handed the record to its operating system, but maybe not to disk. A standby operating-system crash can still lose the standby's copy.
• remote_apply: the standby has also replayed the record, so queries on the standby can see it. The documentation warns of much larger commit delays.
• local: the commit waits only for the primary's own disk, not for any standby.
• off: the commit does not even wait for the primary's own disk, so a primary crash can lose the newest confirmed writes.

The price is time. The documentation says the minimum wait is the round trip between primary and standby. Light in fibre covers about 200 km per millisecond, so 1,000 km of cable adds at least 10 ms there and back.

The commit waits, and keeps waiting. PostgreSQL's documentation says such commits may never complete if a synchronous standby crashes. On 16.15, our insert sat in the SyncRep wait until the standby came back.

So teams run two standbys and wait for any one of them. PostgreSQL supports this directly: the setting ANY 1 (s1, s2), where s1 and s2 are the standbys' names, lets a commit continue once either one replies. In our test, with s1 down, a commit still returned in under 60 ms, counting the start-up of psql, PostgreSQL's command-line client.

Reading from a replica without surprises

Lag also bites on reads. A shop owner records a payment, then opens the balance screen. If that screen reads a standby two seconds behind, the payment seems to vanish. The team routes reads with a small rule:

MAX_LAG_SECONDS = 1.0

def pick_server_for_read(needs_latest):
    if needs_latest:
        return "primary"          # balances, payouts, refunds
    lag = replica_lag_seconds("standby-1")
    if lag is None or lag > MAX_LAG_SECONDS:
        return "primary"          # standby is down or too far behind
    return "standby-1"            # reports and dashboards

The helper replica_lag_seconds is your own function: it runs the lag query from the engineer section on the standby. The needs_latest flag is set by the caller, so any read that moves money goes to the primary.

The lag is None check treats an unreachable standby like a slow one, which is the safe default.

When the primary fails, a standby must be promoted to become the new primary, and every app must start writing to it. This switch is called failover.

Failover sounds simple, but one mistake can be worse than the outage. That mistake is split brain: two servers both believe they are the primary, and both accept writes. Their ledgers drift apart, and someone must merge them by hand.

PostgreSQL will not stop this by itself. Its documentation says it does not provide the software to detect a failed primary. We promoted a standby while the old primary still ran: both took an insert, and both gave it entry id 1.

The guard against split brain is fencing. Fencing means cutting the old primary off, by power, network or permissions, so it cannot accept writes even if it wakes up. The PostgreSQL docs call one form STONITH, Shoot The Other Node In The Head.

The team's failover runbook runs in this order. The timings are made up:

1. 1

   Detect

   A health checker pings the primary every 5 seconds. Only after 30 seconds of silence does it start a failover, so a short network blip is ignored.

2. 2

   Fence the old primary

   Block its writes before anything else. If it was only cut off and comes back, it must not take a single payment.

3. 3

   Pick the right standby

   Choose the synchronous standby that holds the most WAL. With ANY 1, only that one surely holds every confirmed entry.

4. 4

   Promote it

   Run pg_ctl promote or call pg_promote() on the chosen standby, so it stops replaying and starts accepting writes.

5. 5

   Point the app at it

   Update the address the Ledger API writes to, so payments flow to the new primary.

6. 6

   Rebuild the old primary

   Check what it holds, then turn it into a fresh standby, for example with pg_rewind, a PostgreSQL tool that rolls its data back to where the copies split. Never let it rejoin as a primary.

The diagram shows the decision at the heart of that runbook. Look at the order on the Yes path: fence first, then promote.

Real failovers go wrong at large companies too. On 21 October 2018, GitHub lost connectivity between its US East Coast network hub and main data centre for 43 seconds. Its failover tooling began moving database writes to the West Coast.

The East Coast servers held a brief period of writes that had not reached the West Coast. Soon each side held writes the other lacked, so GitHub could not simply move back. It chose data integrity over a fast recovery, and service was degraded for 24 hours and 11 minutes.

Multi-primary replication lets two or more servers accept writes at the same time, each sending its changes to the others. It helps when users in far-apart places all need fast local writes.

You will also see it called multi-leader or active-active. The cost is conflicts. Two primaries can change the same row at the same moment, each without knowing about the other. Something must then decide which change survives.

Say the ledger ran primaries in Gurugram and Mumbai. A shop's balance is Rs 5,000. Gurugram starts a Rs 3,000 payout while Mumbai starts a Rs 4,000 refund. Each primary checks its own copy, sees Rs 5,000, and approves.

Some systems settle this with last write wins: keep whichever change has the later timestamp, and throw the other away. For a ledger, that silently deletes money. Other systems refuse one of the two changes instead.

MySQL Group Replication works the second way in multi-primary mode. Any server in the group may run transactions at any time. When two concurrent transactions on different servers change the same row, the one ordered first commits everywhere.

The other is rolled back, which means undone, on the server where it started. The MySQL manual calls this a distributed first commit wins rule. The app must be ready to retry a rolled-back payment.

This only catches two changes to the same row. If each primary reads the balance and then inserts a new entry row, both commit, and the balance ends at minus Rs 2,000.

In quorum replication, each write goes to N replicas and succeeds once W of them confirm, and each read asks R replicas. If R plus W is greater than N, every read reaches at least one replica that took the write.

A quorum is the minimum number of members that must agree before a decision counts. Amazon's 2007 Dynamo paper made this style famous. It says R + W > N gives a quorum-like system, and that several Dynamo instances used N = 3, R = 2 and W = 2.

Why does the overlap work? Take three replicas, A, B and C. A write lands on A and B, while C is slow. A later read asks any two. Every pair of two includes A or B, so the read always meets the new entry.

The diagram shows one quorum write with N = 3 and W = 2. Look at replica C: it timed out, yet the write still succeeds.

You can check the overlap rule yourself. This short script tries every possible write set and read set on three replicas:

from itertools import combinations

N = 3
for W, R in [(2, 2), (3, 1), (2, 1)]:
    overlap = all(set(w) & set(r)
                  for w in combinations(range(N), W)
                  for r in combinations(range(N), R))
    print(f"N={N} W={W} R={R}  R+W>N: {R + W > N}  every read meets the write: {overlap}")

It prints three lines. W=2 R=2 and W=3 R=1 both show True for both checks. W=2 R=1 shows False twice: with R plus W equal to N, some reads miss the write.

Here is the idea as ledger code. The helpers send_write and ask_replica are stand-ins for your network calls. The first returns True when a replica confirms. The second returns the stored record, None if the replica has no copy, or raises TimeoutError.

REPLICAS = ["ledger-a", "ledger-b", "ledger-c"]   # N = 3
W, R = 2, 2                                      # R + W = 4, more than N

def write_entry(entry_id, entry, version):
    acks = 0
    for name in REPLICAS:
        if send_write(name, entry_id, entry, version):
            acks += 1
    if acks < W:
        raise RuntimeError("Not saved: fewer than W replicas confirmed")
    return acks

def read_entry(entry_id):
    replies = []
    for name in REPLICAS:
        try:
            replies.append(ask_replica(name, entry_id))   # a record, or None if no copy
        except TimeoutError:
            pass                                          # this replica did not answer
    if len(replies) < R:
        raise RuntimeError("Not enough replicas answered")
    copies = [r for r in replies if r is not None]
    if not copies:
        return None
    return max(copies, key=lambda r: r.version)   # newest version wins

The acks < W line is the whole promise: never report saved unless W replicas hold the entry. A replica that answers None still counts as an answer. Real systems contact all replicas in parallel.

There is a catch in the error path. We ran this code against three fake replicas where only A confirmed a write. write_entry raised an error, yet a later read that reached A returned the entry anyway.

Dynamo also bends the rule during failures. Its sloppy quorum writes to the first N healthy nodes, not always the usual ones. A stand-in node keeps the data with a hint saying who owns it, and hands it back later. Reads and writes may then miss each other.

Conflicts can still happen. Dynamo tags each version with a vector clock, a small record of which nodes changed it. When versions conflict, it returns all of them for the app to merge. That suits a shopping cart, but a balance check needs the answer before it approves.

A payments ledger should usually run one primary with synchronous standbys, because money entries need one order of events and no lost confirmations. The table compares all four setups on the ledger.

The team's final design has four parts. A primary in Gurugram takes every write. Two synchronous standbys sit in the same city, on separate racks and power, with ANY 1 so one can fail without stopping payments.

A fourth, asynchronous standby sits in Mumbai. If all of Gurugram goes dark, the team can still promote it, accepting that the newest entries may need checking against the provider's file.

The team adds the rules that keep it honest:

• Every money entry commits synchronously. App-usage events do not wait.
• Balance, payout and refund reads go to the primary. Reports read from a standby.
• Failover always fences the old primary first, and only promotes the synchronous standby holding the most WAL.
• Nightly backups and a daily match against the provider's settlement file still run.

Spreading the data itself across many machines is a separate idea, called sharding. Each shard then gets replicated in one of the ways above. Choosing between staying correct and staying available during a network split is its own topic, taught as the CAP theorem. PACELC extends it with the trade-off between speed and consistency when there is no split.

It promises durability on a standby, meaning the write would survive a crash there. It does not promise visibility there or a clean failure. We ran each case below on PostgreSQL 16.15, one primary and two standbys on one machine, in October 2026. The settings read the same in the version 18 docs, but we did not re-run these tests on 17 or 18.

A flushed commit is not yet visible on the standby

With the default on, the standby has the commit on disk but may not have replayed it. To make the gap visible, we told one standby to replay commits 2 seconds late with recovery_min_apply_delay.

With on, the commit took about 20 ms, and a query on the standby straight after did not find the row. With remote_apply, the commit took just over 2 seconds, and the row was there at once.

So a read-your-own-write on a standby needs remote_apply. The docs' own table marks only remote_apply as giving standby query consistency. With ANY 1, only the standby that replied is sure to have the row: in our test, the other one did not yet. The safe choice is a read from the primary.

A timeout does not end the wait, and a cancel still commits

The setting statement_timeout cancels any statement that runs longer than a limit. With the only synchronous standby down, we set it to 3 seconds, and it cancelled a 5-second pg_sleep as expected.

The insert after it still waited over 10 seconds. Its wait event in pg_stat_activity, PostgreSQL's view of what each session is doing, was SyncRep, which marks a commit waiting for a standby.

We then cancelled the waiting insert from another session with pg_cancel_backend(). The client printed INSERT 0 1, which looks like success, plus this warning:

• 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.

The row was committed on the primary, but the synchronous standby never confirmed it. Unless your app reads warnings, it counts this write as safely copied when it may not be.

A crash goes the other way. We killed the primary while an insert waited, and the client saw a closed connection. After a restart, the row was there. The docs say such waiting transactions are marked fully committed.

So neither a success nor an error after COMMIT tells you for sure where the row lives. Give every ledger entry a unique key, and make a retry check for that key before writing again.

The same warning also lands in the primary's server log, so alert on it: each one is a write no synchronous standby confirmed.

Measure it from the primary with pg_stat_replication, and on a standby with a query that knows when the standby is simply idle. The obvious shortcut reports lag that is not there.

That shortcut is now() minus pg_last_xact_replay_timestamp() on the standby. It measures time since the last replayed transaction, not distance behind. After 12 quiet seconds on our caught-up test standby, it read 12.55 seconds, while the primary showed 0 bytes behind.

Here are the two queries we use. Run the first on the primary and the second on a standby:

-- On the primary: one row per standby
SELECT application_name, state, sync_state, replay_lag
FROM pg_stat_replication ORDER BY application_name;

-- On a standby: seconds behind, 0 when it has replayed all it received
SELECT CASE
         WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
         ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
       END AS lag_seconds;

Check it yourself

With ANY 1 (s1, s2), the first query printed two rows: s1, streaming, quorum and s2, streaming, quorum. With FIRST 1 (s1, s2), which waits for s1 and falls back to s2 only if s1 is gone, s1 showed sync and s2 showed potential.

With no synchronous names, both show async. On the idle standby, the second query printed 0. The replay_lag column is an interval; on our quiet single machine it stayed under a tenth of a second.

Two cautions. The docs say the lag columns can show NULL on a fully caught-up, idle system, so treat NULL as nothing to measure, not as an error. And the guarded query also reads 0 on a standby cut off from the primary, so alert on the primary's view too.

Promote the synchronous standby that holds the most WAL. With ANY 1, every confirmed commit reached at least one standby, and WAL arrives in order, so the furthest one holds them all.

We tested this with ANY 1 (s1, s2). We stopped each standby in turn while writing, so 55 commits were confirmed but s1 missed the last 5. After killing the primary, s2 held all 55 rows and s1 held 50.

A WAL position is called an LSN (log sequence number), written like 0/4004530, and bigger means further along. On each standby, read pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn(), and use the larger. The docs say the receive position is NULL if streaming has not started.

On 16.15, our restarted s1 could not reach the dead primary and reported 0/4000000 instead. That is below its replay position of 0/40041E8. This is undocumented as of 19 October 2026, so pin your version.

Explain it as a few clear choices: who takes writes, how long a write waits, and what happens on failure. Tie each choice to the data you are storing.

1. 1

   Name the data and its cost of loss

   Say what breaks if the newest writes vanish. Losing a like is fine. Losing a payment entry is not.

2. 2

   Choose who takes writes

   Start with one primary unless users are far apart and writes must stay local. Say why multi-primary conflicts would hurt here.

3. 3

   Choose sync or async

   Make money writes synchronous to at least one standby, and wait for any one of two so a single failure does not block writes.

4. 4

   Route reads with lag in mind

   Send reports to replicas and keep reads that must be fresh, such as balances, on the primary.

5. 5

   Walk through failover

   Detect, fence, promote the standby with the most WAL, repoint. Name split brain and say how fencing prevents it.

6. 6

   Mention quorums as the alternative

   Explain R + W > N for a leaderless store, and where it fits better, such as carts or sessions.