PostgreSQL HA with Patroni, PgBouncer, and HAProxy

PostgreSQL high availability is not one product. It is a coordinated operating model across database roles, consensus, routing, connection pooling, application retries, backups, observability, and recovery evidence.

Patroni can coordinate leader election and promotion. PgBouncer can control connection pressure. HAProxy can route clients to the correct role. None of them independently proves that a failover is safe, that the application reconnects correctly, or that corrupted data can be restored.

Download the PostgreSQL HA assets

• PostgreSQL failover drill — preconditions, failure injection, promotion, routing, reconnect, replication recovery, and evidence.
• PostgreSQL health checks — role, replication, sessions, waits, and long-transaction checks.
• HAProxy routing example — Patroni-aware primary and replica routing starting point.
• PgBouncer configuration example — conservative pooling limits and timeouts.
• Disaster recovery runbook repository — tested templates, drill evidence, and readiness scoring.

Treat every configuration as a starting point. Validate PostgreSQL, Patroni, PgBouncer, HAProxy, driver, and application versions in a disposable or representative environment before production use.

Patroni vs PgBouncer: they are not alternatives

The query “Patroni vs PgBouncer” usually reveals an architecture misunderstanding. They solve different problems.

A resilient design validates all of these layers together.

Define the failure and data-loss contract

Before selecting topology, answer:

• What is the maximum acceptable data loss, or RPO?
• How long may writes be unavailable, or RTO?
• Can any reads be stale?
• What happens when the primary host disappears?
• What happens when consensus is unavailable?
• What happens when corruption replicates successfully?
• How does the application behave when every connection breaks?
• Which migrations make the previous application version unsafe?
• When was the last clean restore completed and measured?

High availability addresses a subset of failures. Disaster recovery is still required for corruption, deletion, lost credentials, unavailable regions, broken automation, and compromised environments.

Build a connection budget before scaling application replicas

Many PostgreSQL incidents are connection incidents. Application replicas increase, each process creates a local pool, a deploy restarts everything, and PostgreSQL spends memory and scheduling time managing sessions instead of useful work.

Start from the database limit and work outward:

PostgreSQL max_connections
- superuser emergency reserve
- replication connections
- monitoring and backup sessions
- maintenance and migration reserve
= safe application server connection budget

Then divide the safe budget across PgBouncer instances and workloads.

Example:

max_connections: 300
administration reserve: 15
replication: 10
monitoring/backups: 15
maintenance/migrations: 20
safe application server budget: 240

PgBouncer instances: 2
safe default_pool_size per instance across all databases/users: sized so aggregate server connections stay below 240

Do not simply set max_connections to a large number. Each backend consumes memory and adds scheduling overhead, while a connection storm often coincides with application retries and degraded query latency.

Useful SQL:

select state, count(*)
from pg_stat_activity
group by state
order by count(*) desc;

select wait_event_type, wait_event, count(*)
from pg_stat_activity
where wait_event is not null
group by 1, 2
order by 3 desc;

select usename, application_name, client_addr, state,
       count(*) as sessions
from pg_stat_activity
group by 1, 2, 3, 4
order by sessions desc;

PgBouncer pool mode must match application semantics

Before transaction pooling, test:

• prepared-statement behavior for the actual driver and PgBouncer version;
• session SET commands;
• temporary tables;
• advisory locks;
• LISTEN/NOTIFY;
• migrations and administrative tools;
• transaction boundaries;
• connection reset behavior.

A practical starting point:

[databases]
app = host=haproxy-primary.internal port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 60
reserve_pool_size = 10
reserve_pool_timeout = 3
server_connect_timeout = 5
server_login_retry = 2
query_timeout = 30
query_wait_timeout = 15
client_idle_timeout = 300
server_idle_timeout = 60
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

The numbers are not universal. Derive them from workload concurrency, query duration, database memory, and the connection budget.

Patroni health and failover gates

Patroni relies on a distributed configuration store and correct fencing assumptions. Before failover, capture:

patronictl list
curl -fsS http://127.0.0.1:8008/cluster
curl -fsS http://127.0.0.1:8008/health

On the candidate replica:

select pg_is_in_recovery();
select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
select now() - pg_last_xact_replay_timestamp() as replay_delay;

Failover should be blocked or escalated when:

• candidate lag exceeds RPO;
• consensus health is uncertain;
• more than one node may accept writes;
• timeline history is inconsistent;
• the old primary cannot be fenced or isolated;
• required WAL is missing;
• client routing behavior is unknown;
• the application cannot tolerate connection loss or transaction retry.

For planned maintenance, prefer switchover over forced failover:

patronictl switchover CLUSTER_NAME \
  --leader CURRENT_PRIMARY \
  --candidate TARGET_REPLICA

For an actual primary loss, use a controlled failover only after the decision gate:

patronictl failover CLUSTER_NAME \
  --candidate TARGET_REPLICA \
  --force

HAProxy routing for Patroni roles

Patroni exposes REST endpoints that can distinguish primary and replica roles. A simple HAProxy pattern:

frontend postgres_write
    bind *:5432
    mode tcp
    default_backend patroni_primary

backend patroni_primary
    mode tcp
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 2s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 pg1.internal:5432 check port 8008
    server pg2 pg2.internal:5432 check port 8008
    server pg3 pg3.internal:5432 check port 8008

frontend postgres_read
    bind *:5433
    mode tcp
    default_backend patroni_replicas

backend patroni_replicas
    mode tcp
    option httpchk GET /replica?lag=16MB
    http-check expect status 200
    balance roundrobin
    server pg1 pg1.internal:5432 check port 8008
    server pg2 pg2.internal:5432 check port 8008
    server pg3 pg3.internal:5432 check port 8008

