Build a High‑Availability PostgreSQL Cluster with Streaming Replication and Read‑Write Splitting from Scratch

Before starting, the author stresses the need to clarify the goals: high availability (automatic failover) and read‑write separation (offloading read‑heavy queries from the primary). A three‑tier architecture is proposed: client → read‑write routing layer → PostgreSQL primary‑standby cluster.

1. Architecture Overview

Key concepts are defined: Primary (read/write, generates WAL), Standby (read‑only, replays WAL), WAL (write‑ahead log), and LSN (log sequence number for monitoring replication lag).

2. Configuring Streaming Replication

Step 1 – Create a replication user :

CREATE ROLE repuser WITH REPLICATION LOGIN PASSWORD 'your_strong_password';

This role can only read WAL streams, which is safer than a superuser.

Step 2 – Edit postgresql.conf on the primary (example for PG 15+):

wal_level = replica          -- required for streaming
max_wal_senders = 10       -- max concurrent standbys
wal_keep_size = 1024      -- keep 1 GB WAL
max_replication_slots = 10
listen_addresses = '192.168.1.10'
archive_mode = on
archive_command = 'cp %p /archive/%f'  -- recommended

Step 3 – Configure pg_hba.conf to allow the replication user:

host replication repuser 192.168.1.20/32 scram-sha-256

Step 4 – Restart the primary (systemd or pg_ctl reload).

Step 5 – Take a base backup on the standby using pg_basebackup (or rsync for very large databases):

sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/16/main \
    -U repuser -P -v --wal-method=stream

If the database is terabytes, the author suggests the rsync method with pg_start_backup / pg_stop_backup.

Step 6 – Create standby.signal (PG 12+):

sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal

Step 7 – Configure the standby’s postgresql.conf :

primary_conninfo = 'host=192.168.1.10 port=5432 user=repuser password=your_strong_password'
primary_slot_name = 'standby1_slot'   -- use a replication slot
hot_standby = on
hot_standby_feedback = on
wal_receiver_status_interval = 5

Step 8 – Create a physical replication slot on the primary to prevent WAL loss when the standby disconnects:

SELECT pg_create_physical_replication_slot('standby1_slot');

Without a slot, a long‑running disconnection would require a new base backup.

Step 9 – Start the standby : sudo systemctl start postgresql Step 10 – Verify replication on the primary: SELECT * FROM pg_stat_replication; Look for state = 'streaming' and fields such as sent_lsn, write_lsn, flush_lsn, replay_lsn, plus write_lag / replay_lag. To measure lag in bytes:

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes FROM pg_stat_replication;

3. Choosing a Replication Mode

Asynchronous (default) : primary returns to the client immediately; WAL is shipped later. Performance impact < 5 %, but a few seconds of data may be lost if the primary crashes. Suitable for most workloads.

Synchronous : primary waits until at least one standby confirms receipt. Configured with synchronous_standby_names and synchronous_commit. Four commit levels are described (on, remote_write, remote_apply, local). The author notes that network latency can add tens of milliseconds per transaction across data centers, so synchronous mode is recommended only for critical data.

Cascading replication is introduced for many standbys: a primary streams to Standby A, which then streams to Standby B/C, reducing connection pressure on the primary.

4. Read‑Write Splitting

Several implementation options are compared:

Hard‑coded application routing – low maintenance, good for tiny projects.

PgBouncer – connection pooling with simple routing.

ProxySQL + PG – complex routing, moderate maintenance.

Pgpool‑II – full‑featured (pooling + load balancing) but higher configuration cost.

ORM‑level routing (e.g., Rails, Django) – easiest when the framework supports multiple data sources.

The author’s recommendation for most projects is application‑level routing combined with PgBouncer pooling . An example using Python/SQLAlchemy shows two engines (write and read) and a custom RoutingSession that directs writes to the primary and reads to the standby.

PgBouncer configuration is described: define two database names (e.g., mydb_write → primary, mydb_read → standby(s)). Applications connect to the appropriate name based on operation.

5. Monitoring & Alerting

Two core queries are provided to monitor lag:

SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

Suggested alert thresholds: replay lag > 10 s (warning), > 60 s (critical); standby disconnected > 5 min; pg_wal directory > 10 GB; replication slot idle > 1 h.

6. Failover & Recovery

Manual failover (primary down): run on the standby:

sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main/

The promoted standby becomes the new primary. To bring the old primary back, the author explains that a simple re‑add is unsafe because data diverges; instead, recreate the standby: create a slot on the new primary, take a fresh base backup of the old primary, create standby.signal, and point primary_conninfo to the new primary.

If replication slots are used, a disconnected standby will resume from the last WAL position automatically; without slots, WAL may be recycled, forcing a new base backup.

For production, the author recommends automated tools such as Patroni (etcd/Consul‑based automatic detection and failover) or repmgr (lightweight, event‑driven semi‑automatic failover). Patroni configuration is left for a future article.

7. Frequently Asked Questions

Replication lag growth – check standby CPU, long‑running read‑only transactions, network bandwidth, disk I/O, and write bursts. pg_basebackup out‑of‑memory – increase related memory parameters or switch to rsync.

Standby cannot connect – verify network/firewall, pg_hba.conf entry, password, and listen_addresses.

Replication slot filling disk – drop inactive slots with SELECT pg_drop_replication_slot('dead_slot');. PG 17 adds max_slot_wal_keep_size (suggest 10 GB per slot).

Can a standby write? – No; Hot Standby is strictly read‑only, even temporary tables are prohibited.

Physical vs logical replication – physical copies the whole instance (same major version, high‑availability); logical copies selected tables, can cross major versions, useful for migrations or real‑time data warehouses.

8. Final Checklist

Set wal_level = replica and configure pg_hba.conf – streaming works.

Always use a replication slot to avoid data loss after standby outages.

Asynchronous replication meets the needs of most workloads; use synchronous only for critical data.

Best read‑write splitting: application‑level routing + PgBouncer pooling.

Monitor both byte lag and replay_lag time.

Run through the entire process in a test environment (base backup → promote → failback) to ensure the team knows the manual steps before a real incident.

Author: 热爱生活的老张