PostgreSQL Streaming Replication: Master-Replica on VPS

PostgreSQL Streaming Replication lets you continuously copy data from a Primary (Master) server to one or more Standby (Replica) servers via WAL (Write-Ahead Log) streams. This guide walks you through a complete Master-Replica setup on two Ubuntu VPS instances, giving you High Availability and the ability to offload read queries to the Replica.

How Streaming Replication Works

PostgreSQL records every change in its WAL before applying it to the data files. Streaming Replication ships these WAL records to Replica servers in near real-time, keeping them in "Hot Standby" mode — ready to serve read-only queries at any time.

• Primary (Master) — Accepts all reads and writes; streams WAL to Replica
• Standby (Replica) — Applies incoming WAL; serves read-only queries
• Replication Lag — Typically under 1 second on a well-provisioned network

Prerequisites

• Two Ubuntu 22.04 VPS instances (Master IP: 10.0.0.1, Replica IP: 10.0.0.2)
• PostgreSQL 15 or 16 installed identically on both servers
• Network connectivity between the two instances
• Root or sudo access on both servers

Install PostgreSQL on Both Servers

# Run on both Master and Replica
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# Verify version
psql --version

# Enable and start the service
sudo systemctl enable postgresql
sudo systemctl start postgresql

Configure the Master Server

Edit postgresql.conf on Master

sudo nano /etc/postgresql/15/main/postgresql.conf

Add or update these settings:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_size = 256MB
hot_standby = on

Edit pg_hba.conf on Master

sudo nano /etc/postgresql/15/main/pg_hba.conf

Append this line (replace 10.0.0.2 with your Replica's actual IP):

host    replication     replicator      10.0.0.2/32     scram-sha-256

Create the Replication User

sudo -u postgres psql
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'StrongPass123!';
\q

Restart PostgreSQL on Master

sudo systemctl restart postgresql

Configure the Replica Server

Stop PostgreSQL and Clear the Data Directory

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

Take a Base Backup from the Master

# Run on the Replica (replace 10.0.0.1 with Master's IP)
sudo -u postgres pg_basebackup \
  -h 10.0.0.1 \
  -U replicator \
  -D /var/lib/postgresql/15/main \
  -P -Xs -R

# -P  = show progress
# -Xs = stream WAL during backup
# -R  = write standby.signal and recovery config automatically

Enter the password StrongPass123! when prompted.

Verify the Generated Files

# Confirm standby.signal exists
ls /var/lib/postgresql/15/main/standby.signal

# Check primary_conninfo in postgresql.auto.conf
sudo cat /var/lib/postgresql/15/main/postgresql.auto.conf

Start PostgreSQL on the Replica

sudo systemctl start postgresql
sudo systemctl status postgresql

Verify Replication Status

Check on the Master

sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

A healthy setup shows a row with state = streaming and sent_lsn close to replay_lsn.

Check on the Replica

sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"

Smoke Test

# Create a table on Master
sudo -u postgres psql
CREATE TABLE test_rep (id serial, msg text);
INSERT INTO test_rep (msg) VALUES ('Hello from Master');
\q

# Verify on Replica
sudo -u postgres psql
SELECT * FROM test_rep;
\q

Routing Read Queries to the Replica

In your application, direct read queries to the Replica's IP address to reduce load on the Master. Example in PHP:

// Write connection → Master
$dbWrite = new PDO('pgsql:host=10.0.0.1;dbname=myapp', 'appuser', 'pass');

// Read connection → Replica
$dbRead  = new PDO('pgsql:host=10.0.0.2;dbname=myapp', 'appuser', 'pass');

Never send write queries to the Replica — it is in read-only mode and will return an error.

Failing Over When the Master Goes Down

When the Primary server is no longer reachable, you can promote the Replica to become the new Primary with a single command:

# Promote the Replica to Primary (run on the Replica)
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/15/main

# Confirm it is now the Primary
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
# Expected result: f  (false = it is now a Primary)

After promoting, update the connection strings in all applications to point to the new Primary's IP address. If you want to bring the old Master back, reinstall it as a Replica of the new Primary.

Comparing PostgreSQL Replication Modes

PostgreSQL supports several replication modes, each suited to different requirements.

WAL Archiving and Point-in-Time Recovery

Replication is not a backup. If you accidentally delete data on the Primary, that change is immediately shipped to the Replica. WAL archiving gives you Point-in-Time Recovery (PITR) on top of replication.

# Add to postgresql.conf on the Primary to enable WAL archiving
archive_mode    = on
archive_command = 'cp %p /var/lib/postgresql/wal-archive/%f'

With WAL archives in place you can restore the database to any point in time — for example, one minute before an accidental DROP TABLE — by specifying recovery_target_time in postgresql.auto.conf.

• Configure WAL archiving on the Primary before any other step
• Test a full restore from archive at least once a month
• Keep at least 7 days of archives to cover your recovery window
• Consider pgBackRest or Barman for automated backup lifecycle management

Common Replication Problems and Fixes

The following issues appear most often when setting up Streaming Replication for the first time.

Replica cannot connect to the Primary

Check that pg_hba.conf on the Primary includes the Replica's IP with the correct auth method, and that port 5432 is open in the firewall between the two servers.

# Test connectivity from the Replica to the Primary
psql -h 10.0.0.1 -U replicator -d postgres

Replication lag keeps growing

High lag is usually caused by a network bottleneck or a Replica under heavy read load. Measure lag on the Primary:

SELECT client_addr, state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Disk full on Primary due to WAL accumulation

If the Replica is disconnected for an extended period, WAL files pile up on the Primary. Tune wal_keep_size appropriately, or use a Replication Slot so the Primary tracks exactly how far the Replica has consumed.