PostgreSQL: streaming replication

Configure PostgreSQL replication for high availability. One primary, one or more read-only replicas, manual or automatic failover. The standard solution to eliminate the database SPOF.

Introduction

PostgreSQL streaming replication:

• The primary continuously ships its WAL
• Replicas apply changes in real time
• Replicas accessible read-only (load-balance reads)
• On primary failure, you promote a replica
• Async (default) or sync (zero data loss) replication

This tutorial: 1 primary + 1 replica, async.

Prerequisites

• 2 Linux VPS Debian / Ubuntu
• PostgreSQL 16 already installed on both
• Private network between VPS (or VPN)
• IPs: 10.0.0.1 (primary), 10.0.0.2 (replica)

Step 1: Prepare primary

On 10.0.0.1, edit /etc/postgresql/16/main/postgresql.conf:

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

pg_hba.conf:

host    replication     replicator      10.0.0.2/32             scram-sha-256

sudo systemctl restart postgresql

Step 2: Create replication user

On primary:

sudo -u postgres psql

CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'strong-replic-pass';
\q

Step 3: Create replication slot

SELECT pg_create_physical_replication_slot('replica1_slot');

The slot ensures primary keeps WAL until replica has received it.

Step 4: Prepare replica

On 10.0.0.2, stop PG:

sudo systemctl stop postgresql

Wipe datadir:

sudo -u postgres rm -rf /var/lib/postgresql/16/main/*

Step 5: Clone from primary

sudo -u postgres pg_basebackup \
    -h 10.0.0.1 \
    -U replicator \
    -D /var/lib/postgresql/16/main \
    -Fp -Xs -P -R \
    -S replica1_slot

Options:

• -Fp: plain format
• -Xs: stream WAL during backup
• -P: show progress
• -R: auto-writes standby.signal and connection info
• -S replica1_slot: use created slot

Step 6: Verify replica config

/var/lib/postgresql/16/main/postgresql.auto.conf contains:

primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' host=10.0.0.1 port=5432 sslmode=prefer ...'
primary_slot_name = 'replica1_slot'

Check standby.signal:

sudo ls /var/lib/postgresql/16/main/standby.signal

Step 7: Start replica

sudo systemctl start postgresql
sudo systemctl status postgresql
sudo tail -f /var/log/postgresql/postgresql-16-main.log

Look for started streaming WAL from primary.

Step 8: Verify replication

On primary:

SELECT client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn 
FROM pg_stat_replication;

Expect state = streaming and sync_state = async.

On replica:

SELECT pg_is_in_recovery();

Must return t.

SELECT now() - pg_last_xact_replay_timestamp() AS lag;

Step 9: Test replication

On primary:

CREATE DATABASE test_replic;
\c test_replic
CREATE TABLE foo (id serial, msg text);
INSERT INTO foo (msg) VALUES ('Hello from primary');

On replica:

\c test_replic
SELECT * FROM foo;

Data present. Writes forbidden:

INSERT INTO foo (msg) VALUES ('Try'); 
-- ERROR: cannot execute INSERT in a read-only transaction

Step 10: Synchronous replication (optional)

For zero data loss:

Primary postgresql.conf:

synchronous_standby_names = 'replica1'
synchronous_commit = on

Replica postgresql.auto.conf:

primary_conninfo = '... application_name=replica1 ...'

SELECT pg_reload_conf();

⚠️ Sync mode: every COMMIT waits for replica. Replica down = primary blocks. Use at least 2 sync replicas to avoid SPOF.

Step 11: Manual failover

On replica:

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

Or:

SELECT pg_promote();

Replica becomes writeable. Point apps to the new IP.

Old primary, once recovered, must be reconfigured as replica (use pg_rewind instead of full clone).

Step 12: Automatic failover with Patroni

For auto failover:

• Patroni + etcd / Consul / ZooKeeper: leader election
• PgBouncer or HAProxy in front to redirect
• pg_auto_failover (Citus): simpler alternative

Advanced setup, worth a dedicated tutorial.

Troubleshooting

Replica doesn't sync

sudo tail -f /var/log/postgresql/postgresql-16-main.log

Look for could not connect to primary. Check:

• Network connectivity (firewall, routing)
• pg_hba.conf on primary
• Replicator password
• Slot exists on primary

"WAL receiver process exited"

Check wal_keep_size (primary) and max_slot_wal_keep_size. Too small = replica loses WAL.

Growing lag

Check:

• Replica disk I/O
• Replica CPU
• Network (latency, bandwidth)

SELECT now() - pg_last_xact_replay_timestamp() AS lag;

"FATAL: could not start WAL streaming"

SELECT * FROM pg_replication_slots;

If slot missing, recreate it.

Useful commands

sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"
sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"
sudo -u postgres psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS lag;"
sudo -u postgres psql -c "SELECT * FROM pg_replication_slots;"

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

sudo -u postgres pg_rewind \
    --target-pgdata=/var/lib/postgresql/16/main \
    --source-server="host=10.0.0.2 user=replicator password=..."

sudo -u postgres psql -c "SELECT pg_current_wal_lsn();"

Conclusion

Streaming replication gives you:

• Distributed reads (load balancing)
• Failover on failure
• Backups from replica (no primary load)

Limits:

• DDL (ALTER TABLE) blocks replica reads during apply
• No filtering (all changes replicated)

Going further:

• Combine with Patroni for auto failover
• For replication of subsets, use logical replication
• For multi-master, look at BDR (commercial) or Citus

Resources

• Official docs: https://www.postgresql.org/docs/current/warm-standby.html
• pg_basebackup: https://www.postgresql.org/docs/current/app-pgbasebackup.html
• Patroni: https://github.com/zalando/patroni
• pg_rewind: https://www.postgresql.org/docs/current/app-pgrewind.html