PostgreSQL Replication and High Availability: From Streaming to Patroni

Why PostgreSQL HA Matters

A database outage is the most impactful failure your application can experience. Unlike stateless services that can restart in seconds, a database holds your most critical asset — your data. High availability ensures your PostgreSQL database survives hardware failures, network partitions, and planned maintenance.

Setting Up Streaming Replication

Primary Configuration

-- postgresql.conf on primary
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 5;
ALTER SYSTEM SET wal_keep_size = '1GB';
ALTER SYSTEM SET hot_standby = 'on';
SELECT pg_reload_conf();

Create the replication user:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure-replication-password';

Replica Setup

# Stop PostgreSQL on replica
sudo systemctl stop postgresql

# Clear data directory
sudo rm -rf /var/lib/postgresql/16/main/*

# Base backup from primary (-R creates standby.signal)
sudo -u postgres pg_basebackup \
  -h primary.example.com \
  -U replicator \
  -D /var/lib/postgresql/16/main \
  -Fp -Xs -P -R

# Start replica
sudo systemctl start postgresql

Verify Replication

-- On primary: check connected replicas
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag
FROM pg_stat_replication;

-- On replica: confirm it is in recovery mode
SELECT pg_is_in_recovery();  -- Should return true

PgBouncer Connection Pooling

Applications should never connect directly to PostgreSQL in production. PgBouncer pools connections efficiently:

# pgbouncer.ini
[databases]
myapp = host=pg-primary port=5432 dbname=myapp
myapp_ro = host=pg-replica port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5

Patroni: Automatic Failover

Patroni manages PostgreSQL HA clusters with automatic failover using a distributed consensus store like etcd:

# patroni.yml
scope: myapp-cluster
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: node1:8008

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

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        max_wal_senders: 5
        hot_standby: "on"

postgresql:
  listen: 0.0.0.0:5432
  connect_address: node1:5432
  data_dir: /var/lib/postgresql/data
  authentication:
    superuser:
      username: postgres
      password: postgres-password
    replication:
      username: replicator
      password: replication-password

Monitoring Patroni

# Check cluster status
patronictl -c /etc/patroni.yml list

# Output shows leader, replicas, lag, and timeline
# Manual failover for planned maintenance
patronictl -c /etc/patroni.yml switchover --master node1 --candidate node2

Monitoring Replication Lag

-- Replication lag in seconds on the replica
SELECT CASE
    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
    ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS replication_lag_seconds;

Key metrics to alert on:

• Replication lag > 30 seconds
• Flush lag increasing steadily
• Replica count drops below expected value

Backup from Replica

Always backup from the replica to avoid loading the primary:

# Logical backup from replica
pg_dump -h pg-replica -U postgres myapp | gzip > backup.sql.gz

# Physical backup for point-in-time recovery
pg_basebackup -h pg-replica -U replicator -D /backups/base -Ft -z -P

At TechSaaS, we run PostgreSQL 16 as a shared database service for multiple applications. For clients requiring HA, we deploy Patroni with PgBouncer connection pooling, achieving 99.99% database availability.

Need PostgreSQL HA for your application? Contact [email protected].