It’s 3:00 AM. Your phone buzzes violently against the nightstand. PagerDuty is screaming about a primary database node dropping off the grid, and your heart rate spikes because you know what’s coming next: a manual scramble to promote a read replica, fix broken streaming replication chains, and pray to the PostgreSQL gods that you haven’t introduced a split-brain condition that just silently corrupted your company’s transactional ledger. We’ve all been there. Choosing a high availability framework for self-hosted PostgreSQL isn’t just an architectural checkbox; it’s an insurance policy against career-defining midnight outages. You need something robust, predictable, and battle-tested under fire.

What Makes PostgreSQL High Availability So Hard to Get Right in Self-Hosted Environments?

PostgreSQL is arguably the most reliable relational database engine on the planet, but it doesn’t ship with an opinionated, built-in high availability engine out of the box. Out of the box, you get streaming replication: a primary writes WAL (Write-Ahead Log) records, and one or more standbys consume them. Simple, elegant, and fragile. The moment the primary drops, replication halts, and your applications hit brick walls. Building an HA wrapper around PostgreSQL means solving three brutally difficult distributed systems problems: detecting failures reliably, electing a single new leader without human intervention, and re-wiring the surviving nodes so they don’t fight over who owns the write lock.

This is where engineers step on architectural landmines. The most common trap is relying on simple ping checks or heartbeat scripts running from application servers. Network jitter, brief CPU spikes under heavy analytics queries, or a momentary blip in your top-of-rack switches can cause a monitoring script to falsely assume the primary is dead. If that script triggers a promotion while the old primary is still happily accepting writes on an isolated network segment, you’ve just created a split-brain. Two nodes accept conflicting writes, data diverges irreversibly, and your Monday morning is spent writing custom SQL merge scripts while executives hover over your desk. True HA frameworks must eliminate this ambiguity through rigorous consensus.

How Does Patroni Leverage Distributed Consensus to Guarantee Zero Split-Brain Incidents?

Patroni approaches high availability by shifting the source of truth out of PostgreSQL and into a Distributed Configuration Store (DCS) like etcd, Consul, or ZooKeeper. Patroni runs as a daemon process alongside every PostgreSQL instance on your cluster nodes. Every few seconds, the Patroni agent running on the current primary node renews a time-to-live (TTL) leader lock inside the DCS. As long as the primary is healthy, has network connectivity, and its local agent can heartbeat into etcd, it retains the crown.

If that primary node experiences a kernel panic or loses its network uplink, its etcd lease expires. The surviving standby nodes observe the expired lease, run an election process through the DCS consensus algorithm, and the node that secures the lock signals its local PostgreSQL instance to execute a safe promotion. Because etcd enforces strict linearizable consistency, two nodes can never hold the leader key simultaneously. This architectural design completely eliminates split-brain risks, provided your DCS quorum is deployed across fault-tolerant infrastructure.

Here is a production-tested Patroni configuration template using etcd as the backing DCS:

scope: postgres-ha-cluster
namespace: /service
name: pg-node-01

etcd3:
  hosts:
    - 10.0.1.10:2379
    - 10.0.1.11:2379
    - 10.0.1.12:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        max_connections: 200
        shared_buffers: 4GB
        checkpoint_completion_target: 0.9
        wal_level: replica

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.10:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  authentication:
    replication:
      username: repl_user
      password: "SuperSecureReplicationPassword123!"
    superuser:
      username: postgres
      password: "SuperSecureAdminPassword123!"

tags:
  nofailover: false
  noloadbalance: false
  nosync: false

When Should You Choose pg_auto_failover Over Patroni for Virtualized or Bare-Metal Fleets?

While Patroni is a powerhouse, running etcd or Consul introduces significant operational overhead. You have to monitor the health of your DCS cluster, manage TLS certificates for etcd peer communication, and handle quorum loss scenarios if your DCS nodes span multiple datacenters. If you are managing a simpler infrastructure topology—say, three bare-metal servers or cloud VMs—maintaining a separate DCS can feel like bringing a sledgehammer to a finish carpentry job.

