Postgres Streaming Replication

Replication is the foundation of Postgres HA. Setup is fewer steps than people expect.

Step 1: Configure primary

Streaming replication starts on the primary. A handful of conf settings enable WAL streaming; everything else flows from there.

• WAL level. wal_level = replica in postgresql.conf; this is the prerequisite for streaming.
• Sender slots. max_wal_senders = 10 reserves replication slots; one per replica plus headroom.
• Replicator role. Create a dedicated replicator role with REPLICATION privilege; do not reuse the app user.
• pg_hba.conf. Allow the replica's IP to connect on the replication database; restart Postgres to pick up the changes.

Step 2: Take base backup

• pg_basebackup -h primary -D /var/lib/pgsql/data -P -U replicator
• Creates a clone of primary’s data.

Step 3: Start replica

The replica needs to know it is a standby and where to stream from. The signal file is the modern way; recovery.conf is gone.

• Standby signal. Create an empty standby.signal file in the data directory; PG12+ uses this instead of recovery.conf.
• Connection string. Set primary_conninfo in postgresql.conf pointing at the primary with the replicator credentials.
• Replication slot. Optional but recommended: primary_slot_name prevents WAL deletion if the replica falls behind.
• Start. Boot the replica; it connects to the primary and streams WAL continuously.

Step 4: Verify replication

Replication looks fine until you check it. Two queries confirm the link is live and the lag is acceptable.

• On the primary. SELECT * FROM pg_stat_replication; shows each connected replica with its current state.
• Lag check. The replay_lag column should be sub-second under normal load; sustained seconds indicate a problem.
• On the replica. Read-only queries succeed; write attempts return cannot execute INSERT in a read-only transaction.
• Monitoring. Alert on replay_lag > 30s and on disconnected senders; both signal a failover risk.

Antipatterns

• Replica with no monitoring. Lag undetected.
• Manual failover only. Practice or use Patroni.
• Replica writes. Will not work; data drift if forced.

What to do this week

Three moves. (1) Run the tutorial end-to-end on your own laptop / sandbox. (2) Apply the pattern to one production workload. (3) Document the variations you needed; share with the team.