Streaming replication keeps one or more standby servers continuously updated with the primary’s write-ahead log (WAL). Standbys can serve read-only queries and can be promoted if the primary fails. It’s built into PostgreSQL and is the foundation of most PostgreSQL high-availability setups.

This guide sets up one primary (10.0.0.21) and one standby (10.0.0.22) on Ubuntu/Debian with the same PostgreSQL major version. Use a private network or VPN between the servers.

1. Primary: settings

In postgresql.conf (Debian/Ubuntu: /etc/postgresql/17/main/):

listen_addresses = 'localhost,10.0.0.21'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB
hot_standby = on

wal_level = replica is the default in current versions. Restart:

sudo systemctl restart postgresql

2. Primary: replication user and access

sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'a-long-random-password';"

pg_hba.conf — allow the standby to connect for replication:

host  replication  replicator  10.0.0.22/32  scram-sha-256
sudo systemctl reload postgresql
sudo ufw allow from 10.0.0.22 to any port 5432 proto tcp

3. Primary: a replication slot

A slot makes the primary keep WAL until the standby has received it — the standby can fall behind without losing its place:

sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('standby1');"

The trade-off: if a standby disappears for good, its slot keeps WAL forever and fills the disk. Drop unused slots (pg_drop_replication_slot) and monitor. max_slot_wal_keep_size caps how much WAL a slot can retain.

4. Standby: clone the primary

On the standby, stop PostgreSQL and empty its data directory (it’s a fresh install — don’t do this on a server with data you need):

sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/17/main/*
sudo -u postgres pg_basebackup -h 10.0.0.21 -U replicator -D /var/lib/postgresql/17/main \
  -X stream -S standby1 -R -P
  • -X stream — stream WAL during the backup.
  • -S standby1 — use the slot.
  • -R — write the standby configuration: creates standby.signal and adds primary_conninfo to postgresql.auto.conf.
  • -P — show progress.

Store the password in ~postgres/.pgpass to avoid prompts:

10.0.0.21:5432:replication:replicator:a-long-random-password

(mode 600, owned by postgres).

Start the standby:

sudo systemctl start postgresql

5. Verify

On the primary:

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

state = streaming means it’s working. On the standby:

SELECT pg_is_in_recovery();                  -- true
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

Test: create a table on the primary and query it on the standby. Writes on the standby fail with “cannot execute … in a read-only transaction” — as expected.

6. Read-only queries on the standby

With hot_standby = on, send reporting or read-heavy queries to the standby. Note replication lag: a row written on the primary appears on the standby milliseconds (or more under load) later, so don’t read your own writes from the standby. Long queries on the standby can conflict with replay; hot_standby_feedback = on reduces cancellations at the cost of some bloat on the primary.

7. Failover: promote the standby

If the primary is lost:

sudo -u postgres psql -c "SELECT pg_promote();"
# or: sudo -u postgres pg_ctlcluster 17 main promote

The standby becomes a writable primary. Then point your application to it (change DNS, a floating IP, or a connection string). Never let the old primary come back as a primary at the same time — a “split brain” corrupts your data history. Rebuild it as a new standby (pg_rewind can speed this up).

Automatic failover requires tooling such as Patroni (with etcd/Consul), repmgr or pg_auto_failover. Manual promotion is fine for many small setups.

Synchronous replication (optional)

For zero data loss on failover:

synchronous_standby_names = 'standby1'

Commits wait for the standby to confirm — slower writes, and if the standby is down, writes block. Use with at least two standbys or ANY 1 (…).

Replication is not a backup

Replication copies mistakes instantly: a DROP TABLE on the primary is replayed on the standby. Keep real backups with point-in-time recovery — see pgBackRest and pg_dump.

Related: PostgreSQL remote connections and database replication explained. Two KVM VPS servers in the same location make a simple, effective primary/standby pair.

  • PostgreSQL
  • Replication
  • High Availability