Enter pg_auto_failover, developed by Microsoft (Citus Data). Instead of relying on a distributed key-value store, pg_auto_failover uses a dedicated, lightweight monitor node. The monitor node is not a database instance; it is a minimal C application that periodically polls the primary and secondary PostgreSQL nodes via SQL queries and connection checks. The architecture relies on three nodes: a primary, a secondary, and the monitor. The monitor assigns node states (primary, secondary, wait_primary) and instructs PostgreSQL directly when to promote a standby using standard streaming replication settings.

This design dramatically simplifies day-2 operations. There is no etcd cluster to maintain, no Raft consensus tuning for KV stores, and the entire setup can be initialized with a couple of CLI commands. However, the trade-off is architectural centralization: if your monitor node goes down, automatic failover pauses until the monitor is restored or a failover is manually forced. For teams that want robust automated failover without adopting the full Kubernetes ecosystem stack, pg_auto_failover hits a sweet spot of simplicity and reliability.

To configure secure streaming replication under pg_auto_failover, your pg_hba.conf on the primary database instance must explicitly allow connections from your secondary and monitor nodes using SCRAM-SHA-256 authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Local administrative access
local   all             postgres                                peer

# Allow replication connections between cluster nodes
host    replication     pg_auto_config  10.0.1.0/24             scram-sha-256

# Allow application traffic to the database
host    all             all             10.0.2.0/24             scram-sha-256

# Allow monitor node to query database state and health
host    all             pg_auto_config  10.0.1.50/32            scram-sha-256

How Do You Execute a Failover Disaster Recovery Test Procedure Without Causing Data Loss?

Deploying an HA framework without testing failure scenarios is an invitation for a disaster during peak production hours. You cannot trust an HA setup until you have deliberately pulled the rug out from under your primary database and verified that RTO stays under 10 seconds and RPO remains at zero. Here is a concrete, 4-step disaster recovery test playbook you can execute in a staging environment today.

1. Verify Synchronous Replication State: Before initiating any test, check that your replication stream is fully caught up. Run pg_stat_replication on your primary node and ensure the standby state reads streaming and the lag is zero bytes.
2. Simulate a Primary Hard Failure: Rather than performing a graceful shutdown (which triggers clean handoffs), simulate a catastrophic failure by blocking network traffic via iptables or executing a hard power cut on the primary VM hypervisor level: sudo iptables -A INPUT -p tcp --destination-port 5432 -j DROP.
3. Observe Automated Failover & DCS/Monitor Action: Watch your Patroni logs (journalctl -u patroni -f) or pg_auto_failover monitor logs. Verify that the system detects the loss of heartbeat, the TTL or timeout expires, and a new leader is elected and promoted within your target Recovery Time Objective.
4. Validate Data Integrity & pg_rewind Recovery: Reconnect the old primary node to the network, clear your iptables rules, and observe how Patroni or pg_auto_failover handles the former primary. Using pg_rewind, the old primary should automatically sync its timeline with the new leader, demote itself cleanly to a standby, and rejoin the replication cluster without manual intervention.

Frequently Asked Questions

Can I run Patroni without Kubernetes?
Yes. While Patroni is heavily associated with Kubernetes operators (like CloudNativePG or Zalando’s operator), it runs natively as a systemd service on standard Linux virtual machines or bare-metal servers, backed by an external etcd or Consul cluster.

What happens to my application connections during a Patroni failover?
Applications connected directly to the failed primary will experience a dropped connection and must handle retries. In production, you should always place a connection router like HAProxy, PgBouncer, or Patroni’s built-in REST API-backed service discovery in front of your database nodes to route traffic seamlessly to the new primary.

Is pg_auto_failover suitable for multi-region active-passive deployments?
No. pg_auto_failover is designed primarily for single-region, high-availability deployments where latency between the primary, secondary, and monitor nodes is extremely low. For multi-region topologies with asynchronous WAN links, Patroni paired with custom DCS placement policies offers much safer configuration flexibility.