Automatic failover cluster with Patroni + etcd + HAProxy

Builds and operates a PostgreSQL cluster with automatic failover: Patroni manages PostgreSQL on every node, leader election runs in a 3- (or 5-) member etcd, and applications connect to the HAProxy write (5000) and read (5001) ports, optionally through a keepalived VIP. Besides the installation there are operational scenarios for an existing cluster: health check, switchover, rejoining the old leader, reinit, adding/removing a node, edit-config, pause, Patroni upgrade, full shutdown/startup, etcd member replacement and loss of etcd quorum. The pg-install guide is not used on Patroni nodes (Patroni runs initdb itself); for HA without Patroni, with manual failover, use the pg-streaming guide.

Topology: 3 PostgreSQL + etcd nodes (shared or separate) + HAProxy (+ VIP)

Scenarios

• Installation: new Patroni cluster — Builds an etcd + Patroni + HAProxy (+ VIP) cluster from scratch. The servers must not have a PostgreSQL cluster: Patroni runs initdb (pg-install is not applied).
• Operations runbook: switchover, failover test, maintenance mode, parameter change — Runs through the basic operations on a newly built cluster in order: status, switchover, failover test (the leader is stopped, short write outage), maintenance mode, parameter change. Run it in a maintenance window.
• Is the cluster healthy? (health check) — Read-only: roles, lag, timeline, REST API, etcd health, slots, pending restart and maintenance mode.
• Planned switchover (move the leader to another node) — Before maintenance the leader role moves to a replica in a controlled way; a few seconds of write outage, no data loss.
• Failover happened, the old leader does not rejoin (pg_rewind / reinit) — When the crashed old leader comes back it should join as a replica; if pg_rewind fails, the data directory is rebuilt from the leader.
• Rebuild a replica from scratch (reinit) — The replica is corrupt, far behind or has lost WAL: its data directory is wiped and rebuilt from the leader (or from a backup). The leader is not affected.
• Add a new node to the cluster — Add the new node at the end of the list in the form: the software is installed, pg_hba and HAProxy are updated on the existing nodes, and the node joins as a replica.
• Remove a node from the cluster — A node that is a replica is removed permanently: Patroni is stopped, the DCS entry and the slot are cleaned up, and it is removed from HAProxy.
• Change a PostgreSQL or Patroni setting (edit-config) — The setting is written to the DCS and applied to all nodes; for settings that require a restart, restart --pending (replicas first).
• Stop automatic failover for maintenance (pause / resume) — While paused, Patroni does not touch PostgreSQL and does not fail over; for network/etcd maintenance or manual intervention. Resume when done.
• Upgrade the Patroni software (rolling) — The cluster is paused, Patroni is updated and restarted node by node; PostgreSQL keeps running, no failover happens.
• Stop / start the whole cluster (correct order) — Planned full shutdown (storage, data center maintenance): replicas first, then the leader, etcd last; startup in reverse order.
• Replace or add an etcd member (etcdctl member) — A failed etcd member is removed and a new server is added (or the member count is increased); Patroni's etcd host list is updated.
• etcd quorum lost: the leader became read-only — Without etcd quorum Patroni makes the leader read-only, and no failover happens either. etcd is brought back or rebuilt from a single member.

Before you start

