PostgreSQL Replication#

Streaming replication gives you a full binary copy for high availability and read scaling. Logical replication gives you selective table-level syncing between databases that can run different PostgreSQL versions.

Streaming Replication Setup#

Configure the Primary#

# postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB

Create a replication role and allow connections:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl-secret';

# pg_hba.conf
host  replication  replicator  10.0.0.0/8  scram-sha-256

Initialize the Standby#

sudo systemctl stop postgresql-16
sudo rm -rf /var/lib/postgresql/16/main/*
pg_basebackup -h primary-host -U replicator -D /var/lib/postgresql/16/main \
  --checkpoint=fast --wal-method=stream -R -P
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo systemctl start postgresql-16

The -R flag creates standby.signal and writes connection info to postgresql.auto.conf. The standby now continuously receives and replays WAL from the primary, accepting read-only queries by default.

Replication Slots#

Without a slot, the primary can recycle WAL before the standby consumes it, forcing a full re-sync.

-- On primary
SELECT pg_create_physical_replication_slot('standby1');

Add primary_slot_name = 'standby1' on the standby. Warning: if the standby goes offline, the slot prevents WAL cleanup and the primary’s disk fills up. Monitor and drop unused slots:

SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;
SELECT pg_drop_replication_slot('standby1');  -- if no longer needed

Synchronous vs Asynchronous#

Default is asynchronous – the primary commits without waiting for standby confirmation. The standby can lag slightly, and you lose recent transactions if the primary crashes.

Synchronous makes the primary wait for standby confirmation before acknowledging commits:

# postgresql.conf on primary
synchronous_standby_names = 'standby1'
synchronous_commit = on

The standby’s application_name in primary_conninfo must match. Trade-off: zero data loss but every write adds network latency. If the standby is unreachable, writes on the primary stall. For most setups, asynchronous with replication slots is the right default.

Monitoring Replication Lag#

On the primary:

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

On the standby:

SELECT pg_is_in_recovery() AS is_standby,
  now() - pg_last_xact_replay_timestamp() AS replay_delay;

Alert if replay_lag_bytes exceeds a few MB or replay_delay exceeds a few seconds.

Promoting a Standby#

When the primary fails:

sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main
# Or via SQL: SELECT pg_promote();

After promotion: update application connection strings to the new primary. The old primary cannot rejoin automatically – use pg_rewind to re-sync it:

pg_rewind --target-pgdata=/var/lib/postgresql/16/main \
  --source-server="host=new-primary port=5432 user=postgres" -P

Then create standby.signal and configure primary_conninfo pointing to the new primary.

Logical Replication#

Publishes changes from specific tables. The subscriber applies changes as SQL, so it can have different indexes or a different PostgreSQL version.

On the publisher:

wal_level = logical

CREATE PUBLICATION my_pub FOR TABLE orders, customers;

On the subscriber:

CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host port=5432 dbname=myapp user=replicator password=repl-secret'
  PUBLICATION my_pub;

The subscriber does an initial sync, then applies changes in real time. Monitor:

SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;

Common Failures#

Standby cannot connect: Check pg_hba.conf allows replication type, network/firewall, and that the replicator role has the REPLICATION attribute.

Standby falls behind permanently: WAL files recycled before the standby read them (no slot). Re-initialize with pg_basebackup.

Replication slot causing disk bloat: Offline standby with active slot prevents WAL cleanup. Drop the slot if the standby is gone.

Split-brain after failover: Two servers accepting writes. Pick the authoritative server, pg_rewind the other. Proper fencing (STONITH) prevents this.