Implementing Streaming Replication

# postgresql.conf
wal_level = replica
max_wal_senders = 10
hot_standby = on
listen_addresses = '*'

ALTER SYSTEM SET max_wal_senders = 16;
SELECT pg_reload_conf();

# PG 13+
wal_keep_size = 1GB     # minimum WAL retained on primary

CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'replPass';

# pg_hba.conf
host  replication  repl  10.0.0.0/24  scram-sha-256

pg_basebackup -h primary -U repl -D /var/lib/postgresql/standby \
   -Fp -Xs -P -R -S standby_slot

cd /var/lib/postgresql/standby
ls standby.signal              # marker file present after -R

# postgresql.auto.conf (written by pg_basebackup -R)
primary_conninfo = 'host=primary port=5432 user=repl password=replPass application_name=standby1'
primary_slot_name = 'standby_slot'

pg_ctl -D /var/lib/postgresql/standby start
psql -c "SELECT pg_is_in_recovery();"   # → t

-- On primary
SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)  AS sent_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
  FROM pg_stat_replication;
-- On standby
SELECT now() - pg_last_xact_replay_timestamp() AS apply_lag;

SELECT pg_create_physical_replication_slot('standby_slot');
SELECT * FROM pg_replication_slots;

SELECT pg_promote(wait := true, wait_seconds := 60);
-- or:  pg_ctl promote -D /var/lib/postgresql/standby

synchronous_commit         = on
synchronous_standby_names  = 'FIRST 1 (standby1, standby2)'
# or: 'ANY 2 (s1, s2, s3)'