PostgreSQL Streaming Replication Setup

PostgreSQL Streaming Replication

Streaming replication is PostgreSQL's built-in mechanism for creating real-time copies of your database. A standby server continuously receives WAL (Write-Ahead Log) records from the primary and applies them, keeping the replica within seconds of the primary.

Architecture Overview

In a streaming replication setup:

• Primary — Accepts all read/write queries and streams WAL to standbys
• Standby (Hot) — Receives WAL, applies changes, and can serve read-only queries
• WAL Sender/Receiver — Background processes that handle the replication stream

Step-by-Step Configuration

1. Configure the Primary

# postgresql.conf on primary
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
synchronous_commit = on

# pg_hba.conf — allow replication connections
host replication replicator 10.0.0.0/24 scram-sha-256

2. Create Replication User

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

3. Take a Base Backup on Standby

pg_basebackup -h primary-host -U replicator -D /var/lib/postgresql/16/main \
  -Fp -Xs -P -R

The -R flag automatically creates standby.signal and configures the connection in postgresql.auto.conf.

4. Start the Standby

systemctl start postgresql

Monitoring Replication

-- On primary: check connected standbys
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag
FROM pg_stat_replication;

Synchronous vs Asynchronous

Eazy SaaS Tip: Start with asynchronous replication for most workloads. Only enable synchronous mode for financial or compliance-critical databases where zero data loss is mandatory.