PostgreSQL Streaming Replication Setup: Complete Guide for PG15+

Primary (192.168.1.10)
  └─ WAL stream ──► Standby (192.168.1.11)
                         └─ WAL stream ──► Standby2 (192.168.1.12) [optional cascade]

# postgresql.conf on primary
wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'
hot_standby = on

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl_pass';

# TYPE  DATABASE        USER         ADDRESS         METHOD
host    replication     replicator   192.168.1.11/32 scram-sha-256
host    replication     replicator   192.168.1.12/32 scram-sha-256

# Run on standby server
pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/15/main \
  -P -Xs -R --checkpoint=fast

# -R automatically creates standby.signal and sets primary_conninfo

hot_standby = on
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=repl_pass'

-- On primary
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- On standby
SELECT status, received_lsn, latest_end_lsn,
       now() - latest_end_time AS replication_lag
FROM pg_stat_wal_receiver;

# Promote standby to primary
pg_ctl promote -D /var/lib/postgresql/15/main

# Or via SQL (PostgreSQL 12+)
SELECT pg_promote();