PostgreSQL Streaming vs Logical Replication

-- ============== PRIMARY ==============
-- postgresql.conf
-- wal_level            = replica
-- max_wal_senders      = 10
-- max_replication_slots = 10
-- synchronous_commit   = on
-- synchronous_standby_names = 'standby_a'

-- pg_hba.conf
-- host replication replicator 10.0.0.0/8 scram-sha-256

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '...';

-- Physical replication slot — ensures the primary keeps WAL until the standby replays it
SELECT pg_create_physical_replication_slot('standby_a');

-- ============== STANDBY ==============
-- pg_basebackup copies the entire data directory and writes standby.signal
-- pg_basebackup -h primary -D /var/lib/pgsql/17/data -U replicator \
--               -X stream -S standby_a -R --checkpoint=fast --progress

-- Start the standby; it connects, streams WAL, and enters hot-standby read-only mode.

-- ============== MONITORING ==============
-- On primary
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS apply_lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS total_lag_bytes
FROM pg_stat_replication;

-- On standby
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

-- ============== SYNCHRONOUS MODES ==============
-- off           — fire and forget
-- local         — primary fsync only
-- remote_write  — standby received (in memory)
-- on            — standby fsynced WAL
-- remote_apply  — standby replayed (zero-lag reads on standby; costly latency)

-- ============== THE SLOT HAZARD ==============
-- If a standby is removed without dropping its slot, pg_wal grows without bound.
SELECT slot_name, active, restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS wal_bytes_kept
FROM pg_replication_slots;

-- Always drop orphaned slots
SELECT pg_drop_replication_slot('standby_a');