PostgreSQL Replication & High Availability: Streaming Replication, Patroni & Failover (2026)

wal_level = replica          # minimum for streaming replication
max_wal_senders = 10         # max concurrent WAL sender processes
wal_keep_size = 1GB          # retain this much WAL for slow standbys (no slot)
hot_standby = on             # allow reads on standby (set on standby too)
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

host  replication  replicator  10.0.1.0/24  scram-sha-256

# Wait for at least one standby named 'standby1' OR 'standby2' to flush WAL
synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'

# For ANY 1 of 2 standbys to acknowledge (quorum)
# synchronous_standby_names = 'ANY 1 (standby1, standby2)'

# Per-transaction override (useful for non-critical writes):
# SET LOCAL synchronous_commit TO off;

-- On primary:
CREATE USER replicator REPLICATION LOGIN PASSWORD 'str0ngPass!';

-- On standby server (-Xs = stream WAL, -R = write recovery config):
pg_basebackup -h 10.0.1.10 -U replicator \
  -D /var/lib/postgresql/16/main -P -Xs -R --checkpoint=fast

primary_conninfo = 'host=10.0.1.10 port=5432 user=replicator password=str0ngPass!'
primary_slot_name = 'standby1_slot'

touch /var/lib/postgresql/16/main/standby.signal
pg_ctlcluster 16 main start

SELECT client_addr, state, sent_lsn, replay_lsn,
       pg_size_pretty(sent_lsn - replay_lsn) AS lag
FROM pg_stat_replication;

-- postgresql.conf: wal_level = logical
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;
-- Or replicate entire database:
-- CREATE PUBLICATION all_pub FOR ALL TABLES;

CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=10.0.1.10 dbname=appdb user=replicator password=str0ngPass!'
  PUBLICATION orders_pub
  WITH (copy_data = true, create_slot = true);

-- Monitor subscription status
SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;

scope: postgres-ha
name: pg-node1
restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.10:8008
etcd3:
  hosts: 10.0.1.20:2379,10.0.1.21:2379,10.0.1.22:2379
bootstrap:
  dcs:
    ttl: 30                         # leader lock TTL in seconds
    loop_wait: 10                   # health-check interval
    maximum_lag_on_failover: 1048576  # don't promote if > 1MB behind
    synchronous_mode: false         # set true for zero-loss failover
  initdb: [{encoding: UTF8}, {data-checksums: true}]
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.10:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  authentication:
    replication: {username: replicator, password: str0ngPass!}
    superuser: {username: postgres, password: s3cr3t!}
  parameters:
    wal_level: replica
    max_wal_senders: 10
    wal_keep_size: 1GB

# List cluster state (shows primary + replica lag)
patronictl -c /etc/patroni.yml list

# Planned switchover (graceful, zero data loss)
patronictl -c /etc/patroni.yml switchover --master pg-node1 --candidate pg-node2

# Emergency failover (promotes best standby immediately)
patronictl -c /etc/patroni.yml failover postgres-ha

# Pause/resume auto-failover (for maintenance)
patronictl -c /etc/patroni.yml pause
patronictl -c /etc/patroni.yml resume

[databases]
appdb = host=10.0.1.10 port=5432 dbname=appdb  # points to primary (or HAProxy VIP)
appdb_ro = host=10.0.1.11 port=5432 dbname=appdb  # points to replica

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25       # backend connections per db/user pair
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
client_idle_timeout = 0
log_connections = 0
log_disconnections = 0
stats_period = 60

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                ? "REPLICA" : "PRIMARY";
    }
}

@Configuration
public class DataSourceConfig {
    @Bean @ConfigurationProperties("spring.datasource.primary")
    public DataSource primaryDataSource() { return DataSourceBuilder.create().build(); }

    @Bean @ConfigurationProperties("spring.datasource.replica")
    public DataSource replicaDataSource() { return DataSourceBuilder.create().build(); }

    @Bean @Primary
    public DataSource routingDataSource(
            @Qualifier("primaryDataSource") DataSource primary,
            @Qualifier("replicaDataSource") DataSource replica) {
        RoutingDataSource routing = new RoutingDataSource();
        routing.setTargetDataSources(Map.of("PRIMARY", primary, "REPLICA", replica));
        routing.setDefaultTargetDataSource(primary);
        return routing;
    }
}

