Pro ยท Replication
Physical copies ship the write-ahead log. Logical copies publish tables. Slots remember how far the copy has read.
Sign in to save your place.
Physical replication photocopies the whole book, page by page, in the order the pages were written. That order is the write-ahead log. Logical replication reads the sentences you care about and sends those sentences to another book, which can be a different edition. A replication slot is a bookmark. Without it, the primary may throw away pages the copy has not read yet.
Streaming replication ships WAL from a primary to one or more standbys. The standby is a full copy. It can serve reads when hot standby is on. It cannot take writes until you promote it. The primary process is a walsender. The standby process is a walreceiver.
Synchronous replication waits until a named standby has flushed the WAL. Asynchronous replication, the default, does not wait. Synchronous mode reduces the chance of losing the last transactions. It also makes commits wait on the network. synchronous_standby_names lists who must confirm. If that standby is down and you required it, commits stall. Plan for that before you turn it on.
-- On the primary, postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
-- pg_hba.conf: let the standby connect for replication
-- host replication replicator 10.0.0.8/32 scram-sha-256A standby is created from a base backup, then it catches up by streaming. pg_basebackup is the usual tool. File-system snapshots from a volume manager also work when they are crash-consistent. Do not copy the data directory of a running primary with an ordinary file copy and call it a replica.
A physical replication slot remembers how far a consumer has received WAL. The primary will not recycle WAL the slot still needs. That protects the standby. It can also fill the primary disk if the standby stays down and nothing drops the slot. Monitor pg_replication_slots for slots that are not active, and alert on WAL retained because of them.
SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots;
SELECT pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS retained
FROM pg_replication_slots;Logical slots do the same job for logical decoding. PostgreSQL 17 can fail a logical slot over to a new primary when failover slots are configured. On older versions, a promotion can invalidate a logical slot and you rebuild the subscription. Know which version you run before you promise a slot will survive a failover.
Logical replication copies chosen tables, not the whole cluster. On the publisher you create a publication. On the subscriber you create a subscription that connects to the publisher and lists that publication. Rows that change on the published tables are applied on the subscriber.
-- Publisher
CREATE PUBLICATION orders_pub FOR TABLE public.orders;
-- Subscriber
CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=publisher dbname=shop user=replicator password=replace-me'
PUBLICATION orders_pub;The subscriber is a normal database. You can add indexes, and you can publish onward. Schema changes are not magic. Adding a column in recent PostgreSQL versions can replicate, and a careless ALTER can still break apply. Initial table copy happens when you create the subscription, unless you say otherwise. Sequences, large objects, and some DDL are the usual surprises. Read the current logical-replication chapter for your major version before you migrate with it.
From PostgreSQL 16 a standby can be a logical publisher, which lets you take decoding load off the primary. Row filters and column lists, added in PostgreSQL 15, keep a table in the publication while sending only some rows or columns. Use them as a data filter, and still lock down the subscriber. A filter is not a security boundary if someone can alter the publication.
On the primary, pg_stat_replication shows each receiver, the sent and flushed positions, and whether the connection is sync. On a standby, replay lag is the clock difference from pg_last_xact_replay_timestamp() when that function returns a time. An idle database can show a null timestamp. Do not page a human for a null that means "no new transactions".
SELECT application_name, state, sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_bytes
FROM pg_stat_replication;
SELECT subname, pid, latest_end_time
FROM pg_stat_subscription;Failover for physical replication is a promotion. pg_ctl promote or the trigger file your automation uses makes the standby accept writes. After promotion, point applications at the new primary and rebuild standbys from it. Tools such as Patroni do this with a consensus lock so two nodes do not both decide they are primary. Two primaries is a split brain, and it is worse than an outage.
Connection pooling with PgBouncer sits in front of Postgres. It is not replication. It matters for availability because a promoted primary still needs clients to find it, and a pool pointed at the old host will look like a failed failover.
Amazon RDS, Aurora, Cloud SQL, and Azure Database for PostgreSQL create replicas and failover for you. You often cannot see walsender settings, and you may not create physical slots yourself. Logical replication is frequently allowed, with limits on which publications and which extensions exist. Read the service's page before you paste a self-hosted runbook into a managed console. A replica you cannot promote on your own schedule is still their replica.
Every change is written to the WAL, a slot remembers what the standby still needs, and the standby replays it.
Step 1 of 3Every change is first written to the write-ahead log (WAL), page by page, before it is applied to the tables.
SELECT pg_current_wal_lsn();Step 2 of 3A replication slot stops the primary from deleting WAL the standby still needs. A forgotten slot keeps WAL forever and can fill the disk.
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;Step 3 of 3The standby streams the WAL and replays it, staying a read-only copy you can promote if the primary fails.
SELECT client_addr, state, replay_lag FROM pg_stat_replication;On a database you are allowed to break, create a publication for one table and a subscription in a second database on the same server. Insert a row on the publisher and select it on the subscriber. Then look at pg_replication_slots and name the slot type. Drop the subscription when you are done so the slot does not hold WAL.
Checked against vendor documentation in October 2026. Screens and plan names still change, so confirm the control in your own instance.