PostgreSQL Replication: Streaming and Logical
On this page
Physical vs logical replication
"Replication" covers two features that solve different problems:
- Physical streaming replication sends the primary's WAL (Day 2) to standby servers, which replay it and stay identical to the primary, block for block. Use it for high availability: a standby that can take over when the primary dies, plus read-only replicas for extra read capacity.
- Logical replication decodes the WAL into row changes (this row inserted, that one updated) and applies them to another database as ordinary writes. Use it for moving data: some tables into a reporting database, feeding another system, or upgrading to a new major version with almost no downtime.
| Physical | Logical | |
|---|---|---|
| Copies | The whole cluster | Chosen tables |
| Target | Read-only standby | Normal, writable database |
| Across major versions | No | Yes |
| Schema changes (DDL) | Replicated | Not replicated, apply them yourself |
| Typical use | Failover, read replicas | Reporting copies, upgrades, data feeds |
Physical streaming replication and hot standby
A standby starts as a base backup of the primary (Day 26), then keeps streaming new WAL as it's written. On the primary, allow replication connections:
# postgresql.conf on the primary (wal_level = replica is the default)
max_wal_senders = 10
# pg_hba.conf on the primary
host replication replicator 10.0.0.0/24 scram-sha-256On the standby, pg_basebackup -R copies the data and writes the connection settings plus a standby.signal file, so the server starts as a standby:
pg_basebackup -h db-primary -U replicator -D /var/lib/postgresql/18/main \
-Fp -Xs -P -R --slot=standby1 --create-slot
pg_ctl -D /var/lib/postgresql/18/main startThe standby accepts read-only queries while it replays WAL. This is called hot standby, and it's a common way to move reporting traffic off the primary.
Check replication from the primary:
SELECT application_name, state, replay_lag FROM pg_stat_replication;Replication slots, and how they fill disks
What if the standby is offline for a few hours? The primary normally recycles old WAL files, and when the standby comes back the WAL it needs may be gone. Then it has to be rebuilt from a new base backup.
A replication slot (the --slot above) makes the primary keep WAL until that standby has received it. The danger is the reverse case. If the standby never comes back, the primary keeps WAL forever, the disk fills, and the primary goes down. Two safeguards:
- Set
max_slot_wal_keep_size(for example100GB) so a slot can't hold more than that. A standby that falls further behind loses its slot instead of taking the primary down. - Monitor slots and drop ones you no longer need:
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
SELECT pg_drop_replication_slot('old_standby');Synchronous vs asynchronous replication
By default replication is asynchronous. The primary commits, answers the client, and sends the WAL to standbys a moment later. If the primary dies in that moment, the last few committed transactions may not have reached any standby, and a failover loses them.
Synchronous replication makes each commit wait for a standby to confirm:
synchronous_standby_names = 'ANY 1 (standby1, standby2)'
synchronous_commit = onsynchronous_commit decides what "confirmed" means:
| Value | The commit waits until a standby has... |
|---|---|
remote_write | Received the WAL and handed it to its operating system |
on (default) | Flushed the WAL to its disk |
remote_apply | Replayed it, so queries on the standby can see the change |
The price is latency. Every commit now includes a round trip to the standby, which is fine in the same data center and painful across continents. There's an availability trap too. If you name only one synchronous standby and it goes down, commits on the primary wait until it's back. ANY 1 across two standbys avoids that.
Logical replication: publications and subscriptions
Logical replication uses a publication on the source and a subscription on the target:
-- On the source, which needs wal_level = logical (requires a restart)
CREATE PUBLICATION sales_pub FOR TABLE orders, order_items;
-- On the target; the tables must already exist there with matching columns
CREATE SUBSCRIPTION sales_sub
CONNECTION 'host=db-primary dbname=salesdb user=replicator'
PUBLICATION sales_pub;The subscription first copies each table's existing rows, then streams changes as they happen. On big tables, that initial copy takes time and adds load on the source, so plan when you start it.
Because the target is an ordinary writable database, a few things behave differently from physical replication:
- Schema changes aren't copied. Add a column on the source, add it on the target too, usually on the target first.
- Sequences aren't copied. If the target will take over writes, as in an upgrade, set its sequences past the source's values first (Day 20).
- Conflicts stop replication. If a local write on the target creates a duplicate key that a replicated insert then hits, the subscription stops with an error until you fix the data.
- Rows need an identity. UPDATE and DELETE need a primary key, or another replica identity, to find the matching row.
Failover, split brain and Patroni
Failover means promoting a standby to be the new primary:
SELECT pg_promote(); -- run on the standbyThe command is the easy part. The hard parts are around it:
- Redirecting clients. Applications must find the new primary. Common answers are a DNS name or virtual IP that moves, or a proxy that health-checks the servers. libpq clients can also list several hosts with
target_session_attrs=read-write. - Fencing the old primary. If the old primary comes back and accepts writes, you have two primaries with diverging data, called split brain. It must be shut down or rebuilt as a standby of the new primary.
pg_rewindcan turn it into a standby without a full new copy. - Automation. Doing all of this by hand at 3am is slow and error-prone. Tools like Patroni automate detection, promotion and fencing. Day 30 builds a full design around them.
Practice failover regularly outside production. An untested failover plan usually fails at the worst possible moment.
Check yourself
Drop the slot with pg_drop_replication_slot, and the primary will recycle the WAL it was holding. Set max_slot_wal_keep_size and alert on inactive slots so it can't happen again.
Synchronous replication, for example synchronous_standby_names = 'ANY 1 (s1, s2)' with synchronous_commit = on. Every commit waits for a standby to flush the WAL, which adds the round-trip time to each commit.
Logical. Physical replication copies the whole cluster and needs the same major version, while logical replication can send just those tables to a different version.
The replica must apply changes from the primary, and some, like vacuum removing old rows, conflict with queries still reading them. Options include hot_standby_feedback = on, which tells the primary to keep those rows, or a larger max_standby_streaming_delay, each with trade-offs in bloat or replica lag.
Split brain is two servers both acting as primary and accepting writes, which makes their data diverge. Patroni uses a distributed consensus store, such as etcd, as the single source of truth for who holds the leader lock, so only one node can be primary at a time.