PostgreSQL 17 Streaming Replication Setup

Set up PostgreSQL 17 streaming replication between primary and replica servers with pg_auto_failover for automated high availability. This tutorial covers complete configuration from authentication to monitoring and maintenance procedures.

Prerequisites

At least 3 servers (primary, replica, monitor)
Minimum 4GB RAM per server
Network connectivity between servers
Basic PostgreSQL administration knowledge
Root or sudo access on all servers

What this solves

PostgreSQL streaming replication creates real-time copies of your database on multiple servers, providing high availability and disaster recovery. When combined with pg_auto_failover, your system automatically promotes replica servers to primary when failures occur, minimizing downtime and data loss.

Step-by-step configuration

Update system packages

Start by updating your package manager to ensure you get the latest PostgreSQL packages.

sudo apt update && sudo apt upgrade -y
sudo apt install -y wget ca-certificates

sudo dnf update -y
sudo dnf install -y wget ca-certificates

Install PostgreSQL 17 repository

Add the official PostgreSQL repository to get the latest version with streaming replication features.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install PostgreSQL 17 and pg_auto_failover

Install PostgreSQL server, client tools, and the pg_auto_failover extension for automated failover management.

sudo apt install -y postgresql-17 postgresql-client-17 postgresql-17-auto-failover
sudo systemctl enable postgresql
sudo systemctl start postgresql

sudo dnf install -y postgresql17-server postgresql17 postgresql17-auto-failover
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable postgresql-17
sudo systemctl start postgresql-17

Create replication user on primary server

Create a dedicated user for replication with the necessary privileges for streaming and monitoring.

sudo -u postgres createuser --replication --login replica_user
sudo -u postgres psql -c "ALTER USER replica_user PASSWORD 'SecureRepPass2024!';"

Configure primary server postgresql.conf

Enable WAL archiving, set replication parameters, and configure connection settings for streaming replication.

# Connection settings
listen_addresses = '*'
port = 5432
max_connections = 200

Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB

Archive settings
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/17/main/archive/%f && cp %p /var/lib/postgresql/17/main/archive/%f'

Logging
log_replication_commands = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Performance
shared_buffers = 256MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9

Configure primary server pg_hba.conf

Allow replication connections from replica servers with MD5 authentication for security.

# Allow local connections
local   all             postgres                                peer
local   all             all                                     md5

Allow host connections
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Allow replication connections
host    replication     replica_user    203.0.113.20/32         md5
host    replication     replica_user    203.0.113.21/32         md5

Allow application connections
host    all             all             203.0.113.0/24          md5

Create archive directory and restart primary

Create the WAL archive directory with correct permissions and restart PostgreSQL to apply configuration changes.

sudo mkdir -p /var/lib/postgresql/17/main/archive
sudo chown postgres:postgres /var/lib/postgresql/17/main/archive
sudo chmod 750 /var/lib/postgresql/17/main/archive
sudo systemctl restart postgresql

Note: Never use chmod 777 for database directories. The correct permissions (750) allow only the postgres user and group access while preventing unauthorized access from other system users.

Take base backup for replica server

