PostgreSQL High-Availability — Operations Skill

You are helping operate or design a production PostgreSQL HA cluster. The reference stack (from the team’s real clusters) is PostgreSQL 17 + Patroni + a 3-member etcd quorum + dual HAProxy + Keepalived VRRP VIP + PgBouncer + pgBackRest. Answer from this knowledge; for depth, read the bundled guides:

• reference/PostgreSQL-HA-Production-Install-Guide.md — full install + hardening, with commands.
• reference/Database-Tender-NFR-Responses.md — tender / NFR answers, ISO 25010, no commands.

The mental model (say it in one breath)

Applications → Keepalived VIP → HAProxy (health-checks Patroni REST /primary on :8008, forwards to PgBouncer :6432) → PostgreSQL primary. Patroni holds a leader lock in etcd and promotes a replica automatically on primary loss. Write endpoint = VIP:5432 (/primary); read endpoint = VIP:5433 (/replica). Patroni is the supervisor that starts PostgreSQL — if Patroni is stuck, PostgreSQL never starts.

⚠️ The two golden rules (never violate)

1. Never trust a Layer-4 / TCP health check as a database health signal. A proxy in front of a dead PostgreSQL still accepts TCP, so an L4 check reports the backend UP during a total outage. This exact trap hid a 12-day production outage. Always probe the real PostgreSQL protocol:

   Send an SSLRequest — 8 bytes: length 8 + code 80877103. A healthy server replies S (TLS) or N (no TLS). An EOF / immediate close means HAProxy has no healthy backend. One-liner:

   import socket,struct
   s=socket.create_connection((HOST,5432),5); s.sendall(struct.pack('!ii',8,80877103))
   print(s.recv(1))   # b'S'/b'N' = alive ; b'' = no backend

2. No bulk cluster-wide mutations. PV/reclaim/one-off destructive DB ops go one at a time, with a backup first and verification between each. On this team’s clusters the order of operations for storage is always backups → Retain → then GitOps.

Diagnosis decision tree (start here for any DB incident)

App reports "connection failed" / EOFException in enableSSL / CrashLoopBackOff on Liquibase startup
│
├─ 1. Probe the VIP with the SSLRequest (rule 1). Alive (S/N)? → problem is app-side or network, not the DB.
│      EOF? → the database layer has no backend. Continue.
│
├─ 2. HAProxy stats:  curl -s -u <user>:<pass> http://<lb>:7000/;csv   → which pgsql-* are DOWN, since when?
│      (L7STS = Patroni says not-a-leader/replica; L4CON "Connection refused" = PgBouncer/PG not listening)
│
├─ 3. On a DB node (SSH):  patronictl -c /etc/patroni/patroni.yml list
│      • Empty cluster / no Leader  → Patroni cannot register. Check journalctl -u patroni (step 4).
│      • Leader present, replicas "start failed" → divergent timeline (see runbook B).
│
├─ 4. journalctl -u patroni -n 50   → the root cause is almost always here. Common:
│      • "etcdserver: mvcc: database space exceeded" / "waiting on etcd"  → etcd NOSPACE (runbook A).
│      • "requested timeline N is not a child of this server's history"   → divergent replica (runbook B).
│
└─ 5. Port fingerprint the DB nodes (distinguish RST vs filtered):
       5432 (PostgreSQL) · 6432 (PgBouncer) · 8008 (Patroni REST).
       PgBouncer up but 5432+8008 refused = PostgreSQL not started + Patroni not managing → runbook A/C.

Incident runbooks (rehearsed, real)

Runbook A — etcd NOSPACE (the 12-day-outage root cause)

Symptom: patronictl list empty, journalctl -u patroni shows mvcc: database space exceeded, etcdctl endpoint status shows DB size at/near the 2 GiB quota with a NOSPACE alarm. Cause: etcd has no auto-compaction-retention; revision history grew until it hit the quota and went read-only, so Patroni cannot renew its lease → no leader → PostgreSQL never starts. Fix (order matters):

1. Back up first: etcdctl snapshot save.
2. etcdctl compact <current-revision> --physical
3. etcdctl defrag on each member, one at a time (it blocks that member ~seconds; do the leader last).
4. etcdctl alarm disarm — only after compact+defrag. Result seen live: 2.1 GB → 78 kB, Patroni re-acquired the lease and PostgreSQL came up in seconds. Permanent fix (needs root on the DB VMs): set in /etc/etcd/etcd.yaml on every member: auto-compaction-mode: periodic, auto-compaction-retention: "8h", quota-backend-bytes: 8589934592, then rolling systemctl restart etcd. Alert on etcd_mvcc_db_total_size_in_bytes vs quota. As a stop-gap without root, an hourly etcdctl compact cron works (compaction is cluster-wide).

