How to Set Up PostgreSQL Streaming Replication

What is PostgreSQL Streaming Replication

PostgreSQL streaming replication is a built-in feature that continuously ships WAL (Write-Ahead Log) records from a primary database server to one or more standby servers in near real-time. Unlike file-based log shipping, which transfers WAL segments only after they are fully written and archived, streaming replication pushes changes as they happen, keeping standby nodes current with minimal lag.

In a streaming replication setup, the primary server runs in wal_log mode and opens a direct network connection to each standby. The standby connects as a special replication client and streams the WAL stream over a TCP socket. A dedicated walreceiver process on the standby receives these records and applies them to its local copy of the database, maintaining a hot standby that can accept read-only queries while replication is active.

PostgreSQL 12 simplified the configuration significantly. Instead of a recovery.conf file, all standby parameters now live directly in postgresql.conf or can be set via ALTER SYSTEM. A simple empty file called standby.signal in the data directory tells PostgreSQL to start in standby mode.

Why Streaming Replication Matters

🚀 Deploy your AI agent in 10 minutes

Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.

Try it free →

Streaming replication addresses several critical needs in production database environments:

High Availability — If the primary server fails, a standby can be promoted to take over, minimizing downtime. With automated failover tooling like Patroni or repmgr, recovery can happen in seconds.
Read Scaling — Hot standby servers can serve read-only queries, offloading analytical workloads and report generation from the primary, which remains dedicated to write transactions.
Zero Data Loss (with synchronous replication) — Synchronous replication guarantees that committed transactions on the primary are confirmed only after at least one standby has persisted the WAL, ensuring no transaction is lost in a failure.
Geographic Distribution — Standby servers can be placed in different data centers or cloud regions, providing disaster recovery and reducing latency for users in distant locations.
Backup Offloading — Taking backups from a standby avoids putting additional I/O load on the primary, keeping production performance predictable.

Architecture Overview

The streaming replication architecture involves these key components:

WAL Sender (walsender) — A process on the primary that streams WAL records to connected standbys. There is one walsender process per standby.
WAL Receiver (walreceiver) — A process on each standby that connects to the primary, receives the WAL stream, and writes it to local WAL segments.
Startup Process (standby) — Applies received WAL records to the standby's data files, keeping the database consistent with the primary.
Replication Slots (optional but recommended) — Named slots on the primary that reserve WAL segments until all standbys have consumed them, preventing premature WAL cleanup that would break replication.

Replication can operate in asynchronous mode (default) where the primary does not wait for standby confirmation, or synchronous mode where the primary waits until the WAL is flushed to disk on at least one standby before returning commit confirmation to the client.

Prerequisites

Before setting up streaming replication, ensure the following:

Two servers running the same major version of PostgreSQL (e.g., both 16.x). Minor version differences are acceptable but not recommended for production.
The primary server has a functioning PostgreSQL instance with data you want to replicate.
Network connectivity between primary and standby on the PostgreSQL port (default 5432), plus the standby must be able to reach the primary.
Operating system user accounts (typically postgres) exist on both machines with appropriate permissions.
Firewall rules allow TCP traffic on port 5432 (or your custom port) between the hosts.
Both servers have similar hardware architecture (same CPU endianness), as WAL records are binary and architecture-dependent.

Step-by-Step Setup Guide

1. Configure the Primary Server

Begin by editing postgresql.conf on the primary server. The essential parameters for streaming replication are:

# postgresql.conf on primary
listen_addresses = '*'
wal_level = replica           # or 'logical' if you also need logical replication
max_wal_senders = 5           # at least one per planned standby, plus extras
wal_keep_size = 1024          # MB of WAL to retain on disk (or use slots)
max_replication_slots = 5     # at least one per standby if using slots
hot_standby = on              # allows read-only queries on standbys
archive_mode = on             # optional but recommended for resilience
archive_command = '/bin/true' # minimal; replace with real archiver in production

These settings require a restart of the primary. Apply them and restart:

pg_ctl restart -D /var/lib/postgresql/data

Next, configure client authentication for replication connections. Add an entry to pg_hba.conf on the primary:

# pg_hba.conf on primary — add this line
# TYPE  DATABASE        USER             ADDRESS          METHOD
host    replication     repl_user        192.168.1.0/24   scram-sha-256

Replace 192.168.1.0/24 with the actual CIDR range of your standby servers. The replication pseudo-database governs replication connections specifically. Reload the configuration after this change:

pg_ctl reload -D /var/lib/postgresql/data
# or via SQL: SELECT pg_reload_conf();

2. Create the Replication User

On the primary, create a dedicated user account for replication. This user must have the REPLICATION privilege:

-- Run on the primary as superuser
CREATE USER repl_user WITH REPLICATION
  LOGIN
  ENCRYPTED PASSWORD 'strong_password_here';

