PostgreSQL High Availability Patterns

Analogy categoryAnalogies

What High Availability Means for PostgreSQL

High availability for PostgreSQL is measured against two concrete targets: Recovery Point Objective (RPO), the maximum acceptable amount of data loss measured in time or transactions, and Recovery Time Objective (RTO), the maximum acceptable time the database can be unavailable before service is restored. Streaming replication with synchronous commit can drive RPO close to zero, but RPO alone means nothing without an RTO story — a perfectly synchronized standby that no automated process promotes for twenty minutes while an on-call engineer is paged still represents twenty minutes of downtime, so HA design has to address detection, decision, and promotion, not just data redundancy.

🏏

Cricket analogy: It is like a team having a fully-trained reserve batsman ready in the pavilion (zero data loss potential) but no clear signal system to send them onto the field the instant the opener is injured — the match clock keeps running against the team regardless of how ready the reserve is.

Automated Failover Tools

Patroni is the most widely adopted PostgreSQL HA orchestrator: it runs as a daemon alongside each PostgreSQL instance, uses a distributed consensus store (etcd, Consul, or ZooKeeper) to elect and maintain a leader lock, continuously health-checks the primary, and automatically promotes the most up-to-date standby if the leader lock expires without renewal, rewriting each node's postgresql.conf as needed. repmgr takes a lighter-weight approach, providing tooling for cluster registration, monitoring, and manual or semi-automated promotion (repmgrd can watch for failure and trigger promotion) without requiring an external consensus store, trading some of Patroni's split-brain protection for operational simplicity.

🏏

Cricket analogy: Patroni is like a full match-referee panel using a formal DRS-style consensus protocol (three officials must agree) before overturning the on-field captain decision; repmgr is like a single senior umpire making the call directly based on their own judgment, simpler but with less cross-checking.

yaml

# Patroni configuration snippet (patroni.yml)
scope: prod-cluster
name: pg-node-1

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

postgresql:
  listen: 0.0.0.0:5432
  connect_address: pg-node-1:5432
  data_dir: /var/lib/postgresql/16/main
  authentication:
    replication:
      username: replicator
      password: '...'

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    postgresql:
      parameters:
        synchronous_standby_names: '*'

Load Balancing and Connection Routing

After a failover, applications need to reach whichever node is now the primary without manual reconfiguration; this is typically handled by an HAProxy layer configured to health-check each node's role (via Patroni's REST API, which reports leader/replica status) and route write traffic only to the current leader, or by a virtual IP that Patroni or Consul moves to the new primary, or by PgBouncer pointed at a routing layer rather than a static host. It's also common to route read-only traffic separately to standbys through the same HAProxy or a connection string using PostgreSQL's target_session_attrs=read-write/any option, letting reporting queries scale out without hitting the primary.

🏏

Cricket analogy: It is like a stadium's PA system automatically redirecting the captain's-armband microphone to whoever is currently wearing it, health-checked continuously, rather than the crowd having to be told manually every time captaincy changes hands.

target_session_attrs is a libpq connection parameter that lets a client connection string list multiple hosts and specify it only wants a node that is currently read-write (the primary) or, conversely, one that is read-only (a standby) — combined with multiple host entries, this gives applications basic failover-aware connection routing without any external proxy, though a dedicated router like HAProxy or Patroni's own routing is generally more robust for production.

Backup Strategy as Part of HA

Replication protects against hardware failure, but it is not a backup: a mistaken DROP TABLE or a corrupted row replicates to every standby just as faithfully as any legitimate write, and a bad application deploy that silently corrupts data will be preserved everywhere. Tools like pgBackRest or WAL-G take periodic base backups plus continuous WAL archiving, enabling point-in-time recovery (PITR) to any moment before a disaster, which is the only real defense against logical corruption or human error — a complete HA architecture needs both automated failover for availability and independently retained, tested backups for recoverability.

🏏

Cricket analogy: It is like having a backup stadium ready to host on short notice (failover) but that doesn't help if the actual problem was the groundskeeper mowing the wrong pitch pattern into both venues — you still need an independent archive of the correct pitch specifications to restore from, not just a second copy of the mistake.

Never assume a streaming or logical replica counts as a backup. Both propagate destructive changes (accidental drops, bad migrations, ransomware) just as reliably as legitimate ones. A resilient HA design keeps backups on independent storage with a retention window long enough to catch slow-to-notice corruption, and tests restores regularly — an untested backup is not a verified recovery capability.

HA design must satisfy both RPO (acceptable data loss) and RTO (acceptable downtime), not just data redundancy alone.
Patroni provides consensus-store-backed automated failover with strong split-brain protection; repmgr offers a lighter-weight alternative without an external consensus store.
Automated failover requires health checking, leader election, and promotion logic — not just a synchronized standby sitting idle.
Connection routing (HAProxy, virtual IPs, or target_session_attrs) is required so applications reach the new primary automatically after failover.
Read-only traffic can be routed to standbys separately from write traffic to scale out reporting/analytics workloads.
Replication is not a backup: destructive or corrupting changes replicate just as faithfully as legitimate ones.
pgBackRest/WAL-G with continuous WAL archiving enables point-in-time recovery, the only real defense against logical corruption or human error.