PostgreSQL Streaming Replication

PostgreSQL streaming replication lets a standby server replay WAL (Write-Ahead Log) records from a primary in near real-time, giving you a hot standby you can fail over to with minimal data loss. This guide walks through a complete two-node setup: one primary and one standby, using PostgreSQL 16 on current LTS releases. The same steps apply to PostgreSQL 14 and 15 with minor version-number substitutions.

Architecture Overview

The primary continuously ships WAL segments to the standby via a dedicated replication connection. The standby runs in recovery mode, replaying those WAL records and optionally serving read-only queries. Two modes exist:

• Asynchronous replication (default): The primary commits before confirming the standby received the WAL. Fastest, but a crash can lose the last few transactions.
• Synchronous replication: The primary waits for the standby to confirm WAL receipt (or apply, depending on synchronous_commit level) before returning to the client. Zero data loss, but adds round-trip latency to every write.

In this guide, both nodes are assumed to be on the same network. Use the real IP addresses for your environment. We will use 192.168.1.10 as the primary and 192.168.1.20 as the standby throughout.

Prerequisites and Installation

Install PostgreSQL 16 on both nodes before starting. Use the PostgreSQL Global Development Group (PGDG) repository for the freshest packages.

Debian / Ubuntu

sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
> /etc/apt/sources.list.d/pgdg.list'
sudo apt update && sudo apt install -y postgresql-16

Fedora / RHEL 9 / Rocky 9

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server postgresql16-contrib
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

Arch Linux

sudo pacman -S postgresql
sudo -u postgres initdb --locale=en_US.UTF-8 -D /var/lib/postgres/data

Start and enable PostgreSQL on the primary only for now:

# Debian/Ubuntu
sudo systemctl enable --now postgresql@16-main

# Fedora/RHEL/Rocky
sudo systemctl enable --now postgresql-16

# Arch
sudo systemctl enable --now postgresql

Step 1 — Create the Replication Role

On the primary, create a dedicated PostgreSQL role for replication. Never use the postgres superuser for this.

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

Step 2 — Configure the Primary

Edit postgresql.conf on the primary. The file lives at /etc/postgresql/16/main/postgresql.conf on Debian/Ubuntu, /var/lib/pgsql/16/data/postgresql.conf on RHEL/Rocky, and /var/lib/postgres/data/postgresql.conf on Arch.

# Key settings — append or edit these lines
listen_addresses = '*'          # accept connections from the standby
wal_level = replica             # minimum for streaming replication
max_wal_senders = 5             # concurrent WAL sender processes
wal_keep_size = 256             # MB of WAL to retain for lagging standbys
hot_standby = on                # allows read queries on the standby
archive_mode = off              # enable and configure if you also want PITR

For synchronous replication, add the standby's application name to synchronous_standby_names. Skip this for async:

synchronous_standby_names = 'standby1'
synchronous_commit = remote_apply   # strongest guarantee; use 'on' for slightly less

Allow the Standby in pg_hba.conf

Add a replication entry to pg_hba.conf (same directory as postgresql.conf):

# TYPE  DATABASE        USER         ADDRESS              METHOD
host    replication     replicator   192.168.1.20/32      scram-sha-256

Reload the primary to apply changes:

sudo systemctl reload postgresql@16-main   # Debian/Ubuntu
sudo systemctl reload postgresql-16        # RHEL/Rocky/Fedora
sudo systemctl reload postgresql           # Arch

Step 3 — Open the Firewall on the Primary

ufw (Ubuntu/Debian)

sudo ufw allow from 192.168.1.20 to any port 5432

firewalld (Fedora / RHEL / Rocky)

sudo firewall-cmd --permanent --add-rich-rule=\
'rule family=ipv4 source address=192.168.1.20 port port=5432 protocol=tcp accept'
sudo firewall-cmd --reload

Step 4 — Bootstrap the Standby with pg_basebackup