spring:
  datasource:
    primary:
      url: jdbc:postgresql://pgbouncer:6432/appdb
      username: appuser
      password: ${DB_PASSWORD}
      hikari:
        maximum-pool-size: 10
        pool-name: PrimaryPool
    replica:
      url: jdbc:postgresql://pgbouncer:6432/appdb_ro
      username: appuser
      password: ${DB_PASSWORD}
      hikari:
        maximum-pool-size: 20   # more reads than writes
        pool-name: ReplicaPool

@Service
public class ProductService {

    @Transactional(readOnly = true)   // ← routes to REPLICA
    public List<Product> findAll() {
        return productRepository.findAll();
    }

    @Transactional                    // ← routes to PRIMARY
    public Product create(ProductRequest req) {
        return productRepository.save(new Product(req));
    }
}

SELECT application_name, client_addr, state,
       pg_size_pretty(sent_lsn - replay_lsn) AS lag_size,
       EXTRACT(EPOCH FROM replay_lag) AS replay_lag_sec,
       sync_state
FROM pg_stat_replication ORDER BY replay_lag DESC;

SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
-- retained_wal > 5GB = WARNING: drop or resume the slot!

- alert: PostgresReplicationLagHigh
  expr: pg_replication_lag > 30
  for: 2m
  labels: {severity: warning}
  annotations:
    summary: "Replication lag {{ $value }}s on {{ $labels.instance }}"

[global]
repo1-type=s3
repo1-s3-bucket=my-pg-backups
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=us-east-1
repo1-retention-full=4       # keep 4 full backups
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
compress-type=lz4
process-max=4                # parallel compression

[main]
pg1-path=/var/lib/postgresql/16/main

# Full backup every Sunday 01:00
0 1 * * 0  postgres  pgbackrest --stanza=main --type=full backup
# Incremental daily
0 1 * * 1-6 postgres  pgbackrest --stanza=main --type=incr backup

# PITR: restore to timestamp (before a bad migration)
pg_ctlcluster 16 main stop
pgbackrest --stanza=main --delta --type=time \
  "--target=2026-04-11 14:25:00+00" --target-action=promote restore
pg_ctlcluster 16 main start

# 1. Confirm primary is truly dead
pg_isready -h 10.0.1.10 -p 5432   # should fail
# 2. Choose standby with highest replay_lsn
psql -h 10.0.1.11 -c "SELECT pg_last_wal_replay_lsn();"
# 3. Promote the best standby
pg_ctlcluster 16 main promote
# 4. Update HAProxy backend to new primary IP
echo "set server pg_backend/pg-node1 addr 10.0.1.11" | socat stdio /var/run/haproxy/admin.sock
# 5. Reconfigure remaining standbys: edit primary_conninfo, then:
SELECT pg_reload_conf();
# 6. Rejoin old primary as standby using pg_rewind (faster than basebackup)
pg_rewind --target-pgdata=/var/lib/postgresql/16/main \
          --source-server="host=10.0.1.11 user=postgres"

# 1. Document current primary:  patronictl list
# 2. Simulate crash: kill -9 $(pgrep -f patroni) on primary node
# 3. Measure failover time: watch patronictl list  (target: < 30s)
# 4. Verify data consistency: SELECT COUNT(*) on critical tables
# 5. Rejoin failed node: patronictl reinit postgres-ha pg-node1

• wal_level = replica on all nodes
• At least 2 standbys (1 sync + 1 async)
• Patroni + 3-node etcd cluster
• PgBouncer in transaction pool mode
• HAProxy health-check on Patroni :8008
• pg_stat_replication monitored & alerted
• Replication lag alert (<30s warn / <120s crit)
• No orphaned replication slots
• pgBackRest full weekly + incremental daily
• WAL archiving enabled (archive_mode=on)
• PITR tested within retention window
• pgbackrest verify run weekly
• AbstractRoutingDataSource read/write split
• max_connections tuned (RAM / 3MB)
• scram-sha-256 auth in pg_hba.conf
• data-checksums enabled
• DR runbook tested quarterly
• RTO < 30s and RPO defined per SLA