PostgreSQL High Availability: A Practical Guide with Patroni, pgBouncer, and pgBackRest

Introduction

Your database is the last thing that should go down. Yet setting up PostgreSQL for high availability remains one of the most under-documented areas in the DevOps world. Most teams run a single Postgres instance until the day it dies, then scramble to set up replication while their users stare at error pages.

This guide walks through a PostgreSQL HA architecture using the tools that have become the industry standard: Patroni for automated failover, pgBouncer for connection pooling, streaming replication for data redundancy, and pgBackRest for backups. We'll cover the architecture, actual configuration files, failure scenarios, and the operational runbooks you'll need.

If you're running Postgres in production and don't have automated failover, this article is for you.

Example scope: Paths below assume PostgreSQL 16 on Debian or Ubuntu. Configurations and output are illustrative, not a validated cluster deployment. Check your installed PostgreSQL, Patroni, PgBouncer and pgBackRest versions against the linked manuals. Network isolation, TLS, credentials, service units and restore orchestration must be completed for your environment.

Architecture Overview

A production HA Postgres setup has four layers:

                    ┌─────────────┐
                    │   HAProxy   │  (Virtual IP / DNS)
                    │  Port 5432  │
                    └──────┬──────┘
                           │
              ┌────────────┼────────────┐
              │            │            │
        ┌─────┴─────┐ ┌───┴───┐ ┌─────┴─────┐
        │ pgBouncer  │ │pgBouncer│ │ pgBouncer  │
        │  Node 1    │ │ Node 2 │ │  Node 3    │
        └─────┬──────┘ └───┬───┘ └─────┬──────┘
              │            │            │
        ┌─────┴─────┐ ┌───┴───┐ ┌─────┴─────┐
        │ Patroni +  │ │Patroni│ │ Patroni +  │
        │ Postgres   │ │  + PG  │ │ Postgres   │
        │ (Primary)  │ │(Replica)│ │ (Replica)  │
        └────────────┘ └────────┘ └────────────┘
              │            │            │
        ┌─────────────────────────────────────┐
        │           etcd Cluster (3 nodes)     │
        └─────────────────────────────────────┘

Patroni manages the Postgres instances and handles leader election via a distributed consensus store (etcd, ZooKeeper, or Consul). When the primary fails, Patroni automatically promotes a replica and reconfigures the others to follow the new primary.

pgBouncer sits between your application and Postgres, pooling connections to avoid the expensive fork-per-connection model. Each Postgres node runs its own pgBouncer instance.

HAProxy (or a DNS-based solution) routes traffic to the current primary. Patroni exposes a REST API that HAProxy uses for health checks.

Setting Up Patroni

Install Patroni on each Postgres node:

pip3 install "patroni[etcd3]"

Use a virtual environment and pin reviewed versions when packaging Patroni. The installation guide explains optional dependencies. This illustrative /etc/patroni/patroni.yml omits TLS and secret injection; keep the REST API and etcd endpoints private and authenticated.

scope: postgres-cluster
name: pg-node-1

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
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1MB
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        password_encryption: scram-sha-256
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
        archive_mode: "on"
        archive_command: >
          pgbackrest --stanza=main archive-push %p

  initdb:
    - encoding: UTF8
    - data-checksums

  pg_hba:
    - host replication replicator 10.0.1.0/24 scram-sha-256
    - host all all 10.0.1.0/24 scram-sha-256

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:
    superuser:
      username: postgres
      password: "your-secure-password"
    replication:
      username: replicator
      password: "your-replication-password"

  parameters:
    shared_buffers: 4GB
    effective_cache_size: 12GB
    work_mem: 64MB
    maintenance_work_mem: 512MB
    max_connections: 200
    checkpoint_completion_target: 0.9
    wal_buffers: 64MB
    random_page_cost: 1.1  # SSD storage

Key settings to understand:

• maximum_lag_on_failover: A replica won't be promoted if it's more than 1MB behind the primary. This is an eligibility threshold, not a zero-data-loss guarantee. Asynchronous replication can lose committed transactions, including writes after the last sampled WAL position. Choose durability and availability requirements using Patroni replication modes.
• use_pg_rewind: Allows the old primary to rejoin the cluster as a replica after failover without a full base backup. This is critical for fast recovery.
• use_slots: Replication slots prevent the primary from removing WAL segments that replicas still need, avoiding replication breakage during network issues.

After installing and reviewing the systemd unit, start Patroni in your isolated rehearsal environment:

sudo systemctl enable patroni
sudo systemctl start patroni

Check cluster status:

patronictl -c /etc/patroni/patroni.yml list

Illustrative output:

