HA Basics

High availability for PostgreSQL means surviving primary loss with a defined recovery time (RTO) and acceptable data loss window (RPO). Apps must reconnect and retry through the failover window.

Recipe

Tier-0 OLTP target: 99.95% uptime, RTO < 60s, RPO < 30s with async physical standby + Patroni 3.x.

-- Verify standby is ready for promotion
SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS replay_delay;

# App connection string: target primary via proxy, not bare node IP
# postgresql://app:***@pg-primary.internal:5432/appdb?connect_timeout=5&application_name=checkout

When to reach for this: Any production database where minutes of write outage directly hits revenue or SLA credits.

Working Example

Define SLOs, measure failover budget, configure client retry:

-- On standby: replay freshness before drill
SELECT CASE
  WHEN pg_last_xact_replay_timestamp() IS NULL THEN 'no replay yet'
  ELSE (now() - pg_last_xact_replay_timestamp())::text
END AS replay_age;

# Patroni 3.x: check cluster leader (from ops host)
curl -s http://patroni-node1:8008/cluster | jq '.members[] | {name, role, state}'

# Application retry pattern (psycopg) - simplified
import time
import psycopg
 
for attempt in range(5):
    try:
        with psycopg.connect(conninfo, connect_timeout=5) as conn:
            conn.execute("SELECT 1")
            break
    except psycopg.OperationalError:
        time.sleep(2 ** attempt)

What this demonstrates:

• HA is a system property: PostgreSQL + standby + orchestration + proxy + app retries.
• Replay delay on standby bounds async RPO before promotion.
• Clients must use timeouts and exponential backoff through DNS/proxy cutover.

Deep Dive

Core Metrics

HA Stack Layers

Uptime Math

99.9% allows ~43 minutes downtime per month. 99.95% allows ~22 minutes. Failover drills must prove you stay inside the budget including app cold start.

Gotchas

• Hard-coded primary IP in apps - Failover succeeds but apps stay down. Fix: Proxy hostname or service discovery.
• No app retry on 57P01 / connection reset - Brief blip becomes user-visible outage. Fix: Retry transient errors for idempotent reads and safe writes.
• PgBouncer without server lifetime - Stale connections to dead primary. Fix: server_lifetime + health checks; pause/resume during failover.
• Standby too far behind - Promote with large RPO. Fix: Byte lag alerts before declaring HA ready.
• HA without tested runbook - Theoretical uptime only. Fix: Quarterly Patroni failover game day.

Alternatives

FAQs

Related

• Patroni & etcd/Consul - automated failover
• Connection Proxies - routing during failover
• HA Testing - game-day drills
• DR Basics - RPO/RTO tiers

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.