Master PostgreSQL Streaming Replication: Step‑by‑Step Setup Guide

Core Concepts

Primary (master) : Handles all read‑write traffic; every data modification occurs on this server.

Standby (replica) : Continuously receives WAL records from the primary and replays them, providing read‑only access and keeping data consistent.

WAL (Write‑Ahead Logging) : All changes are first written to the WAL before the data files are updated.

Streaming replication : The standby receives WAL records over a TCP stream and applies them in near real‑time.

Replication modes

Asynchronous (default) : The primary commits a transaction without waiting for the standby, giving higher throughput but a tiny risk of data loss if the primary crashes before the standby receives the WAL.

Synchronous : The primary waits for the standby to confirm receipt of the WAL before committing, guaranteeing zero data loss at the cost of higher latency.

Environment Preparation

Primary server IP: 192.168.1.10 Standby server IP: 192.168.1.20 PostgreSQL version: 12+ (example: 15)

Network: TCP port 5432 must be reachable between primary and standby.

Primary Server Configuration

Create a replication role with login rights:

CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'your_strong_password';

Adjust postgresql.conf (e.g., /var/lib/pgsql/15/data/postgresql.conf) to enable streaming replication:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024
archive_mode = on
archive_command = 'test ! -f /pg_archive/%f && cp %p /pg_archive/%f'

Allow the standby to connect for replication by adding a line to pg_hba.conf:

host    replication    repl_user    192.168.1.20/32    md5

Reload the configuration so the changes take effect:

pg_ctl reload

Standby Server Configuration

Stop the PostgreSQL service on the standby: systemctl stop postgresql-15 Empty the data directory to prepare for a fresh base backup: rm -rf /var/lib/pgsql/15/data/* Take a base backup from the primary. The -R flag automatically creates standby.signal and writes primary_conninfo into postgresql.auto.conf:

pg_basebackup -h 192.168.1.10 -U repl_user -D /var/lib/pgsql/15/data/ -Fp -Xs -P -R

If -R is omitted, perform the following manually:

# Create the signal file
touch /var/lib/pgsql/15/data/standby.signal
# Add connection info to postgresql.auto.conf
primary_conninfo = 'host=192.168.1.10 port=5432 user=repl_user password=your_strong_password'

Start the PostgreSQL service on the standby:

systemctl start postgresql-15

Verification

On the primary, query replication status:

SELECT application_name, client_addr, state, sync_state FROM pg_stat_replication;

Typical output: state = streaming indicates the standby is receiving WAL. sync_state = async or sync shows the configured replication mode.

On the standby, confirm it is in recovery (read‑only) mode: SELECT pg_is_in_recovery(); Result t means the standby is correctly operating as a replica.

Failover Procedure

If the primary becomes unavailable, promote the standby to become the new primary:

pg_ctl promote -D /var/lib/pgsql/15/data/   # or create a file named 'promote' in the data directory

After promotion, client applications must reconnect to the new primary, and any additional replicas should be re‑pointed to it.

Production Recommendations

Manual setup is suitable for learning or small test environments but does not scale well for production.

For automated high availability consider using one of the following tools:

Patroni : Provides automatic failover and cluster management.

Pgpool‑II : Offers connection pooling, load balancing, and failover capabilities.

repmgr : Simplifies replication setup and failover orchestration.

Best Practices

Use asynchronous replication as the default for most workloads because of its lower latency.

For critical data requiring zero loss, switch to synchronous replication and combine it with an HA tool such as Patroni or Pgpool‑II to automate failover.

Diagram