Stage 3 · Build

PostgreSQL Operations

Streaming Replication

Primary/replica setup, replication slots, monitoring lag, and promoting replicas.

8 min readDatabases for SREBuild

How Streaming Replication Works

Streaming replication sends WAL (Write-Ahead Log) records from the primary to replicas in real-time. The primary writes changes to WAL, and replicas replay those changes to maintain an identical copy. This provides read scaling and disaster recovery.

Replication can be synchronous or asynchronous. Synchronous replication guarantees zero data loss but adds latency. Asynchronous replication has minimal performance impact but allows a small window of data loss if the primary fails.

Configuring the Primary

Primary server configuration


-- Enable replication
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 5;
ALTER SYSTEM SET wal_keep_size = '1GB';

-- Create replication user
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

-- Allow replication connections
-- In pg_hba.conf:
-- host replication replicator 10.0.0.0/24 scram-sha-256

-- Verify replication status
SELECT * FROM pg_stat_replication;

max_wal_senders controls how many concurrent replication connections are allowed. Set it higher than the number of replicas to allow for lag and reconnection. wal_keep_size determines how much WAL to retain on the primary.

Setting Up a Replica

Initialize replica from primary


# 1. Take base backup from primary
pg_basebackup -h primary.example.com -U replicator \
  -D /var/lib/postgresql/data \
  --checkpoint=fast \
  --wal-method=stream \
  -P -R

# 2. The -R flag creates:
#    - standby.signal file
#    - primary_conninfo in postgresql.auto.conf

# 3. Configure replica (optional tuning)
cat >> /var/lib/postgresql/data/postgresql.auto.conf << EOF
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s
max_standby_archive_delay = 60s
EOF

# 4. Start the replica
pg_ctl -D /var/lib/postgresql/data start

# 5. Verify on primary
SELECT client_addr, state, sent_lsn, replay_lsn,
       replay_lag FROM pg_stat_replication;

hot_standby = on allows the replica to serve read queries while replicating. hot_standby_feedback prevents the primary from vacuuming rows the replica still needs, avoiding canceling long-running replica queries.

Replication Slots

Replication slots tell the primary how far behind each replica is. This prevents the primary from deleting WAL segments that a replica has not yet received, eliminating a common cause of replication failure.

Replication slot management


-- Create a replication slot
SELECT pg_create_physical_replication_slot('replica1_slot');

-- View all slots
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- Monitor slot lag
SELECT
    slot_name,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_pretty
FROM pg_replication_slots;

-- Drop unused slot
SELECT pg_drop_replication_slot('replica1_slot');

Without replication slots, a replica that falls too far behind will lose its place in the WAL stream and need a full re-sync. Slots prevent this by retaining WAL until the replica confirms receipt.

Replication slots can cause WAL bloat

If a replication slot is not consumed (inactive replica), the primary retains all WAL segments since the slot was created. Monitor pg_replication_slots and drop inactive slots to prevent disk exhaustion.

Monitoring Replication Lag

Replication lag monitoring query


-- On primary: check replication status
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    replay_lag
FROM pg_stat_replication;

-- On replica: check if replication is caught up
SELECT
    pg_is_in_recovery() AS is_replica,
    pg_last_wal_receive_lsn() AS received,
    pg_last_wal_replay_lsn() AS replayed,
    pg_wal_lsn_diff(
        pg_last_wal_receive_lsn(),
        pg_last_wal_replay_lsn()
    ) AS replay_gap_bytes,
    now() - pg_last_xact_replay_timestamp() AS time_since_last_replay;

replay_lag is the wall-clock time behind the primary. If this grows steadily, the replica cannot keep up — check for long-running queries that block replay or insufficient I/O throughput.

Failover and Promotion

Manual failover procedure


# 1. Stop writes to primary (or verify it is down)
pg_ctl -D /var/lib/postgresql/data stop

# 2. On the replica to promote
pg_ctl -D /var/lib/postgresql/data promote

# 3. Verify promotion
psql -h new_primary -U postgres -c "SELECT pg_is_in_recovery();"
# Should return: false

# 4. Update application connection strings
# 5. Initialize other replicas from new primary
# 6. Old primary becomes replica when it recovers

Manual failover is risky under pressure. Use tools like Patroni, pg_auto_failover, or repmgr for automated failover with health checks and fencing.

Use Patroni for automated failover

Patroni is the de facto standard for PostgreSQL high availability. It manages replication, failover, and leader election using DCS (etcd, Consul, or ZooKeeper). It handles split-brain prevention and automatic recovery.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.