Verify the user was created correctly:

SELECT usename, usereplication FROM pg_user WHERE usename = 'repl_user';
-- Expected: usename='repl_user', usereplication=true

3. Create a Replication Slot (Recommended)

Replication slots prevent the primary from removing WAL segments before the standby has consumed them. Create one slot per standby:

-- Run on the primary
SELECT pg_create_physical_replication_slot('standby1_slot');
-- Verify slots exist:
SELECT slot_name, slot_type, active FROM pg_replication_slots;

Without slots, you must configure wal_keep_size generously enough to retain WAL during network outages or standby maintenance, which is harder to predict.

4. Prepare the Standby Server

On the standby machine, ensure PostgreSQL is installed but do not start the server yet. The standby's data directory must be initialized from a base backup of the primary. The cleanest method is pg_basebackup, which copies the entire data directory while connecting as a replication client.

First, ensure the standby's data directory is empty (or does not exist yet):

# On standby machine
rm -rf /var/lib/postgresql/data  # Be careful! Only if empty/non-existent
# Or ensure the target directory is empty

Run pg_basebackup from the standby machine (or copy the result over), pointing it at the primary:

# Execute ON THE STANDBY machine
pg_basebackup \
  -h 192.168.1.100 \           # primary host IP
  -p 5432 \                    # primary port
  -U repl_user \               # replication user
  -D /var/lib/postgresql/data \  # standby data directory
  -P \                         # show progress
  -v \                         # verbose output
  -R \                         # creates standby.signal AND adds connection info
  -S standby1_slot \           # use the replication slot we created
  --no-password                # if using a .pgpass file, otherwise omit

The -R flag is critical. It automatically:

Creates the standby.signal file in the data directory (telling PostgreSQL to start as a standby)
Writes primary_conninfo into postgresql.conf (or generates a minimal config with connection details)

If prompted for a password, either supply it interactively or create a .pgpass file on the standby machine:

# ~/.pgpass on standby machine
192.168.1.100:5432:replication:repl_user:strong_password_here
chmod 600 ~/.pgpass

5. Verify and Adjust Standby Configuration

After pg_basebackup completes, examine the standby's postgresql.conf. The -R flag should have added something like:

# Auto-generated by pg_basebackup -R in postgresql.conf on standby
primary_conninfo = 'host=192.168.1.100 port=5432 user=repl_user password=strong_password_here'
# For replication slot:
primary_slot_name = 'standby1_slot'

Ensure these additional parameters are set in the standby's postgresql.conf:

# Add or verify in standby's postgresql.conf
hot_standby = on                     # allow read-only queries
hot_standby_feedback = on            # informs primary about queries on standby
# Optional but recommended:
wal_retrieve_retry_interval = '5s'   # retry connection every 5 seconds

Confirm that standby.signal exists in the data directory:

ls -la /var/lib/postgresql/data/standby.signal
# Should show the file exists (it can be empty)

If standby.signal is missing, create it manually (this tells PostgreSQL to enter standby mode):

touch /var/lib/postgresql/data/standby.signal

6. Start the Standby Server

Start PostgreSQL on the standby:

pg_ctl start -D /var/lib/postgresql/data

Check the standby logs to confirm successful connection and replication:

tail -f /var/lib/postgresql/data/log/postgresql-*.log
# Look for: "started streaming WAL from primary"
# and: "consistent recovery state reached"
# and: "database system is ready to accept read-only connections"

7. Verify Replication Status

On the primary, run these queries to confirm replication is working:

-- Check connected standby clients
SELECT application_name, client_addr, state, 
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag
FROM pg_stat_replication;

-- Check replication slots
SELECT slot_name, slot_type, active, 
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS pending_wal
FROM pg_replication_slots;

On the standby, verify it is in recovery mode:

SELECT pg_is_in_recovery();  -- should return true (t) on standby
-- Check replication status from standby perspective
SELECT status, last_msg_receipt_time, 
       pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS replay_lag
FROM pg_stat_wal_receiver;

Create a test table on the primary and confirm it appears on the standby:

-- On primary
CREATE TABLE replication_test (id int, created_at timestamptz DEFAULT now());
INSERT INTO replication_test (id) VALUES (1), (2), (3);

-- On standby (after a moment)
SELECT * FROM replication_test;  -- Should return the three rows

Synchronous Replication Setup

For zero data loss guarantees, configure synchronous replication. This ensures the primary waits for WAL flush confirmation on at least one standby before acknowledging a commit to the client.

On the primary, set:

# postgresql.conf on primary
synchronous_standby_names = 'standby1'   # application_name of the standby
# Or for multiple standbys with quorum:
# synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'

Reload the primary configuration:

SELECT pg_reload_conf();

The standby's application_name is set in primary_conninfo. Ensure it matches:

