How to Set Up Streaming Replication in PostgreSQL: Step-by-Step Guide

To set up streaming replication in PostgreSQL, configure the primary server to allow replication connections and enable WAL archiving, then set up the standby server to continuously receive WAL data from the primary. This involves editing postgresql.conf and pg_hba.conf on the primary, creating a base backup, and starting the standby with replication settings.

Syntax

Streaming replication requires changes in two main configuration files on the primary server and specific settings on the standby server.

• postgresql.conf: Enable WAL level and replication settings.
• pg_hba.conf: Allow replication connections from standby.
• Standby setup: Use pg_basebackup to copy data and configure standby.signal for replication.

conf

# On primary server's postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 16MB

# On primary server's pg_hba.conf
host replication replicator 192.168.1.100/32 md5

# On standby server
pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P --wal-method=stream

# Create standby.signal file in data directory to enable standby mode

Example

This example shows how to configure a primary and a standby server for streaming replication on PostgreSQL.

bash

# Step 1: On primary server, edit postgresql.conf
wal_level = replica
max_wal_senders = 3
wal_keep_size = 64MB

# Step 2: On primary server, edit pg_hba.conf
host replication replicator 192.168.1.101/32 md5

# Step 3: Reload primary server config
pg_ctl reload

# Step 4: Create replication user on primary
psql -c "CREATE ROLE replicator WITH REPLICATION PASSWORD 'password' LOGIN;"

# Step 5: On standby server, run base backup
pg_basebackup -h 192.168.1.100 -D /var/lib/postgresql/12/main -U replicator -P --wal-method=stream

# Step 6: On standby server, create standby.signal file
cd /var/lib/postgresql/12/main
touch standby.signal

# Step 7: Create postgresql.auto.conf or edit postgresql.conf on standby
primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=password'

# Step 8: Start standby server
pg_ctl start -D /var/lib/postgresql/12/main

Output

starting PostgreSQL 12 server
waiting for server to start... done
server started

Common Pitfalls

Common mistakes when setting up streaming replication include:

• Not setting wal_level to replica or higher on the primary.
• Forgetting to allow replication connections in pg_hba.conf.
• Not creating the replication user with proper permissions.
• Failing to create the standby.signal file on the standby server (PostgreSQL 12+).
• Incorrect primary_conninfo string causing connection failures.

Example of a wrong pg_hba.conf entry and the correct one:

conf

# Wrong (missing replication keyword)
host all all 192.168.1.101/32 md5

# Correct
host replication replicator 192.168.1.101/32 md5

Quick Reference

Setting	Description	Example Value
wal_level	Controls WAL data for replication	replica
max_wal_senders	Max concurrent WAL sender processes	5
wal_keep_size	WAL files to keep for standby	64MB
pg_hba.conf entry	Allow replication connections	host replication replicator 192.168.1.101/32 md5
replication user	User with REPLICATION privilege	CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'password';
standby.signal	File to enable standby mode (PostgreSQL 12+)	touch standby.signal
primary_conninfo	Connection string on standby to primary	'host=192.168.1.100 port=5432 user=replicator password=password'

Key Takeaways

Set wal_level to replica and configure max_wal_senders on the primary server.

Allow replication connections in pg_hba.conf with correct user and IP.

Use pg_basebackup to create a base backup for the standby server.

Create standby.signal file on standby to enable streaming replication (PostgreSQL 12+).

Ensure primary_conninfo on standby is correct for successful connection.