PostgreSQL Replication & High Availability

Replication Overview

PostgreSQL supports multiple replication methods to keep copies of your data on separate servers. Replication provides high availability (failover), read scaling (read replicas), and disaster recovery (offsite copies).

Streaming Replication Setup

Streaming replication sends WAL (Write-Ahead Log) records from the primary to one or more standby servers in near real-time. Standbys can serve read-only queries.

-- On the PRIMARY server (postgresql.conf):
wal_level = replica
max_wal_senders = 10
wal_keep_size = '1GB'          -- or use replication slots

-- Create a replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'rep_secret';

-- pg_hba.conf: allow replication connections
-- host replication replicator standby_ip/32 scram-sha-256

-- On the STANDBY server: create base backup
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/16/main \
  --checkpoint=fast --wal-method=stream -R

-- The -R flag creates standby.signal and sets primary_conninfo
-- Start the standby — it automatically connects and streams WAL

Replication Slots & Monitoring

-- Create a replication slot (prevents WAL cleanup before replica catches up)
SELECT pg_create_physical_replication_slot('standby_1');

-- Monitor replication lag
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- On the standby: check replication status
SELECT pg_is_in_recovery(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       pg_last_xact_replay_timestamp();

Logical Replication

Logical replication publishes changes from specific tables. Subscribers can be different PostgreSQL versions and can have their own indexes and even additional tables.

-- On the PUBLISHER (source):
ALTER SYSTEM SET wal_level = logical;
-- Restart PostgreSQL

CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- Or publish all tables:
-- CREATE PUBLICATION my_pub FOR ALL TABLES;

-- On the SUBSCRIBER (target):
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=primary_host dbname=app_db user=replicator password=rep_secret'
  PUBLICATION my_pub;

-- Monitor subscription status
SELECT * FROM pg_stat_subscription;

High Availability with Patroni

# Patroni manages automatic failover for PostgreSQL clusters
# Architecture: Primary + Standby(s) + DCS (etcd/Consul/ZooKeeper)

# patroni.yml (simplified)
scope: my-cluster
name: node1

restapi:
  listen: 0.0.0.0:8008

etcd3:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379

bootstrap:
  dcs:
    postgresql:
      parameters:
        max_connections: 200
        wal_level: replica
        max_wal_senders: 10

postgresql:
  listen: 0.0.0.0:5432
  data_dir: /var/lib/postgresql/data
  authentication:
    replication:
      username: replicator
      password: rep_secret

# Patroni automatically:
# - Elects a new primary if the current one fails
# - Reconfigures standbys to follow the new primary
# - Prevents split-brain with distributed consensus