Set up PostgreSQL streaming replication with a hot standby

Streaming replication keeps a standby server continuously up to date by sending it the primary’s WAL as it is written. The standby replays the WAL and, as a hot standby, accepts read-only queries. If the primary fails, you promote the standby and point clients at it. Replication is asynchronous by default: the primary does not wait for the standby, so a crash can lose the most recent transactions.

This guide uses db1.example.com (10.0.0.11) as primary and db2.example.com (10.0.0.12) as standby, both on the same PostgreSQL major version. Paths follow Debian/Ubuntu packaging; adjust them for your system.

1. Prepare the primary

1. Create a replication role. Use a strong password and store it only in the standby’s ~/.pgpass or connection settings.
2. Allow it in pg_hba.conf with the special database name replication.
3. Check the WAL settings and reload or restart as needed.
4. Create a physical replication slot so the primary keeps WAL until the standby has received it.

sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'CHANGE_ME';"

# pg_hba.conf on the primary
host    replication     replicator      10.0.0.12/32            scram-sha-256

# postgresql.conf on the primary
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
max_slot_wal_keep_size = 10GB

wal_level = replica and generous max_wal_senders are already the defaults in recent versions; listen_addresses and wal_level need a restart. max_slot_wal_keep_size caps how much WAL a slot may hold back, so a dead standby cannot fill the primary’s disk.

sudo systemctl restart postgresql
sudo -u postgres psql -c "SELECT * FROM pg_create_physical_replication_slot('db2_slot');"

2. Clone the primary onto the standby

Warning: the next step deletes the standby’s existing data directory. Run it only on the new standby, and first confirm with pg_lsclusters (or SELECT pg_is_in_recovery();) that this machine holds no data you need.

sudo systemctl stop postgresql
sudo -u postgres mv /var/lib/postgresql/18/main /var/lib/postgresql/18/main.old
sudo -u postgres pg_basebackup -h 10.0.0.11 -U replicator \
  -D /var/lib/postgresql/18/main -X stream -S db2_slot -R -P -c fast

The data directory is moved aside rather than deleted, so you can remove main.old later once the standby works. -S db2_slot uses the slot you created, and -R writes the standby configuration for you: it creates an empty standby.signal file and adds primary_conninfo to postgresql.auto.conf. Without -R you would set these yourself:

# postgresql.conf (or postgresql.auto.conf) on the standby
primary_conninfo = 'host=10.0.0.11 port=5432 user=replicator'
primary_slot_name = 'db2_slot'
hot_standby = on

Keep the password out of primary_conninfo by putting a line in ~postgres/.pgpass on the standby, with replication in the database field: 10.0.0.11:5432:replication:replicator:CHANGE_ME (mode 0600).

3. Start and verify

sudo systemctl start postgresql
sudo -u postgres psql -c 'SELECT pg_is_in_recovery();'
sudo -u postgres psql -x -c 'SELECT status, sender_host, slot_name FROM pg_stat_wal_receiver;'

On the standby pg_is_in_recovery() returns t and pg_stat_wal_receiver.status shows streaming. On the primary, pg_stat_replication has one row per connected standby:

SELECT application_name, client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Test it: create a table on the primary and select from it on the standby. Writing on the standby fails, because it is read-only.

4. Synchronous replication (optional)

To make commits wait until a standby has the data, set synchronous_standby_names on the primary, for example 'FIRST 1 (db2)' or 'ANY 1 (db2, db3)'. The names match the standby’s application_name (set it in primary_conninfo). synchronous_commit then controls how far the standby must have gone: remote_write, on (flushed) or remote_apply. Be aware that if no synchronous standby is available, commits on the primary wait.

5. Promotion and failover

To turn the standby into a primary, run pg_ctl promote or SELECT pg_promote(); on it. Make sure the old primary is really down first; two writable primaries cause diverging data (“split brain”). The old primary cannot simply rejoin: rebuild it as a standby with pg_basebackup or rewind it with pg_rewind.

sudo -u postgres psql -c 'SELECT pg_promote();'

Automatic failover with Patroni

PostgreSQL itself does not decide when to fail over. Patroni is a widely used open-source tool that does: an agent runs next to each PostgreSQL node, stores cluster state and a leader lock in a distributed configuration store (etcd, Consul, ZooKeeper or Kubernetes), and promotes a standby when the leader disappears. It uses the same streaming replication described above, offers a REST API that load balancers such as HAProxy can use to find the current primary, and a command-line tool, patronictl, for switchovers. Its documentation recommends running the configuration store with 3 or 5 nodes.

Even with Patroni, learn the manual steps above: they are what the tool automates, and you need them when diagnosing a failed failover.

Cleanup

Replication slots for standbys that no longer exist keep WAL on the primary. Before dropping one, confirm in pg_replication_slots that it is active = f and really belongs to a decommissioned server: SELECT pg_drop_replication_slot('db2_slot');.

Official documentation

• Log-shipping standby servers
• pg_basebackup
• Patroni documentation