Set Up PostgreSQL Replication on VPS

June 2026 · 9 min read

PostgreSQL Streaming Replication sends Write-Ahead Log (WAL) records from a primary server to standby servers in real-time, allowing standby nodes to be only a few milliseconds behind the primary. If the primary fails, you can quickly promote a standby to replace it. This is the approach most production databases use to minimize RTO (Recovery Time Objective) and RPO (Recovery Point Objective).

Architecture

This article uses a 2-node setup:

• Primary (192.168.1.10): Accepts read/write operations
• Standby (192.168.1.11): Syncs from primary, serves read-only queries

Install PostgreSQL on Both Nodes

sudo apt update
sudo apt install postgresql postgresql-contrib -y

# Check version
psql --version
sudo systemctl status postgresql

Configure the Primary Server

sudo -u postgres psql

-- Create replication user
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'ReplicaPassword123';
\q

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

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on

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

# Add at the end of file
host    replication     replicator      192.168.1.11/32     md5

sudo systemctl restart postgresql

Configure the Standby Server

# Stop PostgreSQL on standby first
sudo systemctl stop postgresql

# Remove old data directory
sudo rm -rf /var/lib/postgresql/16/main/*

# Clone from primary
sudo -u postgres pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R
# -R automatically creates standby.signal and primary_conninfo

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

listen_addresses = '*'
hot_standby = on
# Allow read queries on standby
hot_standby_feedback = on

sudo systemctl start postgresql

Test Read Queries on Standby

# Standby can serve read-only queries
sudo -u postgres psql -c "SELECT now(), pg_is_in_recovery();"
# pg_is_in_recovery = true indicates this is a standby

# Test: Create a table on primary and verify it appears on standby
# On primary:
sudo -u postgres psql -c "CREATE TABLE test_repl (id serial, msg text);"
sudo -u postgres psql -c "INSERT INTO test_repl(msg) VALUES ('hello from primary');"
# On standby:
sudo -u postgres psql -c "SELECT * FROM test_repl;"

Verify PostgreSQL Replication is Working Correctly

# Check replication status from primary
sudo -u postgres psql -c "SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;"

# Check replication lag (should be minimal on a good network)
sudo -u postgres psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;"

# Verify standby mode
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

Common Issues & Troubleshooting

Error: "could not connect to the primary server"

Check the primary_conninfo and pg_hba.conf:

cat /var/lib/postgresql/16/main/postgresql.auto.conf
# Verify primary_conninfo has correct IP and password

# Check if pg_hba.conf on primary allows the standby IP
grep replicator /etc/postgresql/16/main/pg_hba.conf
sudo systemctl reload postgresql  # on primary

Standby stops syncing (replication slot hanging)

If you use replication slots and the standby goes down for a long time, wal_keep_size may not be sufficient:

# Check hanging slots
sudo -u postgres psql -c "SELECT * FROM pg_replication_slots;"
# If the standby is permanently offline, drop the slot
sudo -u postgres psql -c "SELECT pg_drop_replication_slot('slot_name');"

Standby is significantly lagging (high replication lag)

Increase wal_sender_timeout and check network connectivity:

sudo nano /etc/postgresql/16/main/postgresql.conf
wal_sender_timeout = 60s
# Check bandwidth between primary and standby
iperf3 -s  # on standby
iperf3 -c STANDBY-IP  # on primary

Promote standby to primary (manual failover)

# On standby when primary has failed
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main
# Verify promotion succeeded
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
# Should return false

Frequently Asked Questions

What is the difference between Streaming Replication and Logical Replication?

Streaming Replication copies the entire WAL (physical copy), so the standby must be the same PostgreSQL version. Logical Replication copies only specified tables, supports cross-version and cross-platform replication, and is suitable for selective sync or zero-downtime migrations.

Can standby servers handle read queries?

Yes, if you set hot_standby = on. Applications can route read queries to the standby to reduce load on the primary. However, standby servers cannot accept write queries—all writes must go to the primary.

Should I use Patroni?

Patroni enables automatic failover without manually running pg_ctl promote. It is ideal for production environments requiring zero-downtime failover. Patroni uses etcd or Consul as a distributed store for leader election. For a basic 2-node setup, manual failover is sufficient.