Runbook B — divergent-timeline replica

Symptom: patronictl list shows the Leader running but replicas start failed; Patroni log: FATAL: requested timeline N is not a child of this server's history. Cause: the replica’s WAL diverged from the leader’s (e.g. months of an unavailable DCS). PostgreSQL correctly refuses to follow a timeline that isn’t its ancestor. Fix: re-initialise the replica from the leader — one at a time: patronictl -c /etc/patroni/patroni.yml reinit postgres <replica>. It runs a full pg_basebackup; the leader holds the good data (verify with a backup first). Do not run two reinits at once — the primary is serving all traffic. The database runs with no redundancy until replicas rejoin, so this is high priority.

Runbook C — no leader / DCS quorum loss

Symptom: no /primary answers 200; patronictl list empty or all Replica. Steps: (1) etcdctl endpoint health --cluster — is etcd healthy? Fix etcd first. (2) If etcd is fine, journalctl -u patroni on each node. (3) With failsafe_mode: true a healthy primary keeps serving during a DCS blip. (4) If Patroni is functional, start it on one node and let it establish a leader before the others (avoids split brain).

Runbook D — one node down, maintenance

Reboot replicas freely. Before patching the primary, do a controlled patronictl switchover. Use patronictl pause for any planned stop so Patroni doesn’t auto-failover mid-work; always resume and confirm a healthy leader + streaming replicas after.

Read-only diagnostics you can run safely (monitoring mode)

# via SSH as the DB admin user; all read-only
patronictl -c /etc/patroni/patroni.yml list                 # cluster topology + roles + lag
etcdctl endpoint status -w table --cluster                  # etcd size / leader / alarms
etcdctl alarm list                                          # NOSPACE etc.
systemctl status patroni etcd pgbouncer                     # postgresql shows inactive/disabled UNDER Patroni (normal!)
journalctl -u patroni -n 100 --no-pager
psql -h 127.0.0.1 -U postgres -tAc "select application_name,state,sync_state from pg_stat_replication;"
curl -s -u <user>:<pass> http://<lb>:7000/;csv              # HAProxy backend truth

⚠️ Two things that look wrong but are normal under Patroni: systemctl status postgresql shows inactive/disabled (Patroni manages PostgreSQL directly), and Patroni’s REST :8008 being refused from outside is fine — it’s not bound externally. Do not conclude “Patroni is dead” from a refused :8008 — that mistake was made once; check systemctl/patronictl instead.

Backup & PITR (replication is NOT backup)

Replication propagates a DROP TABLE, corruption or ransomware to every replica instantly. Only pgBackRest with continuous WAL archiving gives Point-In-Time Recovery. A one-off pg_dump on the DB node is a useful supplementary export, not a backup strategy (not scheduled, not off-host, not PITR, not restore-tested). Enforce 3-2-1-1-0 (≥3 copies, 2 media, 1 off-site, 1 immutable/Object- Lock, 0 restore errors) and test restores (monthly restore, quarterly timed DR drill). Details in the install guide §10.

Durability decision (do it with the client)

• RPO = 0 → Patroni synchronous_mode (+synchronous_mode_strict). Trade-off: strict mode refuses writes if no sync standby → reconcile with the write-availability SLA (quorum sync, or an agreed carve-out). Never claim RPO=0 while running async.
• Latency-optimised → async + maximum_lag_on_failover → a small, stated-in-seconds RPO.

Key hardening gaps to flag (common on inherited clusters)

password_encryption: md5 → scram-sha-256; no WAL archiving → add pgBackRest/PITR; etcd without auto-compaction (Runbook A); use_slots without max_slot_wal_keep_size → a dead slot fills pg_wal → read-only FS → DB down; shared admin SUPERUSER + plaintext passwords → least-privilege + a secrets manager; no monitoring/alerting → the reason outages go unnoticed for days.

For tender / NFR questions

Answer from reference/Database-Tender-NFR-Responses.md using the three-move pattern: restate the requirement → state the mechanism → state the measurable evidence. Keep targets honest (99.95–99.99 % for a 3-node cluster, never reflexive five-nines); scope the SLA to the DB endpoint; lead with evidence (drill reports, SLO dashboards, restore logs), not adjectives.