PostgreSQL replication basics (streaming replica)

Topic: Databases core

Set up a streaming replica for high availability or read scaling. Configure the primary for replication (wal_level, pg_hba); use pg_basebackup to clone; add primary_conninfo and recovery target on the replica. Use this when you need a standby or read replica.

Intent: How-to

Quick answer

  • Primary: wal_level=replica (or logical), max_wal_senders, replication slot or pg_hba entry for replica. Replica: pg_basebackup from primary; add primary_conninfo and standby.signal (or recovery.conf in older versions) in data dir.
  • Start replica: it connects to primary and replays WAL. Verify with SELECT * FROM pg_stat_replication; on primary. Replica is read-only by default; promote to primary for failover (pg_ctl promote or promote_trigger_file).
  • Synchronous replication: set synchronous_standby_names on primary so commits wait for replica. Use for zero data loss; adds latency. Monitor lag with pg_stat_replication.replication_lag.

Prerequisites

Steps

  1. Configure primary

    wal_level = replica; max_wal_senders = 2 or more; add pg_hba.conf entry for replication from replica IP. Restart primary. Optionally create a replication slot for the replica.

  2. Clone with pg_basebackup

    On replica host: pg_basebackup -h primary_ip -D /var/lib/pgsql/data -U repl -Ft -z -P. Create standby.signal in data dir; add primary_conninfo to postgresql.auto.conf or a config file.

  3. Start replica

    Start PostgreSQL on replica; it streams WAL from primary. On primary: SELECT * FROM pg_stat_replication; to see replica and lag. Replica is read-only; queries allowed for read scaling.

  4. Failover (promote)

    To promote replica: create promote_trigger_file and touch it, or pg_ctl promote. Reconfigure clients to use new primary; re-establish replica from new primary if needed.

Summary

Set wal_level and replication access on the primary; clone with pg_basebackup; add primary_conninfo and standby.signal on the replica. Use this for HA or read scaling; promote for failover.

Prerequisites

Steps

Step 1: Configure primary

Set wal_level and max_wal_senders; add pg_hba replication entry; restart.

Step 2: Clone with pg_basebackup

Run pg_basebackup from the replica host; create standby.signal and set primary_conninfo.

Step 3: Start replica

Start PostgreSQL on the replica; verify with pg_stat_replication on the primary.

Step 4: Failover (promote)

Promote the replica when needed; reconfigure clients and optionally re-create a new replica.

Verification

  • Replica is in recovery and streaming; pg_stat_replication shows it; lag is acceptable; promote procedure is documented and tested.

Troubleshooting

Replica not connecting — Check primary_conninfo, pg_hba, and network; check primary logs. Lag growing — Check replica I/O and CPU; increase wal_keep_size or use replication slots on primary.

Next steps

Continue to