How to Create Read Replica in PostgreSQL: Step-by-Step Guide

To create a read replica in PostgreSQL, set up streaming replication by configuring the primary server to allow replication connections and create a standby server that continuously receives WAL (Write-Ahead Logging) data. Use pg_basebackup to copy the primary data directory and configure postgresql.conf and recovery.conf (or standby.signal in newer versions) on the replica to start replication.

Syntax

Creating a read replica involves configuring the primary and standby servers with specific settings.

• Primary server: Enable replication and set WAL level.
• Standby server: Use pg_basebackup to copy data and configure recovery settings.

Key configuration files and parameters:

• postgresql.conf: Set wal_level = replica, max_wal_senders, and hot_standby = on.
• pg_hba.conf: Allow replication connections from standby.
• recovery.conf or standby.signal: Configure standby to connect to primary.

bash

## On Primary server (postgresql.conf)
wal_level = replica
max_wal_senders = 5
hot_standby = on

## On Primary server (pg_hba.conf)
host replication replicator 192.168.1.0/24 md5

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

## On Standby server (postgresql.conf)
hot_standby = on

## Create standby.signal file in data directory (PostgreSQL 12+)
# touch /var/lib/postgresql/data/standby.signal

## On Standby server (postgresql.auto.conf or recovery.conf for older versions)
primary_conninfo = 'host=primary_host port=5432 user=replicator password=yourpassword'

Example

This example shows how to create a read replica by copying the primary data directory and configuring the standby server for streaming replication.

bash

# On Primary server:
# 1. Edit postgresql.conf
wal_level = replica
max_wal_senders = 3
hot_standby = on

# 2. Edit pg_hba.conf to allow replication user
host replication replicator 192.168.1.100/32 md5

# 3. Reload PostgreSQL
pg_ctl reload

# On Standby server:
# 4. Use pg_basebackup to copy data
pg_basebackup -h 192.168.1.100 -D /var/lib/postgresql/12/main -U replicator -P --wal-method=stream

# 5. Create standby.signal file
touch /var/lib/postgresql/12/main/standby.signal

# 6. Create postgresql.auto.conf with primary connection info
echo "primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=yourpassword'" > /var/lib/postgresql/12/main/postgresql.auto.conf

# 7. Start PostgreSQL on standby
pg_ctl start -D /var/lib/postgresql/12/main

# 8. Check replication status on primary
psql -c "SELECT * FROM pg_stat_replication;"

Output

pid | usesysid | usename   | application_name | client_addr  | state   | sync_state
----+----------+----------+------------------+--------------+---------+-----------
123 | 16384    | replicator | walreceiver     | 192.168.1.101 | streaming | sync

Common Pitfalls

Common mistakes when creating a read replica include:

• Not setting wal_level to replica on the primary, which disables WAL streaming.
• Forgetting to allow replication connections in pg_hba.conf.
• Not creating the standby.signal file (PostgreSQL 12+) or misconfiguring recovery.conf on the standby.
• Using mismatched PostgreSQL versions between primary and standby.
• Failing to use pg_basebackup or copying data incorrectly, causing replication errors.

bash

# Wrong: Missing wal_level setting on primary
# postgresql.conf
# wal_level = minimal

# Right: Set wal_level to replica
wal_level = replica

# Wrong: No replication user in pg_hba.conf
# pg_hba.conf
# (no entry for replication)

# Right: Add replication user
host replication replicator 192.168.1.0/24 md5

# Wrong: No standby.signal or recovery.conf on standby
# Right: Create standby.signal file
# touch /var/lib/postgresql/data/standby.signal

Quick Reference

Summary tips for creating a PostgreSQL read replica:

• Set wal_level = replica and max_wal_senders on primary.
• Allow replication connections in pg_hba.conf.
• Use pg_basebackup to copy primary data to standby.
• Create standby.signal file on standby (PostgreSQL 12+).
• Configure primary_conninfo with connection details on standby.
• Start standby server and verify replication with pg_stat_replication.

Key Takeaways

Enable streaming replication by setting wal_level to replica on the primary server.

Use pg_basebackup to copy the primary data directory to the standby server.

Create standby.signal file and configure primary_conninfo on the standby for replication.

Allow replication connections in pg_hba.conf for the replication user.

Verify replication status using pg_stat_replication on the primary server.