Review endpoint semantics and lag thresholds for the installed Patroni version. Read routing also needs application consistency rules; a healthy replica may still be too stale for a specific customer operation.

What happens during failover

A realistic sequence is:

1. Existing database connections to the old primary fail or hang until timeouts expire.
2. Patroni and the consensus layer determine the new leader.
3. The candidate promotes and exposes the primary health endpoint.
4. HAProxy begins routing new connections to the new primary.
5. PgBouncer discards or replaces broken server connections.
6. Application clients retry or reconnect.
7. Old primary is isolated and later rejoins as a replica after timeline validation.
8. Backups, monitoring, read routing, and maintenance follow the new topology.

The most common hidden failure is step 5 or 6. Database promotion succeeds, but pooled connections stay broken, retries synchronize into a storm, or transactions are replayed unsafely.

Application reconnect and retry rules

Test the real driver and framework.

Safe principles:

• use bounded connection and statement timeouts;
• retry connection establishment with jitter;
• retry transactions only when the operation is known to be idempotent or has a deduplication key;
• do not retry an unknown commit outcome blindly;
• expose pool acquisition wait time and retry count;
• cap concurrency while the database recovers;
• distinguish read-only transaction errors from write-path failures.

A transaction may have committed even when the client lost the response. Business operations need idempotency keys or reconciliation logic, not only driver retries.

Replication and durability checks

On the primary:

select application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
from pg_stat_replication
order by application_name;

On a replica:

select pg_is_in_recovery(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() as replay_delay;

Monitor both byte lag and time lag. Time lag can be misleading during idle periods, while byte lag alone does not express business RPO.

Synchronous replication can reduce data loss but increases write availability dependence on the synchronous standby policy. Test failure of the synchronous replica, network partitions, and degraded storage before relying on the setting.

Query latency, locks, and autovacuum still matter

HA does not fix an overloaded database.

Review:

• pg_stat_statements by total and mean execution time;
• long and idle-in-transaction sessions;
• blocking chains;
• autovacuum progress and dead tuples;
• checkpoint frequency and WAL generation;
• storage latency;
• index usage and write amplification;
• pool wait time and transaction duration.

Blocking-chain input:

select
  blocked.pid as blocked_pid,
  blocker.pid as blocker_pid,
  now() - blocked.query_start as blocked_for,
  left(blocked.query, 120) as blocked_query,
  left(blocker.query, 120) as blocker_query
from pg_stat_activity blocked
join pg_locks bl on bl.pid = blocked.pid and not bl.granted
join pg_locks kl
  on kl.locktype = bl.locktype
 and kl.database is not distinct from bl.database
 and kl.relation is not distinct from bl.relation
 and kl.page is not distinct from bl.page
 and kl.tuple is not distinct from bl.tuple
 and kl.virtualxid is not distinct from bl.virtualxid
 and kl.transactionid is not distinct from bl.transactionid
 and kl.classid is not distinct from bl.classid
 and kl.objid is not distinct from bl.objid
 and kl.objsubid is not distinct from bl.objsubid
 and kl.pid <> bl.pid
join pg_stat_activity blocker on blocker.pid = kl.pid
where kl.granted;

Use the result as evidence. Do not automate session termination without understanding transaction and business impact.

Backup and restore remain mandatory

Replication is not recovery from every failure. A complete system needs:

• protected base backups;
• continuous WAL archiving;
• retention matching recovery requirements;
• backup integrity checks;
• encryption-key and credential recovery;
• clean restore environment;
• point-in-time recovery procedure;
• application and business validation;
• measured RPO and RTO.

Example backup evidence record:

Backup ID: ...
Base backup completed: ...
WAL archive latest object: ...
Integrity check: pass / fail
Restore target: isolated environment
Restore started: ...
Database available: ...
Application validation passed: ...
Recovered point: ...
Measured RPO: ...
Measured RTO: ...
Evidence location: ...

Failover drill acceptance criteria

A drill is successful only when:

• one and only one primary is writable;
• the promoted node meets RPO;
• routing reaches the correct role;
• PgBouncer replaces broken server connections;
• applications reconnect within target;
• no uncontrolled retry storm occurs;
• a critical write transaction succeeds exactly once;
• replicas rejoin the correct timeline;
• backup and monitoring follow the new primary;
• evidence and measured RTO are recorded;
• every gap has an owner and deadline.

Run the PostgreSQL failover drill before a planned production architecture change and after material changes to drivers, routing, consensus, networking, or database versions.

Related SteadyOps reading

• Disaster Recovery Runbook Template — failover versus restore decisions, validation, and drill evidence.
• Kubernetes Production Readiness Checklist — workload lifecycle, release safety, observability, and recovery.
• Zero-Downtime Deployments — application rollout and database compatibility during releases.
• Security Evidence Operations Model — operational evidence, access history, and incident records.

Key takeaways

• Patroni, PgBouncer, and HAProxy are complementary layers, not alternatives.
• A connection budget is a reliability control, not only a performance optimization.
• Failover safety depends on RPO, consensus, fencing, routing, pooling, and application retry behavior.
• Promotion is incomplete until a real business write succeeds exactly once.
• Replication does not replace point-in-time recovery and clean restore drills.
• Every HA claim should be supported by a repeatable drill and retained evidence.

[databases]
app = host=postgres-primary port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 40
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 60
query_timeout = 30
client_idle_timeout = 300

select state, count(*)
from pg_stat_activity
group by state
order by count(*) desc;

select pid, now() - xact_start as age, state, wait_event_type, wait_event, left(query, 120)
from pg_stat_activity
where xact_start is not null
order by xact_start;

select client_addr, state, sync_state, write_lag, flush_lag, replay_lag
from pg_stat_replication;