+ Cluster: postgres-cluster ----+---------+---------+----+-----------+
| Member    | Host       | Role    | State   | TL | Lag in MB |
+-----------+------------+---------+---------+----+-----------+
| pg-node-1 | 10.0.1.10  | Leader  | running |  1 |           |
| pg-node-2 | 10.0.1.11  | Replica | running |  1 |       0.0 |
| pg-node-3 | 10.0.1.12  | Replica | running |  1 |       0.0 |
+-----------+------------+---------+---------+----+-----------+

Configuring pgBouncer for Connection Pooling

Each PostgreSQL client connection uses a backend process. Memory depends on workload and configuration, so measure it instead of multiplying a fixed per-connection estimate. PgBouncer can share fewer server connections across more clients when the application supports the selected pooling mode.

; /etc/pgbouncer/pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Pool settings
pool_mode = transaction
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; Connection limits
max_client_conn = 1000
max_db_connections = 100

; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 60
query_wait_timeout = 30

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

Critical decision: pool_mode. Transaction mode is the right default for most applications - it assigns a server connection for the duration of a transaction, then returns it to the pool. Session mode keeps the server connection until the client disconnects, preserving session semantics while reusing connections between sessions. Statement mode is the most aggressive but breaks multi-statement transactions and prepared statements.

Compatibility check: Transaction pooling does not preserve arbitrary session state or LISTEN registrations. Protocol-level prepared statements can work when supported by your PgBouncer version and configured with a nonzero max_prepared_statements; SQL PREPARE is different. Check the feature matrix and configuration reference, then exercise your actual driver and ORM. SCRAM authentication also needs compatible credentials and server authentication settings.

Backup Strategy with pgBackRest

pgBackRest is the gold standard for Postgres backups. It supports full, differential, and incremental backups with parallel compression and encryption.

; /etc/pgbackrest/pgbackrest.conf

[global]
repo1-type=s3
repo1-s3-bucket=your-pg-backups
repo1-s3-region=us-east-1
repo1-s3-endpoint=s3.amazonaws.com
repo1-path=/backups
repo1-retention-full=4
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=your-encryption-passphrase

process-max=4
compress-type=zst
compress-level=3

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

Create the stanza and run your first backup:

# Initialize the backup repository
pgbackrest --stanza=main stanza-create

# Full backup
pgbackrest --stanza=main --type=full backup

# Differential backup (only changed since last full)
pgbackrest --stanza=main --type=diff backup

# Incremental backup (only changed since last backup of any type)
pgbackrest --stanza=main --type=incr backup

Set up a cron schedule:

# Full backup every Sunday at 1 AM
0 1 * * 0 pgbackrest --stanza=main --type=full backup

# Differential backup every day at 1 AM (except Sunday)
0 1 * * 1-6 pgbackrest --stanza=main --type=diff backup

Point-in-time recovery requires a suitable base backup and continuous archived WAL through the requested target. A scheduled backup alone does not prove that recovery works. The pgBackRest guide documents restore targets, repository access and recovery behavior.

Failover Testing and Operational Runbooks

Rehearse on an isolated copy with approved fault injection. Record the last acknowledged write, first successful reconnect and recovered application state. Do not infer recovery time from Patroni's TTL alone: routing, client retries and database startup also contribute.

Scenario	Evidence to retain
Primary failure	Eligible replica, leadership timeline, application reconnect time and missing acknowledged writes, if any.
DCS/network partition	Quorum, fencing behavior and proof that only one node accepts writes. Document how the injected fault is removed.
Planned switchover	Client errors, retry behavior and in-flight transaction handling. A planned switch is not a promise of zero interruption.
Backup restore	Restore into a separate destination; verify target time, row-level application checks and elapsed recovery time before reconnecting clients.

Full-cluster recovery needs a Patroni bootstrap and DCS plan as well as restored data. Starting Patroni against an arbitrary restored directory is not a complete recovery procedure. Agree recovery point and recovery time objectives, then retain the commands and results from your own rehearsal.

For an original acceptance record, use columns for fault, start time, last acknowledged transaction ID, first successful write, recovered transaction ID, result and recovery owner. This is a worksheet proposal, not a claim of a client test result.

Continue with our backup and recovery planning guide and RDS Proxy versus PgBouncer comparison.

Monitoring Your HA Cluster

You need visibility into replication lag, connection pool utilization, and failover events. Key metrics to track:

-- Replication lag (run on primary)
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;

-- Connection counts
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle' AND now() - pg_stat_activity.query_start > interval '30 seconds';

Choose exporters and alert expressions from the metric names your installed versions actually expose. Alert on cluster-wide absence of a writable leader, not on every replica reporting that it is not primary. Also cover exporter availability, retained WAL, archive failures, pool wait time and age of the last successful restore rehearsal.

Primary references and further reading

• Patroni replication and durability trade-offs
• PgBouncer feature compatibility
• pgBackRest backup and recovery procedures