• There is no running or previously created PostgreSQL cluster on the PostgreSQL nodes and … is empty (Patroni creates the cluster itself). The pg-install guide has not been applied on these servers.
• All servers run the same operating system release and are up to date; clocks are synchronized with NTP (etcd and Patroni are sensitive to clock skew).
• … GB RAM on the PostgreSQL nodes; … is on a separate disk/LV and the same size on all nodes.
• The etcd data (/var/lib/etcd) is on a low-latency disk (SSD). A slow disk disrupts leader elections; it is recommended not to share the disk with the I/O-heavy PostgreSQL data.
• Warning: the number of etcd nodes is … (even). An even number does not increase fault tolerance; use 3 or 5.
• The servers can reach each other on these ports: PostgreSQL …, Patroni REST 8008, etcd 2379/2380, HAProxy 5000/5001/7000…, VRRP (IP protocol 112)…. These rules are also open on the network devices in between.
• The servers can reach the PGDG repository (download.postgresql.org…, apt.postgresql.org…) and github.com for etcd over HTTPS, or an internal mirror is configured.
• The VIP … is free (not used on any server) and the network (virtualization/cloud) allows VRRP and moving the same IP between servers. Cloud environments usually do not allow this; use the cloud load balancer instead.
• Warning: there is only one HAProxy server for the VIP; if that server goes down, the VIP has nowhere to move.
• root or sudo access on all servers; a maintenance window and a fallback plan are ready.
• …: the same operating system and PostgreSQL major version as the other nodes can be installed, as much disk for … as the other nodes, clock synchronized with NTP; access to …, 8008 and etcd 2379 on the other nodes is open.
• … (…): low-latency disk (SSD), clock synchronized with NTP, access to 2379/2380 from the other etcd members and to 2379 from the PostgreSQL nodes is open; access to github.com or the etcd archive in /tmp.

Phases of the guide

• Preparation (all servers) — Add the server names to /etc/hosts · Enable time synchronization (chrony) · Kernel settings and THP · Firewall rules (PostgreSQL nodes) · Firewall rules (etcd servers) · Firewall rules (HAProxy servers)
• etcd cluster — Install the etcd software · Install the etcd software · etcd configuration: … · Create the etcd systemd service · Create the etcd systemd service · Start etcd (on all etcd nodes at the same time) …
• PostgreSQL and Patroni software — Add the PGDG repository · Add the PGDG repository · Install PostgreSQL and Patroni · Disable the standalone PostgreSQL service and prepare the directories
• Patroni configuration — Write the passwords to the environment file · Attach the environment file to the Patroni service · patroni.yml: … · Validate the configuration
• Start the cluster — Start Patroni on the first node · Start Patroni on the other nodes: … · Check the cluster status
• HAProxy — Install HAProxy · Install HAProxy · Write haproxy.cfg · Write haproxy.cfg · Validate and start HAProxy · Validate and start HAProxy
• VIP with keepalived — Install keepalived · Install keepalived · keepalived configuration: … · Start keepalived · Start keepalived
• Application user and connection test — Application user and database · Set the application user's password · Check the REST API health check endpoints · Test the connection through HAProxy · Application connection string
• Post-installation — Take an etcd snapshot backup · Take an etcd snapshot backup · Set up backups (mandatory) · Connect monitoring
• Cluster status — Members, roles and lag
• Planned switchover — Perform the switchover
• Failover test (stop the leader) — Stop Patroni on the leader node · Watch the new leader · Start the old leader again
• Maintenance mode (pause) — Put the cluster into maintenance mode · Perform the maintenance · Exit maintenance mode
• Parameter change — Change the parameter for the whole cluster · Apply the pending restarts
• Health check (read-only) — Members, roles, timeline and lag · Latest leader changes (history) · REST API health endpoints · etcd health · Replication lag, slots and pending restart (on the leader) · Configuration and maintenance mode
• Planned switchover — Pre-switchover check · Perform the switchover · Verify the new leader and the rejoin of the old leader
• Maintenance mode (pause / resume) — Pre-pause check · Put the cluster into maintenance mode (pause) · Perform the maintenance · Exit maintenance mode (resume)
• Patroni upgrade: preparation — Current and target Patroni version · Back up and validate the configuration · Put the cluster into maintenance mode (pause)
• Patroni upgrade: finish — Exit maintenance mode (resume)
• Stop the cluster (replica → leader → etcd) — Record the state and roles · Stop the applications · Stop Patroni on the replicas · Stop Patroni on the leader · Stop etcd
• Start the cluster (etcd → last leader → replicas) — Start etcd (on all etcd members at the same time) · Verify etcd health · Start Patroni on the last leader first · Start Patroni on the replicas · Verify the cluster and start the applications
• Diagnosis: loss of etcd quorum — Confirm the symptoms: no leader, PostgreSQL read-only · See the state of the etcd members
• Path A: bring the etcd members back — Path A: start the etcd services · Verify that Patroni has recovered
• Verification and prevention — Verify etcd and Patroni, consider failsafe