PostgreSQL High Availability: Patroni, repmgr, and Failover

In today's always-on world, database downtime can cost millions. PostgreSQL, while robust, is not immune to hardware failures, network partitions, or software crashes. High Availability (HA) ensures your database remains accessible even when a node fails. This tutorial dives into two popular PostgreSQL HA solutions: Patroni and repmgr. Patroni, built on distributed consensus (etcd, Consul, or Zookeeper), provides automatic failover and self-healing clusters. repmgr offers a simpler, more manual approach with robust replication management. We'll explore their architectures, setup, failover mechanics, and production pitfalls. By the end, you'll be able to design and operate a PostgreSQL cluster that survives node failures with minimal downtime. We'll also dissect a real-world outage caused by a misconfigured fencing mechanism, teaching you what not to do. Whether you're a DBA or a developer responsible for database reliability, this guide provides actionable insights for building resilient PostgreSQL deployments.

1. Understanding PostgreSQL High Availability Concepts

High Availability (HA) for PostgreSQL involves ensuring the database remains accessible despite node failures. Key concepts include: replication (streaming or logical), failover (automatic or manual), quorum (majority of nodes agree on primary), and fencing (isolating failed nodes to prevent split-brain). Patroni uses a distributed configuration store (etcd, Consul, Zookeeper) to maintain cluster state and elect a leader. repmgr relies on a dedicated monitoring database and manual or cron-driven failover. Both support synchronous and asynchronous replication. Synchronous replication guarantees zero data loss but impacts write performance. Asynchronous replication may lose some transactions during a crash but offers better performance. Understanding these trade-offs is crucial for designing an HA solution that meets your RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

-- Check replication status on primary
SELECT client_addr, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

2. Setting Up Patroni with etcd

Patroni orchestrates PostgreSQL instances into a highly available cluster. It uses a distributed key-value store like etcd to store cluster state and leader election. To set up Patroni: install Patroni on each node, configure etcd, and create a Patroni configuration file (patroni.yml). The configuration defines the cluster name, PostgreSQL parameters, replication settings, and etcd endpoints. Patroni manages PostgreSQL via a REST API and automatically handles failover, replication, and recovery. Below is a minimal patroni.yml for a three-node cluster. After starting Patroni on each node, the cluster will elect a primary and replicas will stream from it.

scope: mycluster
namespace: /db/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.0.1:8008

etcd:
  host: 10.0.0.10:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 100
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 8
        max_wal_senders: 5
        max_replication_slots: 5
        wal_log_hints: "on"

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.0.1:5432
  data_dir: /var/lib/pgsql/data
  bin_dir: /usr/pgsql/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: rep-pass
    superuser:
      username: postgres
      password: admin-pass
    rewind:
      username: rewind_user
      password: rewind-pass
  parameters:
    unix_socket_directories: /var/run/postgresql

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

3. Managing Failover with Patroni

Patroni automatically handles failover when the primary becomes unreachable. It uses a leader election algorithm based on etcd's distributed locks. When the primary fails, Patroni on the replicas detect the loss of the leader lock and hold an election. The replica with the most recent WAL position becomes the new primary. You can also trigger manual failover using patronictl failover. Patroni supports switchover (planned failover) for maintenance. During failover, Patroni ensures that the old primary is fenced (e.g., via pg_ctl stop or hardware watchdog) before promoting a new primary to prevent split-brain. The failsafe_mode option can be enabled to require a majority of etcd nodes for promotion, adding an extra safety net.

# List cluster members
patronictl -c /etc/patroni.yml list

# Manual failover to a specific candidate
patronictl -c /etc/patroni.yml failover --master node1 --candidate node2

# Planned switchover
patronictl -c /etc/patroni.yml switchover --master node1 --candidate node2

# Check cluster history
patronictl -c /etc/patroni.yml history

4. Introduction to repmgr

