Skip to content
DBAExperts
PostgreSQL High availability

How to set up streaming replication in PostgreSQL

Streaming replication keeps a standby server copying the primary's WAL in near real time. It is the foundation of high availability and of having a read-only replica.

  1. 1

    Prepare the primary for replication

    Set wal_level to replica and provide enough slots. Restart to apply the postgresql.conf changes.

    -- postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
  2. 2

    Create a replication role and allow its connection

    The standby connects with a role that has the REPLICATION attribute. Add the corresponding rule in pg_hba.conf.

    CREATE ROLE replicador WITH REPLICATION LOGIN PASSWORD 'cambia_esto';
-- pg_hba.conf:
-- host replication replicador <ip_standby>/32 scram-sha-256
  3. 3

    Clone the primary onto the standby with pg_basebackup

    Run on the standby server (with its data directory empty), this copies the entire cluster and generates the standby configuration with -R.

    pg_basebackup -h <ip_primario> -U replicador -D /var/lib/postgresql/16/main -Fp -Xs -P -R
  4. 4

    Start the standby and check the lag

    Start the standby and query the replication lag from the primary. Low, stable lag means the replica is keeping up.

    SELECT client_addr, state, replay_lag
FROM pg_stat_replication;

// common mistake

An abandoned replication slot (when the standby goes down and never comes back) makes the primary retain WAL indefinitely and fill the disk. Monitor your slots and drop the ones you no longer use.

// when it's worth an expert

Real high availability needs more than a standby: automatic failover, lag monitoring, switchover tests, and a plan to fail back to the primary. At dba.mx we set up HA with Patroni or repmgr and test it before the primary actually fails, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.