# standby's postgresql.conf
primary_conninfo = 'host=192.168.1.100 port=5432 user=repl_user password=... application_name=standby1'

Verify synchronous status on the primary:

SELECT application_name, sync_state, sync_priority 
FROM pg_stat_replication;
-- sync_state should be 'sync' for synchronous standby

Monitoring Streaming Replication

Continuous monitoring is essential. Key queries for a monitoring dashboard include:

-- Replication lag in bytes and human-readable
SELECT application_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;

-- Standby last message receipt (check for stale connections)
SELECT application_name, 
       EXTRACT(epoch FROM (now() - last_msg_receipt_time)) AS seconds_since_last_msg
FROM pg_stat_replication;

-- WAL generation rate on primary (bytes per second approximate)
SELECT pg_size_pretty(pg_wal_lsn_diff(
    pg_current_wal_lsn(), 
    pg_current_wal_lsn() - '1 second'::interval * pg_wal_lsn_diff_per_second()
)) AS wal_rate_per_sec FROM generate_series(1,1) t;

-- Check if any replication slots are dangerously behind
SELECT slot_name, 
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS behind
FROM pg_replication_slots
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1024*1024*1024; -- >1GB

Integrate these queries into tools like Nagios, Prometheus with postgres_exporter, or Datadog for alerting on replication lag exceeding thresholds.

Failover: Promoting a Standby

When the primary fails or needs maintenance, promote a standby to become the new primary:

# On the standby you want to promote
pg_ctl promote -D /var/lib/postgresql/data

# Or via SQL (PostgreSQL 12+):
SELECT pg_promote();

The standby will apply any remaining pending WAL, then open for read-write operations. The standby.signal file is automatically removed. After promotion, reconfigure any remaining standbys to replicate from the new primary, or use a tool like Patroni to automate this.

Best Practices

Always use replication slots — They prevent WAL removal before standbys consume it, eliminating a major cause of replication breakage during network interruptions.
Monitor replication lag continuously — Alert when lag exceeds acceptable thresholds (e.g., > 100 MB or > 30 seconds). Lag indicates network issues, heavy write loads, or a struggling standby.
Use synchronous replication for critical data — If data loss is unacceptable, configure synchronous_standby_names. Be aware this adds latency to commits, as the primary must wait for standby acknowledgment.
Set hot_standby_feedback = on — This informs the primary about queries running on the standby, preventing vacuum operations from removing rows still needed by standby queries.
Test failover procedures regularly — Promote a standby in a staging environment, verify applications reconnect, and document the exact steps. An untested failover plan is unreliable.
Secure replication traffic — Use SSL for replication connections by setting ssl = on and configuring certificates, or tunnel replication over SSH/VPN in untrusted networks.
Match hardware and configuration — Standby servers should have similar I/O capacity and PostgreSQL configuration (shared_buffers, effective_cache_size) to keep up with the primary's write load.
Archive WAL in addition to streaming — Set archive_command to ship WAL segments to a safe location. This provides a fallback if a standby falls too far behind and needs to catch up via archived WAL.
Use connection pooling aware of read-only routing — Tools like PgBouncer in transaction mode or HAProxy can route read queries to standbys and write queries to the primary automatically.
Consider automated failover management — For production, use Patroni (with etcd/Consul), repmgr, or cloud-managed solutions. Manual promotion is error-prone and slow in critical incidents.

Common Troubleshooting

If replication does not start, check these common issues:

-- On standby, check connection attempts
SELECT * FROM pg_stat_wal_receiver;  -- status should be 'streaming'

-- On primary, check for failed authentication
SELECT * FROM pg_stat_replication;   -- empty? check pg_hba.conf and firewall

-- Ensure replication user can authenticate
-- From standby machine, test manually:
psql -h 192.168.1.100 -p 5432 -U repl_user -d "dbname=replication" -c "IDENTIFY SYSTEM"
# Should return systemid, timeline, xlogpos, etc.

-- Check standby logs for errors like:
-- "FATAL:  could not connect to the primary server"
-- "FATAL:  replication slot 'standby1_slot' does not exist"

If the standby falls behind and the replication slot prevents WAL cleanup, you may need to recreate the standby from a fresh base backup. This is why monitoring lag is critical — catching it early allows intervention before WAL accumulates excessively.

Conclusion

PostgreSQL streaming replication provides a robust, built-in mechanism for high availability, read scaling, and disaster recovery. By configuring the primary with appropriate wal_level and max_wal_senders, creating a dedicated replication user, and using pg_basebackup with the -R flag to bootstrap a standby, you establish a continuously updated hot standby with minimal administrative overhead. Replication slots safeguard against WAL loss, while synchronous replication offers transactional durability guarantees for the most critical workloads. Combined with vigilant monitoring, regular failover testing, and automated management tools like Patroni, streaming replication forms the foundation of a resilient PostgreSQL deployment ready for production demands.