Create a base backup of the primary database to initialize the replica server with current data.

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/17/main/*
sudo -u postgres pg_basebackup -h 203.0.113.10 -D /var/lib/postgresql/17/main -U replica_user -P -W -R
sudo chown -R postgres:postgres /var/lib/postgresql/17/main

Configure replica server postgresql.conf

Set up the replica server with hot standby mode and configure streaming replication parameters.

# Connection settings
listen_addresses = '*'
port = 5432
max_connections = 200

Standby settings
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

Archive recovery
restore_command = 'cp /var/lib/postgresql/17/main/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/17/main/archive %r'

Performance
shared_buffers = 256MB
wal_buffers = 16MB

Configure replica recovery settings

The pg_basebackup command with -R flag automatically creates the standby.signal file and primary connection settings.

sudo -u postgres ls -la /var/lib/postgresql/17/main/standby.signal
sudo -u postgres cat /var/lib/postgresql/17/main/postgresql.auto.conf

Start replica server

Start the replica PostgreSQL server which will automatically connect to the primary and begin streaming replication.

sudo systemctl start postgresql
sudo systemctl status postgresql

Install and configure pg_auto_failover monitor

Set up the pg_auto_failover monitor node that manages automatic failover decisions and cluster state.

sudo -u postgres pg_autoctl create monitor --hostname 203.0.113.30 --pgdata /var/lib/postgresql/monitor --pgport 5433
sudo -u postgres pg_autoctl -q show systemd --pgdata /var/lib/postgresql/monitor > /tmp/pgautofailover.service
sudo mv /tmp/pgautofailover.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable pgautofailover
sudo systemctl start pgautofailover

Register primary server with pg_auto_failover

sudo -u postgres pg_autoctl create postgres --hostname 203.0.113.10 --pgdata /var/lib/postgresql/17/main --monitor postgres://autoctl_node@203.0.113.30:5433/pg_auto_failover --pgport 5432
sudo -u postgres pg_autoctl -q show systemd --pgdata /var/lib/postgresql/17/main > /tmp/pgautofailover-primary.service
sudo mv /tmp/pgautofailover-primary.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable pgautofailover-primary
sudo systemctl start pgautofailover-primary

Register replica server with pg_auto_failover

sudo systemctl stop postgresql
sudo -u postgres pg_autoctl create postgres --hostname 203.0.113.20 --pgdata /var/lib/postgresql/17/main --monitor postgres://autoctl_node@203.0.113.30:5433/pg_auto_failover --pgport 5432
sudo -u postgres pg_autoctl -q show systemd --pgdata /var/lib/postgresql/17/main > /tmp/pgautofailover-replica.service
sudo mv /tmp/pgautofailover-replica.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable pgautofailover-replica
sudo systemctl start pgautofailover-replica

Configure firewall rules

Open the necessary ports for PostgreSQL replication and pg_auto_failover communication between servers.

sudo ufw allow from 203.0.113.0/24 to any port 5432
sudo ufw allow from 203.0.113.0/24 to any port 5433
sudo ufw reload

sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='203.0.113.0/24' port protocol='tcp' port='5432' accept"
sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='203.0.113.0/24' port protocol='tcp' port='5433' accept"
sudo firewall-cmd --reload

Verify your setup

Check replication status and cluster health with these verification commands.

# Check replication status
sudo -u postgres psql -c "SELECT client_addr, state, sync_state FROM pg_stat_replication;"

Check cluster status
sudo -u postgres pg_autoctl show state

Check monitor status
sudo -u postgres pg_autoctl show events

# Create test data on primary
sudo -u postgres psql -c "CREATE TABLE replication_test (id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT NOW());"
sudo -u postgres psql -c "INSERT INTO replication_test DEFAULT VALUES;"

Verify on replica (read-only)
sudo -u postgres psql -h 203.0.113.20 -c "SELECT * FROM replication_test;"

Monitoring and maintenance procedures

Set up monitoring queries

Create monitoring queries to track replication lag and cluster health. These can be integrated with your existing monitoring infrastructure.

-- Check replication lag
SELECT 
    client_addr,
    application_name,
    state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) AS lag
FROM pg_stat_replication;

-- Check cluster formation
SELECT * FROM pgautofailover.formation;

Configure automated backup verification

Set up automated testing of your replica to ensure it can be promoted successfully during maintenance windows.

#!/bin/bash
Test failover readiness
echo "Checking replica lag..."
sudo -u postgres psql -h 203.0.113.20 -c "SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"

Check if replica is ready for promotion
echo "Checking failover readiness..."
sudo -u postgres pg_autoctl show state --formation default --group 0

chmod +x /home/postgres/test_failover.sh
sudo chown postgres:postgres /home/postgres/test_failover.sh

Set up log rotation for PostgreSQL

Configure log rotation to prevent disk space issues and maintain performance monitoring data.

/var/log/postgresql/*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    postrotate
        /bin/systemctl reload postgresql >/dev/null 2>&1 || true
    endscript
}

Warning: Always test failover procedures in a non-production environment first. Automatic failover can result in data loss if not properly configured.

Common issues

Symptom	Cause	Fix
Replica server cannot connect	Authentication failure or firewall blocking	Check pg_hba.conf entries and firewall rules. Verify replica_user password.
Replication lag increasing	Network issues or high write load	Check network connectivity and consider increasing wal_buffers or wal_keep_size.
pg_auto_failover not responding	Monitor node unreachable	Check monitor node status: `sudo systemctl status pgautofailover`
Archive directory filling up	WAL files not being cleaned	Verify archive_cleanup_command is working: check /var/lib/postgresql/17/main/archive/
Failed to promote replica	Insufficient permissions or corrupted WAL	Check postgres user owns all files: `sudo chown -R postgres:postgres /var/lib/postgresql/`
Connection pooling errors	Too many connections during failover	Configure connection pooling with PgBouncer

Next steps

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Global variables
PRIMARY_IP=""
REPLICA_IPS=()
REPLICATION_PASSWORD="SecureRepPass2024!"
TOTAL_STEPS=8

# Usage message
usage() {
    echo "Usage: $0 --primary-ip <IP> --replica-ips <IP1,IP2,...> [--password <pass>]"
    echo "Example: $0 --primary-ip 203.0.113.10 --replica-ips 203.0.113.20,203.0.113.21"
    exit 1
}

# Parse arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        --primary-ip)
            PRIMARY_IP="$2"
            shift 2
            ;;
        --replica-ips)
            IFS=',' read -ra REPLICA_IPS <<< "$2"
            shift 2
            ;;
        --password)
            REPLICATION_PASSWORD="$2"
            shift 2
            ;;
        -h|--help)
            usage
            ;;
        *)
            echo -e "${RED}Unknown option: $1${NC}"
            usage
            ;;
    esac
done

if [[ -z "$PRIMARY_IP" ]] || [[ ${#REPLICA_IPS[@]} -eq 0 ]]; then
    echo -e "${RED}Error: Primary IP and replica IPs are required${NC}"
    usage
fi

# Cleanup function
cleanup() {
    echo -e "${RED}Installation failed. Check logs for details.${NC}"
    exit 1
}

trap cleanup ERR

# Check prerequisites
if [[ $EUID -ne 0 ]]; then
    echo -e "${RED}This script must be run as root${NC}"
    exit 1
fi

# Auto-detect distro
if [[ -f /etc/os-release ]]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian)
            PKG_MGR="apt"
            PKG_UPDATE="apt update && apt upgrade -y"
            PKG_INSTALL="apt install -y"
            PG_VERSION="17"
            PG_SERVICE="postgresql"
            PG_CONFIG_DIR="/etc/postgresql/17/main"
            PG_DATA_DIR="/var/lib/postgresql/17/main"
            ;;
        almalinux|rocky|centos|rhel|ol)
            PKG_MGR="dnf"
            PKG_UPDATE="dnf update -y"
            PKG_INSTALL="dnf install -y"
            PG_VERSION="17"
            PG_SERVICE="postgresql-17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_DATA_DIR="/var/lib/pgsql/17/data"
            ;;
        fedora)
            PKG_MGR="dnf"
            PKG_UPDATE="dnf update -y"
            PKG_INSTALL="dnf install -y"
            PG_VERSION="17"
            PG_SERVICE="postgresql-17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_DATA_DIR="/var/lib/pgsql/17/data"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_UPDATE="yum update -y"
            PKG_INSTALL="yum install -y"
            PG_VERSION="17"
            PG_SERVICE="postgresql-17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_DATA_DIR="/var/lib/pgsql/17/data"
            ;;
        *)
            echo -e "${RED}Unsupported distro: $ID${NC}"
            exit 1
            ;;
    esac
else
    echo -e "${RED}Cannot detect OS distribution${NC}"
    exit 1
fi

echo -e "${GREEN}[1/$TOTAL_STEPS] Updating system packages...${NC}"
$PKG_UPDATE
$PKG_INSTALL wget ca-certificates curl

echo -e "${GREEN}[2/$TOTAL_STEPS] Installing PostgreSQL 17 repository...${NC}"
if [[ "$PKG_MGR" == "apt" ]]; then
    sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
    apt update
else
    $PKG_INSTALL "https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm" || true
fi

echo -e "${GREEN}[3/$TOTAL_STEPS] Installing PostgreSQL 17 and pg_auto_failover...${NC}"
if [[ "$PKG_MGR" == "apt" ]]; then
    $PKG_INSTALL postgresql-17 postgresql-client-17 postgresql-17-auto-failover
    systemctl enable postgresql
    systemctl start postgresql
else
    $PKG_INSTALL postgresql17-server postgresql17 postgresql17-auto-failover
    /usr/pgsql-17/bin/postgresql-17-setup initdb
    systemctl enable postgresql-17
    systemctl start postgresql-17
fi

echo -e "${GREEN}[4/$TOTAL_STEPS] Creating replication user...${NC}"
sudo -u postgres createuser --replication --login replica_user || echo -e "${YELLOW}User may already exist${NC}"
sudo -u postgres psql -c "ALTER USER replica_user PASSWORD '$REPLICATION_PASSWORD';"

echo -e "${GREEN}[5/$TOTAL_STEPS] Configuring postgresql.conf...${NC}"
cat >> "$PG_CONFIG_DIR/postgresql.conf" << EOF

# Replication Configuration
listen_addresses = '*'
port = 5432
max_connections = 200

# WAL Settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB

# Archive Settings
archive_mode = on
archive_command = 'test ! -f $PG_DATA_DIR/archive/%f && cp %p $PG_DATA_DIR/archive/%f'

# Logging
log_replication_commands = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# Performance
shared_buffers = 256MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
EOF

echo -e "${GREEN}[6/$TOTAL_STEPS] Configuring pg_hba.conf...${NC}"
cat >> "$PG_CONFIG_DIR/pg_hba.conf" << EOF

# Replication connections
host    replication     replica_user    $PRIMARY_IP/32         md5
EOF

for replica_ip in "${REPLICA_IPS[@]}"; do
    echo "host    replication     replica_user    $replica_ip/32         md5" >> "$PG_CONFIG_DIR/pg_hba.conf"
    echo "host    all             all             $replica_ip/32         md5" >> "$PG_CONFIG_DIR/pg_hba.conf"
done

echo -e "${GREEN}[7/$TOTAL_STEPS] Creating archive directory and restarting PostgreSQL...${NC}"
mkdir -p "$PG_DATA_DIR/archive"
chown postgres:postgres "$PG_DATA_DIR/archive"
chmod 750 "$PG_DATA_DIR/archive"

# Configure firewall
if command -v firewall-cmd &> /dev/null; then
    firewall-cmd --permanent --add-port=5432/tcp
    firewall-cmd --reload
elif command -v ufw &> /dev/null; then
    ufw allow 5432/tcp
fi

systemctl restart "$PG_SERVICE"

echo -e "${GREEN}[8/$TOTAL_STEPS] Verifying installation...${NC}"
if systemctl is-active --quiet "$PG_SERVICE"; then
    echo -e "${GREEN}✓ PostgreSQL service is running${NC}"
else
    echo -e "${RED}✗ PostgreSQL service is not running${NC}"
    exit 1
fi

if sudo -u postgres psql -c "SELECT version();" | grep -q "PostgreSQL 17"; then
    echo -e "${GREEN}✓ PostgreSQL 17 is installed correctly${NC}"
else
    echo -e "${RED}✗ PostgreSQL 17 verification failed${NC}"
    exit 1
fi

if sudo -u postgres psql -c "SELECT usename FROM pg_user WHERE usename='replica_user';" | grep -q "replica_user"; then
    echo -e "${GREEN}✓ Replication user created successfully${NC}"
else
    echo -e "${RED}✗ Replication user creation failed${NC}"
    exit 1
fi

echo -e "${GREEN}PostgreSQL 17 streaming replication setup completed successfully!${NC}"
echo -e "${YELLOW}Next steps:${NC}"
echo "1. Configure replica servers using pg_basebackup"
echo "2. Set up pg_auto_failover monitor node"
echo "3. Register primary and replica nodes with the monitor"
echo "4. Test failover scenarios"

Review the script before running. Execute with: bash install.sh

#postgresql #streaming-replication #high-availability #automatic-failover #pg-auto-failover

Configure PostgreSQL 17 streaming replication for high availability with automatic failover

Prerequisites

What this solves

Step-by-step configuration

Update system packages

Install PostgreSQL 17 repository

Install PostgreSQL 17 and pg_auto_failover

Create replication user on primary server

Configure primary server postgresql.conf

Replication settings

Archive settings

Logging

Performance

Configure primary server pg_hba.conf

Allow host connections

Allow replication connections

Allow application connections

Create archive directory and restart primary

Take base backup for replica server

Configure replica server postgresql.conf

Standby settings

Archive recovery

Performance

Configure replica recovery settings

Start replica server

Install and configure pg_auto_failover monitor

Register primary server with pg_auto_failover

Register replica server with pg_auto_failover

Configure firewall rules

Verify your setup

Check cluster status

Check monitor status

Verify on replica (read-only)

Monitoring and maintenance procedures

Set up monitoring queries

Configure automated backup verification

Test failover readiness

Check if replica is ready for promotion

Set up log rotation for PostgreSQL

Common issues

Next steps

Related tutorials

Configure Cassandra SSL encryption and authentication with security hardening

Optimize Cassandra data modeling and query performance with advanced tuning and monitoring

Set up Cassandra backup automation with nodetool

Don't want to manage this yourself?