PostgreSQL streaming replication + automatic failover

Symptom

Goal: set up a hot standby for PostgreSQL with streaming replication and add Patroni/repmgr for automatic failover.

1. Set WAL and connection parameters on the primary

For streaming replication, wal_level must be at least 'replica'. max_wal_senders must cover the number of replicas + backup connections, and early WAL removal must be prevented with wal_keep_size or a replication slot. The changes require a restart.

PostgreSQL

ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET hot_standby = on;
-- To apply the postgresql.conf change:
-- systemctl restart postgresql

2. Define the replication role and pg_hba access

Create a role with the REPLICATION attribute for the standby to connect with. Add a line to pg_hba.conf allowing access to the 'replication' database from the standby IP, and reload with pg_reload_conf().

PostgreSQL

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';
-- Add to pg_hba.conf:
-- host replication replicator 10.0.0.20/32 scram-sha-256
SELECT pg_reload_conf();

3. Create a persistent replication slot on the primary

A physical replication slot prevents the primary from removing needed WAL even when the standby falls behind; slot management is safer than wal_keep_size. We will use the slot name in the standby configuration.

PostgreSQL

SELECT pg_create_physical_replication_slot('standby1_slot');
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

4. Take a base backup on the standby server with pg_basebackup

With the standby's data directory empty, take a full copy from the primary. The -R flag automatically writes the standby.signal file and primary_conninfo; -C can also create the slot. The slot name is passed with -S.

PostgreSQL

pg_basebackup -h 10.0.0.10 -U replicator -D /var/lib/pgsql/data \
  -R -X stream -S standby1_slot -P -v

5. Verify primary_conninfo and hot_standby, start the standby

Check primary_conninfo (host, user, slot) in the postgresql.auto.conf written by -R; the presence of the standby.signal file determines standby mode. With hot_standby=on the standby accepts read-only queries. Start the service.

PostgreSQL

-- Expected in postgresql.auto.conf:
-- primary_conninfo = 'host=10.0.0.10 user=replicator password=secret'
-- primary_slot_name = 'standby1_slot'
-- systemctl start postgresql
SELECT pg_is_in_recovery();  -- should return true on the standby

6. Monitor replication lag from the primary

On the primary, pg_stat_replication shows the sent/replayed LSN and lag for each standby. write/flush/replay lag should stay small; sync_state is 'sync' if a synchronous replica is used.

PostgreSQL

SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

7. Add Patroni or repmgr for automatic failover

Bare streaming replication requires a manual promote. Automatic failover requires a cluster manager: Patroni (leader election on etcd/Consul + automatic promote) or repmgr (monitoring/failover with repmgrd). On the application side, provide a single endpoint with HAProxy/pgBouncer. If a manual promote is needed: pg_ctl promote. Before promoting, make sure the old primary is really stopped and applications cannot write to it (split-brain risk). Rejoining the old primary as a standby of the new primary requires pg_rewind; for that, wal_log_hints = on or data checksums must be enabled.

PostgreSQL

-- Manual promote (if there is no cluster manager):
pg_ctl promote -D /var/lib/pgsql/data
-- or via SQL:
SELECT pg_promote();
-- With Patroni: patronictl -c /etc/patroni.yml switchover mycluster