Linux VPS PostgreSQL Streaming Replication Tutorial: Complete Master-Replica Setup with Point-in-Time Recovery on Ubuntu 24.04 in 2026

Understanding PostgreSQL Streaming Replication on VPS Infrastructure

PostgreSQL streaming replication delivers real-time data sync between a master server and one or more replicas. Unlike file-based replication, streaming sends Write-Ahead Log (WAL) records directly to replicas. This cuts lag to milliseconds.

This guide walks through complete streaming replication setup on Ubuntu 24.04 VPS servers. You'll configure a primary server, establish replica connections, and implement point-in-time recovery for production environments.

For PostgreSQL hosting requirements, HostMyCode database hosting provides optimized VPS configurations with dedicated resources for high-availability setups.

Prerequisites and Server Requirements

You need two Ubuntu 24.04 VPS instances running identical PostgreSQL versions. The primary server handles writes while replicas serve read-only queries.

Minimum specs per server:

• 4GB RAM (8GB for production)
• 50GB SSD storage plus space for WAL archives
• Network connectivity between servers on port 5432
• Root or sudo access

Install PostgreSQL 16 on both servers:

sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16 -y
sudo systemctl enable postgresql
sudo systemctl start postgresql

Primary Server Configuration Setup

Enable PostgreSQL streaming replication by editing /etc/postgresql/16/main/postgresql.conf:

# Enable replication
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3

# Archive configuration
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/16/archive/%f'

# Connection settings
listen_addresses = '*'
port = 5432

Create the archive directory with proper permissions:

sudo mkdir -p /var/lib/postgresql/16/archive
sudo chown postgres:postgres /var/lib/postgresql/16/archive
sudo chmod 700 /var/lib/postgresql/16/archive

Restart PostgreSQL:

sudo systemctl restart postgresql

Replica User and Authentication Setup

Create a dedicated replication user on the primary:

sudo -u postgres psql
CREATE USER replica_user REPLICATION LOGIN PASSWORD 'secure_replica_password';
\q

Configure host-based authentication in /etc/postgresql/16/main/pg_hba.conf:

# Replication connections
host    replication     replica_user    10.0.0.0/8      md5
host    replication     replica_user    172.16.0.0/12   md5
host    replication     replica_user    192.168.0.0/16  md5

Replace IP ranges with your actual VPS network addresses. Reload configuration:

sudo systemctl reload postgresql

Creating the Initial Replica Backup

Stop PostgreSQL on the replica server and clear existing data:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

Use pg_basebackup to create an initial replica from the primary:

sudo -u postgres pg_basebackup -h PRIMARY_SERVER_IP -D /var/lib/postgresql/16/main -U replica_user -P -W -R

The -R flag creates standby.signal and adds replication settings to postgresql.auto.conf.

For VPS environments requiring reliable network connectivity, consider HostMyCode VPS hosting with guaranteed uptime and fast inter-server communication.

Replica Server Configuration and Startup

Configure the replica server's /etc/postgresql/16/main/postgresql.conf:

# Hot standby configuration
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s

# Connection settings
listen_addresses = '*'
port = 5432

Verify the auto-generated /var/lib/postgresql/16/main/postgresql.auto.conf contains:

primary_conninfo = 'user=replica_user host=PRIMARY_SERVER_IP port=5432 sslmode=prefer'
restore_command = 'cp /var/lib/postgresql/16/archive/%f %p'

Start PostgreSQL on the replica:

sudo systemctl start postgresql

Verifying Streaming Replication Status

Check replication status from the primary server:

sudo -u postgres psql -c "SELECT client_addr, state, sync_state FROM pg_stat_replication;"

Expected output shows connected replicas:

 client_addr |   state   | sync_state 
-------------+-----------+------------
 10.0.1.100  | streaming | async

Verify replica server status:

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

Test replication by creating a database on the primary:

sudo -u postgres createdb replication_test

The database should appear on the replica within seconds.

Point-in-Time Recovery Configuration

Point-in-Time Recovery (PITR) lets you restore the database to any specific moment. Configure WAL archiving on the primary by creating an archive script.

Create the script at /usr/local/bin/archive_wal.sh:

#!/bin/bash
WAL_FILE=$1
DEST_PATH=/var/lib/postgresql/16/archive/$2

# Copy with verification
cp "$WAL_FILE" "$DEST_PATH"
if [ $? -eq 0 ]; then
    echo "$(date): Archived $2" >> /var/log/postgresql/wal_archive.log
    exit 0
else
    echo "$(date): Failed to archive $2" >> /var/log/postgresql/wal_archive.log
    exit 1
fi

Make the script executable:

sudo chmod +x /usr/local/bin/archive_wal.sh
sudo chown postgres:postgres /usr/local/bin/archive_wal.sh

Update postgresql.conf archive command:

archive_command = '/usr/local/bin/archive_wal.sh %p %f'

Monitoring Replication Lag and Performance

Monitor replication lag with built-in PostgreSQL functions:

-- On primary server
SELECT client_addr, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Create a monitoring script at /usr/local/bin/check_replication.sh:

#!/bin/bash
LAG=$(sudo -u postgres psql -t -c "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));")
if (( $(echo "$LAG > 60" | bc -l) )); then
    echo "WARNING: Replication lag is ${LAG}s"
    exit 1
fi
echo "Replication lag: ${LAG}s"

Schedule monitoring with cron:

*/5 * * * * /usr/local/bin/check_replication.sh

Failover and Recovery Procedures

Manual failover promotes a replica to primary status. On the replica server:

sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

The replica becomes a standalone primary accepting write connections.

For point-in-time recovery, stop the replica and modify recovery settings in postgresql.conf:

restore_command = 'cp /var/lib/postgresql/16/archive/%f %p'
recovery_target_time = '2026-01-15 14:30:00'

Create recovery.signal and restart PostgreSQL:

sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
sudo systemctl restart postgresql

Frequently Asked Questions

How much replication lag is acceptable for production?

Streaming replication typically maintains lag under 100ms on stable networks. Monitor lag consistently and investigate if it regularly exceeds 5 seconds.

Can I run multiple replicas from one primary server?

Yes, PostgreSQL supports multiple replicas. Increase max_wal_senders and max_replication_slots values accordingly. Each replica adds network and CPU overhead to the primary.

What happens if the replica loses network connectivity?

The replica automatically reconnects when network access returns. PostgreSQL retains WAL files on the primary until all replicas acknowledge receipt.

How do I switch from asynchronous to synchronous replication?

Set synchronous_standby_names in postgresql.conf on the primary server to specify which replicas must confirm transactions before commit completion.

Can I perform backups from replica servers?

Yes, taking backups from replicas reduces primary server load. Use pg_dump or pg_basebackup against the replica's IP address for consistent backup operations.