repmgr (Replication Manager) is a simpler alternative to Patroni. It manages PostgreSQL replication and provides tools for monitoring, failover, and switchover. repmgr uses a dedicated database (repmgr metadata) to track cluster state. It supports both synchronous and asynchronous replication. Unlike Patroni, repmgr does not use distributed consensus; it relies on a single monitoring database. Failover can be manual or automated via cron jobs or event triggers. repmgr is easier to set up but lacks the automatic self-healing and quorum-based decisions of Patroni. It's suitable for smaller deployments or teams that prefer a more hands-on approach.

-- Register a standby node with repmgr
SELECT repmgr_standby_register(
    node_name => 'node2',
    conninfo => 'host=10.0.0.2 port=5432 user=repmgr dbname=repmgr'
);

-- Check cluster status
SELECT * FROM repmgr.nodes;

-- Perform a failover (on the standby that should become primary)
SELECT repmgr_failover();

5. Fencing and Split-Brain Prevention

Fencing (also called STONITH - Shoot The Other Node In The Head) is critical to prevent split-brain scenarios where two nodes both think they are primary. In Patroni, fencing is achieved by using a watchdog device (e.g., Linux watchdog) or by issuing a pg_ctl stop command to the failed primary. repmgr relies on manual intervention or external fencing mechanisms. Without proper fencing, a network partition can lead to data corruption. Best practices: enable STONITH via hardware watchdog or IPMI, configure Patroni's failsafe_mode, and use synchronous replication to minimize data loss. Always test fencing by simulating network partitions.

# Install watchdog daemon
sudo yum install watchdog -y

# Configure watchdog to use hardware watchdog (e.g., /dev/watchdog)
echo "watchdog-device = /dev/watchdog" | sudo tee -a /etc/watchdog.conf
echo "watchdog-timeout = 10" | sudo tee -a /etc/watchdog.conf

# Start watchdog
sudo systemctl enable watchdog
sudo systemctl start watchdog

# In Patroni configuration, enable watchdog
tags:
  watchdog: true

6. Monitoring and Alerting for HA Clusters

Monitoring is essential for maintaining a healthy HA cluster. Key metrics to monitor: replication lag, cluster state (primary/replica), etcd health, and Patroni API status. Use tools like Prometheus with the postgres_exporter and Patroni exporter. Set up alerts for replication lag exceeding thresholds, node failures, and etcd leader changes. Patroni exposes a REST API at /cluster that returns JSON with cluster status. You can integrate this with your monitoring stack. repmgr provides command-line tools like repmgr cluster show and repmgr standby follow. Additionally, monitor PostgreSQL logs for errors related to replication or failover.

# Check Patroni cluster health via API
curl -s http://10.0.0.1:8008/cluster | jq .

# Example output
{
  "members": [
    {
      "name": "node1",
      "role": "leader",
      "state": "running",
      "api_url": "http://10.0.0.1:8008",
      "host": "10.0.0.1",
      "port": 5432,
      "timeline": 1
    },
    {
      "name": "node2",
      "role": "replica",
      "state": "running",
      "api_url": "http://10.0.0.2:8008",
      "host": "10.0.0.2",
      "port": 5432,
      "timeline": 1,
      "lag": 0
    }
  ]
}

7. Zero-Downtime Upgrades and Maintenance

One of the benefits of HA is the ability to perform upgrades without downtime. For PostgreSQL minor version upgrades, you can use a rolling upgrade: failover to a replica, upgrade the old primary, then fail back. For major version upgrades, use logical replication or tools like pg_upgrade with Patroni's switchover. Patroni supports switchover for planned role changes. repmgr also provides repmgr standby switchover. Always test upgrades in a staging environment. During maintenance, ensure that the application connection strings are updated to point to the new primary if using a connection pooler like PgBouncer or HAProxy.

# Perform a rolling upgrade of PostgreSQL minor version
# 1. Switchover to replica (node2)
patronictl -c /etc/patroni.yml switchover --master node1 --candidate node2

# 2. Upgrade node1 (old primary) to new version
sudo yum update postgresql*
sudo systemctl restart postgresql

# 3. Wait for node1 to rejoin as replica
patronictl -c /etc/patroni.yml list

# 4. Switch back if desired
patronictl -c /etc/patroni.yml switchover --master node2 --candidate node1