On the standby, stop PostgreSQL if it started automatically, then wipe the data directory and replace it with a base backup from the primary. This is destructive — any existing data on the standby is lost.

sudo systemctl stop postgresql@16-main   # or the distro-appropriate unit
sudo rm -rf /var/lib/postgresql/16/main/*   # Debian/Ubuntu path; adjust for your distro

sudo -u postgres pg_basebackup \
  -h 192.168.1.10 \
  -U replicator \
  -D /var/lib/postgresql/16/main \
  -P -Xs -R
# -Xs  : stream WAL during the backup
# -R   : write standby.signal and connection info into postgresql.auto.conf

The -R flag creates the standby.signal file and adds a primary_conninfo entry automatically. Verify both exist:

ls /var/lib/postgresql/16/main/standby.signal
grep primary_conninfo /var/lib/postgresql/16/main/postgresql.auto.conf

For synchronous replication, set the application name so the primary can identify this standby:

sudo -u postgres sh -c "echo \"primary_conninfo = 'host=192.168.1.10 port=5432 \
user=replicator password=StrongPassw0rd! application_name=standby1'\" \
>> /var/lib/postgresql/16/main/postgresql.auto.conf"

Step 5 — Start the Standby

sudo systemctl start postgresql@16-main   # Debian/Ubuntu
sudo systemctl start postgresql-16        # RHEL/Rocky/Fedora
sudo systemctl start postgresql           # Arch

Step 6 — Verify Replication

On the primary, check the pg_stat_replication view. You should see one row for the standby.

sudo -u postgres psql -c \
  "SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state \
   FROM pg_stat_replication;"

Expected output (values will differ):

   client_addr  |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | sync_state
----------------+-----------+-----------+-----------+-----------+------------+------------
 192.168.1.20   | streaming | 0/5000060 | 0/5000060 | 0/5000060 | 0/5000060  | async

For sync replication the sync_state column shows sync instead of async. On the standby, confirm it is in recovery:

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

Failover

PostgreSQL does not ship an automatic failover manager — use repmgr or Patroni for production. For a manual failover:

1. Confirm the primary is genuinely down (avoid split-brain).
2. On the standby, promote it to primary:

sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main
# Or via SQL on the standby:
sudo -u postgres psql -c "SELECT pg_promote();"

After promotion, standby.signal is removed automatically and the node accepts writes. Update your application connection string to point to the new primary. The old primary, once recovered, must be re-basebackuped as a new standby — it cannot simply rejoin without a fresh base backup unless you use rewind (pg_rewind).

Using pg_rewind to Rejoin the Old Primary

pg_rewind rewinds the old primary's data directory to a point it diverged from the new primary, avoiding a full base backup. Requires wal_log_hints = on or data checksums enabled on both nodes before the failure.

sudo -u postgres pg_rewind \
  --target-pgdata=/var/lib/postgresql/16/main \
  --source-server="host=192.168.1.20 user=postgres dbname=postgres"

Then add standby.signal and update primary_conninfo to point to the new primary (192.168.1.20), and restart PostgreSQL.

Troubleshooting

• Standby stuck in "startup" state: Check pg_log on the standby for authentication errors. Verify pg_hba.conf on the primary allows the standby IP under the replication database entry.
• WAL sender count exhausted: Increase max_wal_senders on the primary and reload. Each standby and each pg_basebackup session consumes one slot.
• Replication lag growing: The standby cannot keep up with WAL generation. Investigate I/O performance on the standby. Consider replication slots (pg_create_physical_replication_slot) to prevent WAL being discarded before the standby consumes it — but monitor slot lag closely to avoid disk exhaustion on the primary.
• synchronous_commit and client timeouts: If your standby goes offline under sync replication, writes on the primary will block indefinitely until the standby reconnects or you set synchronous_standby_names = ''. Plan your HA topology accordingly.
• pg_basebackup fails with "could not connect": Verify the firewall rule, confirm listen_addresses is not localhost, and test connectivity with psql -h 192.168.1.10 -U replicator -d replication from the standby.