TopolearnReplicas give high availability and read scaling; logical replication enables near-zero-downtime upgrades.
Streaming (physical) replication ships WAL from the primary to standbys that replay it byte for byte. A standby created with pg_basebackup -R (writes primary_conninfo and standby.signal) follows continuously and, with hot_standby = on, serves read-only queries. It replicates the whole cluster and requires the same major version.
synchronous_standby_namesmakes commits wait for chosen standbys (synchronous_commitcontrols how far:remote_write,on,remote_apply).- Replication slots make the primary keep WAL until a standby has received it. Without a slot a lagging standby can fall too far behind; with an abandoned slot, WAL accumulates until the disk fills. Set
max_slot_wal_keep_sizeas a safety cap. - Long queries on a standby can conflict with replay;
hot_standby_feedbackavoids cancellations at the cost of bloat on the primary. - Failover (promoting a standby with
pg_promote()) is usually automated by Patroni or a managed service.
Logical replication decodes WAL into row changes and sends them per table: CREATE PUBLICATION on the source, CREATE SUBSCRIPTION on the target. Source and target can be different major versions and have different indexes or extra tables, which enables low-downtime major upgrades, consolidating databases and feeding change data capture (Debezium). It doesn't replicate DDL or sequence values, so apply schema changes on both sides and sync sequences at cutover.
Monitor pg_stat_replication and pg_replication_slots for lag and inactive slots.
Example
-- on the source (wal_level = logical)
CREATE PUBLICATION shop_pub FOR TABLE orders, customer;
-- on the target, after creating the same tables
CREATE SUBSCRIPTION shop_sub
CONNECTION 'host=old-db dbname=shop user=replicator password=secret'
PUBLICATION shop_pub; -- copies existing rows, then streams changes
-- on the primary: replica lag and slot health
SELECT application_name, state, sync_state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots; -- inactive slot + growing retained = dangerRemember
- Streaming replication copies the whole cluster for HA and read replicas
- Slots retain WAL for consumers; abandoned slots fill disks
- Logical replication works per table across major versions
Watch out for
- Leaving an inactive replication slot behind after decommissioning a replica.