How to Configure PostgreSQL Streaming Replication

How to Configure PostgreSQL Streaming Replication

PostgreSQL streaming replication continuously ships write-ahead log (WAL) records from a primary server to one or more standby servers. This provides high availability and read scalability for applications running on your Breeze instances, with near-zero replication lag in most configurations.

Preparing the Primary Server

On your primary Breeze instance, create a dedicated replication user:

sudo -u postgres psql -c "
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'ReplSecure123!';"

Edit /etc/postgresql/16/main/postgresql.conf on the primary:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024    # MB of WAL to retain
max_replication_slots = 5
synchronous_commit = on
hot_standby = on

Add a replication entry to /etc/postgresql/16/main/pg_hba.conf:

host replication replicator standby-breeze-ip/32 md5

Restart PostgreSQL on the primary:

sudo systemctl restart postgresql

Creating a Replication Slot

Replication slots ensure the primary retains WAL segments until the standby has consumed them:

sudo -u postgres psql -c "
SELECT pg_create_physical_replication_slot('standby1_slot');"

Setting Up the Standby Server

On your standby Breeze instance, stop PostgreSQL and remove the existing data directory:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

Use pg_basebackup to create a base backup from the primary:

sudo -u postgres pg_basebackup -h primary-breeze-ip -U replicator \
  -D /var/lib/postgresql/16/main -Fp -Xs -P -R -S standby1_slot

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

Verifying the Configuration

Check the auto-generated connection settings in /var/lib/postgresql/16/main/postgresql.auto.conf:

primary_conninfo = 'host=primary-breeze-ip port=5432 user=replicator password=ReplSecure123!'
primary_slot_name = 'standby1_slot'

Start PostgreSQL on the standby:

sudo systemctl start postgresql

Checking Replication Status

On the primary, verify the standby is connected:

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

On the standby, confirm it is in recovery mode:

SELECT pg_is_in_recovery();  -- Should return true
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

Enabling Synchronous Replication

For zero data loss, configure synchronous replication on the primary. Edit postgresql.conf:

synchronous_standby_names = 'standby1_slot'

This ensures the primary waits for the standby to confirm writes before committing. Note that this adds latency to write operations, so evaluate the trade-off between data safety and write performance for your workload on your Breeze instances.

Failover Considerations

If the primary fails, promote the standby to become the new primary:

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

Update your application connection strings to point to the new primary. For automated failover, consider tools like Patroni or repmgr that handle leader election and configuration management across your Breeze cluster.