Streaming Replication Setup

Advanced20 min

Configure PostgreSQL streaming replication with a primary and standby server for high availability and read scaling.

Prerequisites

-Two PostgreSQL instances (primary and standby)
-Network connectivity between instances
-PostgreSQL 14+

Steps

Create a replication role on the primary

Create a dedicated user with replication privileges on the primary server.

$ psql -U postgres -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl_secure_pass';"

Configure primary for replication

Set the required WAL and replication parameters on the primary.

$ psql -U postgres -c "ALTER SYSTEM SET wal_level = 'replica'; ALTER SYSTEM SET max_wal_senders = 5; ALTER SYSTEM SET wal_keep_size = '1GB';"

These changes require a PostgreSQL restart to take effect.

Add replication entry to pg_hba.conf

Allow the standby server to connect for replication.

$ echo 'host replication replicator 10.0.0.0/24 scram-sha-256' | sudo tee -a /etc/postgresql/16/main/pg_hba.conf

Replace 10.0.0.0/24 with the actual IP range of your standby server. Restart PostgreSQL after this change.

Create a base backup on the standby

Clone the primary database to the standby server using pg_basebackup.

$ pg_basebackup -h primary-host -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R

The -R flag creates standby.signal and sets primary_conninfo in postgresql.auto.conf automatically.

Start the standby and verify replication

Start PostgreSQL on the standby and confirm it is receiving WAL from the primary.

$ sudo systemctl start postgresql && psql -U postgres -c "SELECT pg_is_in_recovery();"

Check replication status on the primary

Monitor replication lag and standby connection status from the primary.

$ psql -U postgres -c "SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, replay_lag FROM pg_stat_replication;"

Full Script

full-script.sh

#!/bin/bash
# PostgreSQL Streaming Replication Status Check
set -euo pipefail

USER="postgres"

echo "=== Server Role ==="
IS_STANDBY=$(psql -U "$USER" -tAc "SELECT pg_is_in_recovery();")
if [ "$IS_STANDBY" = "t" ]; then
  echo "This server is a STANDBY (replica)"
  echo ""
  echo "=== Replication status (standby) ==="
  psql -U "$USER" -c "
  SELECT pg_last_wal_receive_lsn() AS received_lsn,
         pg_last_wal_replay_lsn() AS replayed_lsn,
         pg_last_xact_replay_timestamp() AS last_replay_time,
         now() - pg_last_xact_replay_timestamp() AS replay_lag;
  "
else
  echo "This server is the PRIMARY"
  echo ""
  echo "=== Connected standbys ==="
  psql -U "$USER" -c "
  SELECT client_addr,
         state,
         sent_lsn,
         write_lsn,
         flush_lsn,
         replay_lsn,
         replay_lag
  FROM pg_stat_replication;
  "
  echo ""
  echo "=== WAL settings ==="
  psql -U "$USER" -c "
  SELECT name, setting
  FROM pg_settings
  WHERE name IN ('wal_level', 'max_wal_senders', 'wal_keep_size', 'synchronous_commit');
  "
fi

echo ""
echo "=== Replication slots ==="
psql -U "$USER" -c "
SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots;
"

FAQ

Discussion

Loading comments...

#!/bin/bash # PostgreSQL Streaming Replication Status Check set -euo pipefail USER="postgres" echo "=== Server Role ===" IS_STANDBY=$(psql -U "$USER" -tAc "SELECT pg_is_in_recovery();") if [ "$IS_STANDBY" = "t" ]; then echo "This server is a STANDBY (replica)" echo "" echo "=== Replication status (standby) ===" psql -U "$USER" -c " SELECT pg_last_wal_receive_lsn() AS received_lsn, pg_last_wal_replay_lsn() AS replayed_lsn, pg_last_xact_replay_timestamp() AS last_replay_time, now() - pg_last_xact_replay_timestamp() AS replay_lag; " else echo "This server is the PRIMARY" echo "" echo "=== Connected standbys ===" psql -U "$USER" -c " SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, replay_lag FROM pg_stat_replication; " echo "" echo "=== WAL settings ===" psql -U "$USER" -c " SELECT name, setting FROM pg_settings WHERE name IN ('wal_level', 'max_wal_senders', 'wal_keep_size', 'synchronous_commit'); " fi echo "" echo "=== Replication slots ===" psql -U "$USER" -c " SELECT slot_name, slot_type, active, restart_lsn FROM pg_replication_slots; "