Introduction
Set up a highly available PostgreSQL cluster with streaming replication, automatic failover using Patroni, and connection pooling. This comprehensive guide covers everything from design through implementation, testing, and deployment.
Database Replication and Clustering
Set up a highly available PostgreSQL cluster with streaming replication, automatic failover using Patroni, and connection pooling.
DifficultyAdvanced
Duration3–4 weeks
Components10 items
Steps3 steps
Theory & Background
Streaming replication: primary streams WAL (Write-Ahead Log) to standby replicas in real-time. Latency: typically < 10ms for replication lag. Configure on primary: wal_level=replica, max_wal_senders=5, synchronous_standby_names='' (async) or 'standby1' (sync). On standby: recovery.conf (PostgreSQL 12+: primary_conninfo in postgresql.conf, standby.signal file). Verify: pg_stat_replication on primary shows connected standbys. pg_last_wal_receive_lsn() on standby shows current lag.
Advertisement
Components & Requirements
10 components required for this project.
| # | Component | Purpose | Qty |
|---|---|---|---|
| 1 | Ubuntu VMs (4 nodes) | 3 DB nodes + 1 monitoring | x4 |
| 2 | PostgreSQL 15 | Database engine | x1 |
| 3 | Patroni | HA PostgreSQL cluster manager | x1 |
| 4 | etcd (3 nodes) | Distributed consensus for Patroni | x1 |
| 5 | PgBouncer | Connection pooling | x1 |
| 6 | HAProxy | Route to primary vs replica | x1 |
| 7 | pgBackRest | Continuous archiving and PITR backup | x1 |
| 8 | Patroni REST API | Cluster state monitoring | x1 |
| 9 | Netdata / Prometheus | DB performance monitoring | x1 |
| 10 | pgAdmin 4 | Database management GUI | x1 |
Step-by-Step Implementation
Follow these 3 steps carefully.
1
PostgreSQL Streaming Replication
Streaming replication: primary streams WAL (Write-Ahead Log) to standby replicas in real-time. Latency: typically < 10ms for replication lag. Configure on primary: wal_level=replica, max_wal_senders=5, synchronous_standby_names='' (async) or 'standby1' (sync). On standby: recovery.conf (PostgreSQL 12+: primary_conninfo in postgresql.conf, standby.signal file). Verify: pg_stat_replication on primary shows connected standbys. pg_last_wal_receive_lsn() on standby shows current lag.
2
Patroni Automatic Failover
Patroni is a template for HA PostgreSQL using DCS (Distributed Configuration Store) — etcd, ZooKeeper, or Consul. Election: when primary fails, Patroni on all nodes communicates via DCS to elect new leader (highest LSN replica wins). Failover time: 10–30 seconds. After election: winner promotes to primary, others reconfigure to replicate from new primary. HAProxy health endpoint: /master returns 200 for primary, 503 for replica. Direct read queries to replica: /replica returns 200 for healthy replicas.
3
PgBouncer Connection Pooling
PostgreSQL creates a separate process per connection — heavy at 1000+ connections. PgBouncer pools: maintains small pool of actual DB connections, multiple application connections share pool. Mode: transaction pooling (most efficient — connection returned after each transaction, session-level features like prepared statements not supported), session pooling (one server connection per application session). Typical: 10,000 application connections → 100 DB connections. Configure pool_size per database, max_client_conn, reserve_pool_size for peak traffic.
Code & Implementation
Core code for patroni.yml:
patroni.yml
YAML
scope: catb-postgres-cluster namespace: /db/ name: postgres-node1 restapi: listen: 0.0.0.0:8008 connect_address: 192.168.10.21:8008 etcd3: hosts: - 192.168.10.31:2379 - 192.168.10.32:2379 - 192.168.10.33:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 # 1MB max lag master_start_timeout: 300 postgresql: use_pg_rewind: true use_slots: true parameters: wal_level: replica hot_standby: "on" max_wal_senders: 5 max_replication_slots: 5 postgresql: listen: 0.0.0.0:5432 connect_address: 192.168.10.21:5432 data_dir: /var/lib/postgresql/15/main bin_dir: /usr/lib/postgresql/15/bin authentication: replication: username: replicator password: "{{ REPLICATOR_PASSWORD }}" superuser: username: postgres password: "{{ POSTGRES_PASSWORD }}"Testing & Troubleshooting
Test Database Replication and Clustering by verifying each subsystem individually before full integration.
Troubleshooting Tips
Verify power voltages, check ground connections, use serial monitor for debug.
Real-World Applications
E-commerce transactional database HA
Financial system zero-downtime database
Healthcare records database compliance
SaaS application database tier
Gaming leaderboard high-throughput DB
Analytics database read scaling
Disaster recovery database setup
Multi-region data residency compliance
Extensions & Next Steps
- Implement logical replication for schema-independent replication
- Add Citus extension for distributed PostgreSQL sharding
- Build automated PITR restore testing pipeline
- Implement row-level security for multi-tenant data isolation
- Add TimescaleDB for time-series data workloads
Interactive Playground
Coming SoonAn interactive simulator will be available here — simulate circuits and run code in-browser without hardware.
Frequently Asked Questions
What is the difference between synchronous and asynchronous replication?
Asynchronous replication: primary commits transaction, then streams WAL to replicas. If primary fails before replica receives WAL, data loss possible (RPO > 0). Performance: no waiting for replica confirmation — full primary performance. Synchronous replication: primary waits for at least one replica to confirm WAL receipt before committing. Guaranteed zero data loss (RPO = 0). Performance impact: commit latency includes round-trip to replica (+ 1ms on LAN, + 10ms on WAN). Hybrid: synchronous_commit='local' for data loss tolerance, 'on' for zero-loss. Most production databases use asynchronous with acceptable RPO (seconds to minutes).
Advertisement