What is it? #
Replication keeps a second PostgreSQL server continuously up to date with the first.
The mechanism is the WAL again. The primary sends its WAL stream to a standby, which replays it continuously. The standby is a live, near-identical copy that can take over if the primary fails, and can serve read-only queries in the meantime.
The choice that matters most is synchronous versus asynchronous. Asynchronous means the primary commits without waiting for the standby — fast, with a small window where recent commits exist only on the primary. Synchronous means every commit waits for the standby to confirm — no data loss on failover, at the cost of write latency on every single transaction.
And the point that cannot be repeated too often: replication is not a backup. It protects against a server dying, not against a mistake.
Think of it like this #
A colleague sitting beside you, copying every line into an identical ledger as you write it.
If your desk catches fire, theirs is current to within a second. That is high availability.
But when you write a wrong figure, they copy the wrong figure faithfully, immediately. They are not a safety net against errors — only against losing your copy.
Asynchronous is them copying at their own pace; you keep working without waiting. Synchronous is you pausing after each line until they confirm they have written it. Nothing is ever missing from their ledger, and you write more slowly all day.
Simple example #
A production database on one server. If that server dies, restoring from backup takes two hours.
With a standby held a second behind, failover takes about a minute. The same incident becomes a brief interruption instead of a long outage — and the nightly backups still exist for the mistakes replication cannot help with.
Code #
---------- The architecture ----------
PRIMARY STANDBY
(reads + writes) (reads only)
│
│ WAL records, streamed continuously over TCP
│
[WAL sender] ───────────────────────▶ [WAL receiver]
│
▼
replays WAL into
its own data files
The standby is ALWAYS in recovery mode. It never accepts writes
until it is promoted.-- ---------- STEP 1: on the PRIMARY, create a replication role ----------
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'a-strong-password';
-- REPLICATION is a special attribute: it allows streaming the WAL but
-- grants no access to table data. Least privilege applies here too.# ---------- STEP 2: on the PRIMARY, allow the connection ----------
# In postgresql.conf:
wal_level = replica # the default; required for replication
max_wal_senders = 10 # one per standby, plus spare for backups
max_replication_slots = 10
listen_addresses = '10.0.1.5' # reachable by the standby, not the world
# In pg_hba.conf — note the special database name "replication":
# TYPE DATABASE USER ADDRESS METHOD
host replication replicator 10.0.1.6/32 scram-sha-256
# ^^^^^^^^^^^ literally the word "replication", not a database name
sudo systemctl restart postgresql # wal_level / max_wal_senders need a restart-- ---------- STEP 3: create a replication slot ----------
SELECT pg_create_physical_replication_slot('standby1');
-- A slot makes the primary RETAIN WAL until this standby has consumed it,
-- so a standby that disconnects briefly can always catch up.
--
-- !! The same property is a hazard: if the standby never returns and the
-- !! slot is left behind, WAL accumulates until the disk fills and the
-- !! PRIMARY STOPS ACCEPTING WRITES. Bound it:
-- max_slot_wal_keep_size = '50GB' (PostgreSQL 13+)# ---------- STEP 4: on the STANDBY, clone the primary ----------
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/* # !! DESTRUCTIVE: empties the
# !! standby's data directory.
# !! Be certain of the machine.
sudo -u postgres pg_basebackup \
-h 10.0.1.5 -U replicator \
-D /var/lib/postgresql/16/main \
-S standby1 \ # use the slot created above
-R \ # WRITE THE CONNECTION SETTINGS AUTOMATICALLY
-Xs -P
# -R creates standby.signal and writes primary_conninfo into
# postgresql.auto.conf, so the standby knows where to stream from.
# Without -R you would write both by hand.# ---------- STEP 5: start the standby ----------
sudo systemctl start postgresql
# Confirm it is in recovery (i.e. acting as a standby):
sudo -u postgres psql -c "SELECT pg_is_in_recovery();" # expect: t-- ---------- STEP 6: verify replication is healthy (ON THE PRIMARY) ----------
SELECT client_addr,
state, -- expect "streaming"
sync_state, -- "async" or "sync"
sent_lsn, write_lsn, flush_lsn, replay_lsn,
pg_size_pretty(
pg_wal_lsn_diff(sent_lsn, replay_lsn)
) AS replay_lag_bytes,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- The four LSNs show how far the standby has got:
-- sent the primary has sent it
-- write the standby has written it to its OS
-- flush the standby has flushed it to disk <- synchronous waits here
-- replay the standby has APPLIED it and it is visible to queries-- ---------- On the STANDBY: how far behind am I? ----------
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
-- The most useful single number. Alert if it exceeds your tolerance.
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
-- Equal -> fully caught up
-- Diverged -> receiving but replaying slowly (often a long query blocking it)---------- Asynchronous vs synchronous ----------
ASYNCHRONOUS (the default)
Primary commits and returns IMMEDIATELY. The standby follows
milliseconds to seconds behind.
+ No write-latency cost.
- If the primary dies, commits not yet sent are LOST.
Typical exposure: well under a second of transactions.
Right for most systems.
SYNCHRONOUS
Primary WAITS for the standby to confirm before COMMIT returns.
+ Zero data loss on failover.
- Every commit pays the network round trip.
- !! If the sole synchronous standby goes down, COMMITS BLOCK
on the primary. The safety feature becomes an outage.
ALWAYS run at least TWO candidates:
synchronous_standby_names = 'ANY 1 (standby1, standby2)'
so losing one does not stop the primary.# Enabling synchronous replication, on the PRIMARY:
# synchronous_commit = on
# synchronous_standby_names = 'ANY 1 (standby1, standby2)'
sudo systemctl reload postgresql
# synchronous_commit can also be set PER TRANSACTION, which is often the
# best of both worlds — pay the latency only where it matters:
# SET LOCAL synchronous_commit = 'on'; -- for a payment
# SET LOCAL synchronous_commit = 'off'; -- for an analytics insert-- ---------- Read replicas: using the standby for queries ----------
-- A standby serves read-only queries, which offloads reporting from the
-- primary. Two caveats that surprise people:
-- 1. The data may be slightly stale (replication lag). A user who writes
-- and immediately reads from the replica may not see their own change.
-- Route read-after-write traffic to the primary.
-- 2. A long query on the standby can CONFLICT with incoming WAL replay,
-- because replay wants to remove rows the query is still reading.
-- PostgreSQL resolves this by either delaying replay or cancelling
-- the query:
-- ERROR: canceling statement due to conflict with recovery
-- On the STANDBY, choose which you prefer:
-- max_standby_streaming_delay = 30s -- let replay wait up to 30s
-- hot_standby_feedback = on -- tell the primary which rows the
-- -- standby still needs, so it does
-- -- not vacuum them away
--
-- !! hot_standby_feedback = on can cause BLOAT ON THE PRIMARY, because it
-- !! holds back VACUUM there. It trades primary health for standby query
-- !! stability. Choose deliberately.# ---------- Failover: promoting a standby ----------
# 1. MAKE ABSOLUTELY SURE THE PRIMARY IS DOWN AND STAYS DOWN.
# Two servers both accepting writes ("split brain") causes divergent
# data that is extremely painful to reconcile. Fence the old primary:
# stop it, disable the service, or cut its network.
sudo systemctl stop postgresql && sudo systemctl disable postgresql
# 2. Promote the standby:
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main
# or: SELECT pg_promote();
# 3. Confirm:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();" # expect: f
# 4. Repoint the application at the new primary.
# 5. TAKE A NEW BASE BACKUP — the promoted server is on a new timeline.
# 6. Rebuild the old primary as a standby of the new one. It CANNOT simply
# be restarted as a primary; it has diverged.---------- Automatic failover ----------
Manual promotion is fine when someone is available. For automatic
failover you need a tool that handles leader election and fencing:
Patroni the most widely used; uses etcd/Consul/ZooKeeper for
consensus, and handles promotion and reconfiguration
repmgr simpler, lighter, fewer moving parts
pg_auto_failover from Citus; straightforward two-node-plus-monitor
DO NOT build automatic failover from shell scripts. Deciding correctly
that a primary is really dead — rather than briefly unreachable — is
genuinely hard, and getting it wrong creates split brain.---------- Logical replication: a different tool ----------
Streaming (physical) replication copies the WHOLE CLUSTER, byte for byte.
Logical replication copies SELECTED TABLES as row changes.
wal_level = logical
-- on the source:
CREATE PUBLICATION my_pub FOR TABLE orders, customers;
-- on the target:
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=10.0.1.5 dbname=shop user=replicator'
PUBLICATION my_pub;
Use it for: major-version upgrades with minimal downtime, replicating
a subset of tables, or consolidating data from several databases.
Limitations: it does not replicate schema changes, sequences need care,
and every replicated table must have a primary key or REPLICA IDENTITY.How it works #
Streaming replication reuses the WAL. A WAL sender process on the primary streams records to a WAL receiver on the standby, which writes them and replays them into its own data files. Because it is the same WAL that guarantees crash recovery, the standby ends up byte-identical rather than merely similar.
The standby stays permanently in recovery mode, which is what makes it read-only. Promotion ends recovery and makes it writable.
pg_basebackup -R does the tedious part of setup: it writes standby.signal and records primary_conninfo, so the cloned directory already knows how to connect and start streaming.
Replication slots make the primary retain WAL until the standby has consumed it, which lets a standby disconnect briefly and catch up rather than falling off the end. The same guarantee is the hazard covered in the WAL lesson: an abandoned slot retains WAL forever and eventually fills the primary's disk, stopping writes. max_slot_wal_keep_size puts a bound on it.
The four LSNs in pg_stat_replication describe a pipeline: sent, written, flushed, replayed. Synchronous commit waits for flush by default — the data is safely on the standby's disk, though not necessarily visible to queries there yet. The gap between flush and replay is usually the interesting one when a standby is serving reads.
Synchronous replication's failure mode deserves emphasis. If the only synchronous standby becomes unavailable, the primary has nobody to wait for, and commits block indefinitely. A feature intended to prevent data loss becomes a complete write outage. ANY 1 (standby1, standby2) means "any one of these confirms", so losing one is survivable. Running a single synchronous standby is a configuration that works perfectly until the moment it takes production down.
Standby query conflicts arise because WAL replay may need to remove rows a long-running query on the standby is still reading. PostgreSQL either delays replay (max_standby_streaming_delay) or cancels the query. hot_standby_feedback = on avoids the conflict by telling the primary which rows are still needed — at the cost of holding back VACUUM on the primary, which causes bloat there. That is a genuine trade between standby stability and primary health.
Split brain is the serious failure in failover. If the old primary is still accepting writes when the standby is promoted, both accumulate different changes, and reconciling them afterwards is painful and sometimes impossible. Fencing the old primary — ensuring it is stopped and stays stopped — is the step that prevents it, and it matters more than the promotion itself.
Real-world use #
Replication earns its place by turning "restore from backup for two hours" into "fail over in a minute". For anything with real availability requirements, that difference is the whole justification.
It does not replace backups, and this bears repeating because the mistake is common and expensive. A standby applies DROP TABLE within seconds. It protects against hardware failure and nothing else. Every production system needs both, and the PITR lesson is what covers the other half.
Asynchronous replication suits most workloads. The exposure — typically well under a second of transactions lost in a hard failure — is acceptable for the majority of applications, and it costs nothing in write latency. Reserve synchronous replication for data where losing a single committed transaction is genuinely unacceptable, and if you use it, run at least two candidate standbys.
Per-transaction synchronous_commit is underused and often the right answer: pay the latency for payments and leave analytics inserts asynchronous, in the same database.
Read replicas help genuinely with reporting load, but route read-after-write traffic to the primary. A user who saves a change and immediately sees the old value because their read went to a lagging replica is a confusing bug that is hard to reproduce.
Monitor replication lag and alert on it. now() - pg_last_xact_replay_timestamp() is the number to watch. A standby drifting steadily further behind is usually either an under-resourced standby or a long query blocking replay — and a standby that is hours behind is not the failover option anyone believes it to be.
For automatic failover use Patroni or repmgr rather than custom scripts. Correctly distinguishing a dead primary from a briefly unreachable one is a hard distributed-systems problem, and the cost of getting it wrong is split brain.
Finally, practise a failover before you need one. Promote a standby in a test environment, repoint an application, and rebuild the old primary. Discovering during an outage that the old primary cannot simply be restarted is an unpleasant surprise.
Common mistakes #
- Treating a replica as a backup — it copies DROP TABLE faithfully within seconds.
- Running a single synchronous standby, so losing it blocks every commit on the primary.
- Leaving an abandoned replication slot, which retains WAL until the primary’s disk fills.
- Promoting a standby without fencing the old primary, causing split brain and divergent data.
- Enabling hot_standby_feedback without realising it holds back VACUUM and bloats the primary.
Practice #
Set up two test servers and build streaming replication between them: create the replication role, configure pg_hba.conf for the special replication database, create a slot, clone with pg_basebackup -R, and confirm pg_stat_replication shows "streaming". Insert data on the primary and watch it appear on the standby. Then measure lag with pg_last_xact_replay_timestamp(), stop the standby for a few minutes, restart it and watch it catch up via the slot. Finally, promote the standby and observe that the old primary cannot simply rejoin.