PostgreSQL High-Availability — Production Installation Guide

Acest conținut nu este încă disponibil în limba selectată.

Reusable engineering playbook. Reference architecture: 3× PostgreSQL 17 + Patroni, backed by a dedicated odd-numbered etcd quorum, fronted by dual HAProxy + Keepalived VRRP, with PgBouncer pooling and pgBackRest for backups/PITR. Grounded in the live ChisinauGaz cg-stage database tier (docs/CG-Stage/Infrastructure/Database HA (Patroni).md) and hardened against the gaps that caused its 12-day outage. Version-neutral where possible; concrete for PostgreSQL 17 / Ubuntu 24.04.

Audience: engineers building or rebuilding a production PostgreSQL cluster. Companion: [[Database-Tender-NFR-Responses]] — the same architecture expressed as tender answers. Status of this doc: living reference. Last revised 2026-07-25.

0. How to read this guide

Each section states the decision, why it matters, and how to do it. Commands are the proven form for the reference stack; treat versions/paths as parameters. Boxes marked ⚠️ Lesson are failure modes we have actually seen on the cg-stage cluster — they are the reason a step exists.

The single most important idea: an HA database degrades quietly long before it fails loudly. A lost sync standby, a lagging replica, an etcd near its quota, an unarchived WAL segment, an expiring certificate — each is invisible without monitoring, and each is a future outage. Build the monitoring and the fencing first, not last.

1. Architecture, topology and prerequisites

Reference topology

                          ┌─────────────── clients / applications ───────────────┐
                          │  writes → VIP:5432        reads → VIP:5433            │
                          └───────────────────────┬──────────────────────────────┘
                                                  │  Keepalived VRRP VIP
                         ┌────────────────────────┴────────────────────────┐
                         ▼                                                  ▼
                   HAProxy (lb1)   ◀── VRRP MASTER/BACKUP ──▶        HAProxy (lb2)
                         │   httpchk GET /primary,/replica on :8008         │
              ┌──────────┼──────────────────────────────────────┬──────────┘
              ▼          ▼                                       ▼
        PgBouncer   PgBouncer                               PgBouncer          (:6432 on each DB node)
              │          │                                       │
         PostgreSQL  PostgreSQL                             PostgreSQL         (:5432, 1 primary + 2 replicas)
         Patroni     Patroni                                Patroni           (:8008 REST)
              └──────────┴─────────────── etcd quorum (3 or 5) ──┘            (:2379/:2380)

        pgBackRest repo  ──▶  off-host / off-site object storage (immutable, encrypted)
        Prometheus + Alertmanager + Grafana  ──▶  pages a human

Fault-tolerance matrix — an N-member etcd quorum tolerates floor((N-1)/2) member losses:

etcd members	tolerates	verdict
1	0	dev only
2	0	❌ never — worse than 1 node; losing either freezes the cluster
3	1	✅ standard
4	1	❌ same tolerance as 3, more cost + split-vote risk
5	2	✅ when a two-failure budget is required, or etcd is decoupled from the DB nodes

⚠️ Lesson (CG-Stage inventory). The spec listed etcd on 2 load-balancer nodes; the live cluster actually runs a 3-member etcd co-located on the DB nodes — the correct choice. Never run 2.

No single point of failure

Every tier must survive one loss: 3 DB nodes, an odd etcd quorum, two HAProxy behind a VRRP VIP, and applications that point only at the VIP (never at a node IP). Place etcd on independent fault domains from the DB data where the budget allows — a 5-member etcd decoupled from the DB nodes is the stronger posture for a mission-critical ERP.

Storage layout — a gating prerequisite

/           OS
/var/lib/postgresql/17/main   PGDATA        (NVMe/SSD)
/pg_wal                        pg_wal        SEPARATE low-latency device
/var/lib/etcd                  etcd          SEPARATE low-latency device, never shared with the DB
/var/lib/pgbackrest  (or S3)   backup repo   SEPARATE, off-host preferred

⚠️ Lesson (recurring root cause). When OS + WAL + data + backups share one thin volume, a pg_wal fill (or an unbounded replication slot — see §5) fills /, the filesystem flips read-only, and PostgreSQL dies with “cannot write” errors. This is the chronic incident pattern on the Esempla cluster (CLAUDE.md) and the disk-headroom risk on cg-stage (CG-014). Separate volumes are not optional. On cg-stage the nodes present ~48 GiB usable against a 100 GB spec — resolve that (CG-014) before treating the capacity plan as met.

OS prerequisites (each node)

# Transparent Huge Pages OFF (THP causes latency spikes for PostgreSQL)
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
# make persistent via GRUB: transparent_hugepage=never  (then update-grub + reboot)

# Explicit huge pages for shared_buffers (see §3 for nr_hugepages sizing)
# I/O scheduler for SSD/NVMe
echo mq-deadline | sudo tee /sys/block/nvme0n1/queue/scheduler

# Time sync — clock skew breaks TLS validity windows, log correlation and etcd leases
sudo apt-get install -y chrony && systemctl enable --now chrony
chronyc tracking     # verify offset is small and stable

# vm.swappiness low, tune kernel semaphores per PostgreSQL docs
sudo sysctl -w vm.swappiness=1

⚠️ Add (critique). Clock sync is not cosmetic: NTP skew across nodes breaks certificate validity windows, Patroni/etcd lease timing and log correlation. Monitor chrony offset.

CG-Stage delivery caveat

The cg-stage DB VMs (10.50.26.5/.6/.7, HAProxy/Keepalived on .14/.15, VIP .150) live outside Kubernetes; we reach them by SSH as dbvmadm (no passwordless sudo) and the k8s cluster is kubectl-only. Deliver every change here as a reviewed, client-executed runbook, not a self-applied change — and log it to docs/CG-Stage/Operations/kubectl-history.md tagged [MUT].

2. etcd — the DCS, deployed and hardened first

Patroni stores its leader lock in etcd. If etcd is unhealthy, the whole cluster is unhealthy — so build and validate etcd before layering Patroni on top.

Install and form the quorum

# On each of the 3 (or 5) members — /etc/etcd/etcd.yaml
name: "pgsql-m"
data-dir: "/var/lib/etcd"
listen-peer-urls: "https://10.50.26.5:2380"
listen-client-urls: "https://10.50.26.5:2379,https://127.0.0.1:2379"
advertise-client-urls: "https://pgsql-m:2379"
initial-advertise-peer-urls: "https://pgsql-m:2380"
initial-cluster: "pgsql-m=https://pgsql-m:2380,pgsql-s1=https://pgsql-s1:2380,pgsql-s2=https://pgsql-s2:2380"
initial-cluster-token: "<unique-per-cluster>"
initial-cluster-state: "new"
heartbeat-interval: 100        # ms — raise if disk/network latency is high (see Lesson)
election-timeout: 1000         # ms — must be >= 5× heartbeat-interval

# ─── HARDENING — the three settings whose absence caused the CG-001 outage ───
auto-compaction-mode: periodic
auto-compaction-retention: "8h"          # compact history older than 8h automatically
quota-backend-bytes: 8589934592          # 8 GiB explicit quota (default is ~2 GiB — a cliff)

# ─── TLS + auth (critique: an unauthenticated etcd = full-cluster takeover) ───
client-transport-security:
  cert-file: /etc/etcd/pki/server.crt
  key-file:  /etc/etcd/pki/server.key
  trusted-ca-file: /etc/etcd/pki/ca.crt
  client-cert-auth: true
peer-transport-security:
  cert-file: /etc/etcd/pki/peer.crt
  key-file:  /etc/etcd/pki/peer.key
  trusted-ca-file: /etc/etcd/pki/ca.crt
  client-cert-auth: true

Validate the quorum before Patroni:

etcdctl --endpoints=pgsql-m:2379,pgsql-s1:2379,pgsql-s2:2379 endpoint health --cluster
etcdctl --endpoints=... endpoint status -w table   # one stable leader, low DB size
etcdctl member list -w table

Operational hygiene (schedule this — do not rely on a human remembering)

# Compaction is cluster-wide (run on one member). Defrag is per-member and STOP-THE-WORLD —
# it briefly blocks that member, so roll it one at a time and avoid the leader.
etcdctl compact "$(etcdctl endpoint status -w json | jq -r '.[0].Status.header.revision')" --physical
for m in pgsql-s1 pgsql-s2 pgsql-m; do          # leader last
  etcdctl --endpoints=$m:2379 --command-timeout=600s defrag
done
etcdctl alarm disarm      # clears a NOSPACE alarm AFTER compact+defrag

# etcd DISASTER RECOVERY — Patroni can rebuild Postgres, but if etcd loses quorum you need this:
etcdctl snapshot save /backup/etcd-$(date -u +%Y%m%dT%H%M%SZ).db     # schedule daily, off-host
# restore: etcdctl snapshot restore <file> ... then re-form the cluster (documented runbook)

⚠️ Lesson (CG-001, the 12-day outage). etcd accumulated 4.97 million revisions for 4 actual keys — 2.1 GB — because auto-compaction-retention was never set. It hit the 2 GiB quota, raised a NOSPACE alarm, went read-only, Patroni could not renew its lease → no leader → PostgreSQL never started, for 12 days. The fix took minutes (compact → defrag → disarm: 2.1 GB → 78 kB). The permanent guard is the three hardening lines above plus an alert on DB-size-vs-quota.

⚠️ Lesson (CG-011). Slow etcd disk shows as apply request took too long in the log and causes rolling control-plane probe failures / flapping. Alerting is necessary but not sufficient — a genuinely slow disk needs heartbeat-interval/election-timeout widened or it will flap. Put etcd on a low-latency disk that is never shared with the database.

Scrape/alert: etcd_mvcc_db_total_size_in_bytes vs etcd_server_quota_backend_bytes (alert

95 %), WAL fsync p99 < 10 ms, backend commit p99 < 25 ms, leader changes rare, proposal failures = 0.

3. PostgreSQL 17 base install and performance tuning

initdb — decisions you cannot cheaply reverse

# --data-checksums: detects silent corruption. Can only be added later with FULL downtime
#   (pg_checksums on a stopped cluster since PG12) — so enable it now.
# wal_log_hints=on: REQUIRED for pg_rewind (cheap replica rejoin after failover).
initdb -D /var/lib/postgresql/17/main \
  --data-checksums \
  --auth-host=scram-sha-256 --auth-local=peer \
  --encoding=UTF8 --locale=en_US.UTF-8
# then set wal_log_hints=on in postgresql.conf (or let Patroni manage it — §4)

⚠️ Fix (critique). “Checksums cannot be added later” is inaccurate — pg_checksums adds them to a fully stopped cluster. Enable at initdb anyway (avoids the downtime), but don’t write the false claim into a tender.

Memory (example for a 8 GB node — scale to yours)

Parameter	Value	Restart?	Note
`shared_buffers`	~25 % RAM (`2GB`)	restart	back it with `huge_pages=on`
`huge_pages`	`on` (fail-fast, not `try`)	restart	reserve `nr_hugepages ≈ shared_buffers / 2 MiB` + ~10 %
`effective_cache_size`	50–75 % RAM (`6GB`)	reload	planner hint, not an allocation
`work_mem`	16–64 MB OLTP	reload	⚠️ worst case ≈ backends × concurrent-sorts × work_mem — bound `max_connections × work_mem`
`maintenance_work_mem`	512 MB–2 GB	reload	vacuum/index build
`max_connections`	100–300 (low)	restart	real fan-out absorbed by PgBouncer (§8); sum of pool sizes < this

Hugepages sizing:

# nr_hugepages = ceil(shared_buffers / Hugepagesize) + headroom
grep Hugepagesize /proc/meminfo                 # usually 2048 kB
echo 'vm.nr_hugepages = 1100' | sudo tee /etc/sysctl.d/30-hugepages.conf   # e.g. 2GB/2MB=1024 +~10%

Checkpoints, WAL, autovacuum

# Checkpoints — make them TIME-driven, spread the flush, avoid latency spikes
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 8GB                 # sized so checkpoints fire on time, not on fill
wal_compression = lz4              # (or zstd)
log_checkpoints = on               # so "checkpoints occurring too frequently" is visible

# Autovacuum — the 2005-era defaults are far too lazy for OLTP
autovacuum_max_workers = 4
autovacuum_naptime = 15s
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_scale_factor = 0.05        # + per-table overrides on hot tables
# watch age(datfrozenxid) for wraparound (see §11)

# Durability & integrity
fsync = on
full_page_writes = on
synchronous_commit = on            # commit latency == WAL fsync latency; put WAL on low-latency NVMe

# Observability GUCs — enable BEFORE tuning so you can measure
shared_preload_libraries = 'pg_stat_statements,pgaudit'   # ⚠️ needs a RESTART — set at install
track_io_timing = on
log_temp_files = 0

⚠️ Add (critique). shared_preload_libraries (pg_stat_statements, pgaudit, pgcrypto) require a restart. Set them at install so you never have to schedule a restart to retrofit them.

Generate a baseline with PGTune and validate with pgbench before committing numbers. Restart-vs-reload discipline: shared_buffers, huge_pages, max_connections, wal_buffers need a restart; work_mem, effective_cache_size, checkpoint_*, autovacuum_* reload live.

4. Patroni configuration and dynamic settings

Manage all cluster-wide settings via patronictl edit-config (stored in the DCS, applied cluster-wide). Keep the per-node bootstrap YAML minimal — never hand-edit postgresql.conf for things Patroni owns, it will overwrite them.

Real cg-stage bootstrap (/etc/patroni/patroni.yml), annotated with the hardening to add:

scope: postgres
namespace: /db/
name: pgsql-m

restapi:
  listen: 0.0.0.0:8008
  connect_address: pgsql-m:8008
  authentication: { username: patroni, password: '<secret-from-vault>' }

etcd3:
  hosts: pgsql-m:2379,pgsql-s1:2379,pgsql-s2:2379   # ← list ALL members, not just self
  protocol: https                                   # ← ADD: TLS to etcd (see §2)
  username: root
  password: '<secret-from-vault>'

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576        # 1 MB — a staler replica is never promoted
    master_start_timeout: 300
    # ─── ADD these ───
    failsafe_mode: true                     # a pure DCS outage doesn't needlessly demote a healthy primary
    postgresql:
      use_pg_rewind: true
      use_slots: true
      remove_data_directory_on_diverged_timelines: true   # a demoted primary auto-rejoins
      parameters:
        wal_level: replica
        hot_standby: 'on'
        wal_log_hints: 'on'                 # required for pg_rewind
        max_wal_senders: 10                 # ← raise from 5 (headroom for replicas + backup + rewind)
        max_replication_slots: 10
        max_slot_wal_keep_size: '64GB'      # ← CRITICAL: bound WAL a dead slot can pin (see Lesson)
        password_encryption: 'scram-sha-256'   # ← from md5
        checkpoint_completion_target: 0.9
  initdb:
    - auth-host: scram-sha-256              # ← from md5
    - auth-local: peer
    - encoding: UTF8
    - data-checksums
    - locale: en_US.UTF-8
  pg_hba:                                   # hostssl only for remote + replication (§9)
    - hostssl replication replicator samenet scram-sha-256
    - host replication all 127.0.0.1/32 scram-sha-256

postgresql:
  listen: 0.0.0.0:5432
  connect_address: pgsql-m:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin/
  authentication:
    superuser:   { username: postgres, password: '<vault>' }
    replication: { username: replicator, password: '<vault>' }
    rewind:      { username: rewind_user, password: '<vault>' }

tags:
  nofailover: false        # set true on a node that must never become primary (e.g. a backup/DR node)
  # failover_priority: N   # steer election order among eligible candidates

Timing invariant: ttl ≥ loop_wait + 2·retry_timeout, and the watchdog (§6) must fire before the ttl-based promotion window opens: watchdog_timeout = ttl − safety_margin. Defaults (30/10/10) are safe for an on-prem LAN. Only lower them (e.g. 20/5/5 → faster failover) if the network genuinely won’t produce false positives.

⚠️ Lesson + Add (critique, ties to the recurring disk-full pattern). use_slots: true without max_slot_wal_keep_size is the disk-full failure mode: a down or lagging replica’s slot pins WAL on the primary forever → pg_wal fills → filesystem read-only → DB dies. Always set max_slot_wal_keep_size, and alert on inactive/lagging slots (§11).

PostgreSQL 17: use failover slots so logical replication slots survive a switchover.

5. Replication and durability mode — decide RPO explicitly

This is a business decision with the client, not a default. There is no universally correct answer.

Posture	RPO	Cost	When
Async + `maximum_lag_on_failover`	small, bounded (state the real seconds)	lowest latency, highest write availability	latency-sensitive, tolerant of a few lost txns
Synchronous (`synchronous_mode: true`)	0 for sync nodes	write latency ↑	committed data must survive a node loss
Synchronous strict (`+ synchronous_mode_strict: true`)	0, guaranteed	writes refused if no sync standby	financial/regulatory zero-loss

# via patronictl edit-config — Patroni OWNS synchronous_standby_names, never hand-edit it
synchronous_mode: true
synchronous_mode_strict: true      # refuse writes rather than silently degrade to async
synchronous_node_count: 1          # ≥1 sync standbys; see the availability caveat below
postgresql:
  parameters:
    synchronous_commit: 'on'       # or remote_apply if reads-on-standby must see the commit (more latency)

⚠️ CRITICAL reconciliation (critique). synchronous_mode_strict (RPO=0) and a high write availability SLA are in direct tension: in strict mode, if all sync standbys are unavailable the primary refuses writes — healthy but down for writes. On a 3-node cluster with synchronous_node_count=1, losing both replicas stalls writes; losing one leaves you one fault from a stall. You must pick and state one: (a) quorum-based sync with enough candidates that a single loss can’t stall writes, or (b) an explicit SLA carve-out that sync-standby-loss write-stalls don’t count against the availability budget. Never claim RPO=0 while running async.

Encrypt replication: hostssl replication in pg_hba, standby primary_conninfo with sslmode=verify-full, optional clientcert=verify-full for mTLS.

6. Watchdog / fencing — the only real split-brain guarantee

A primary partitioned from the DCS can keep serving writes while a replica promotes itself → two primaries → data divergence. The watchdog guarantees the old primary is gone before promotion: if Patroni can’t confirm cluster state, it stops petting the watchdog and the kernel hard-reboots the node.

# softdog (kernel-timer) — the common default
echo softdog | sudo tee /etc/modules-load.d/softdog.conf
sudo modprobe softdog
sudo chown patroni /dev/watchdog

# in patroni.yml
watchdog:
  mode: required           # Patroni refuses to promote without a working watchdog
  device: /dev/watchdog
  safety_margin: 5         # fire 5s before ttl; set -1 for absolute guarantee (timeout = ttl//2)

⚠️ Fix (critique) — softdog ≠ hardware watchdog. softdog is a kernel-timer thread and cannot reset a hung kernel or a wedged I/O path — exactly the read-only-FS / storage-hang failure mode this environment is prone to. It only covers userspace/Patroni stalls. Where the threat model includes kernel/storage hangs (it does here), require a hardware watchdog (IPMI/iTCO); treat softdog as the fallback, not the default.

⚠️ Add (critique) — failsafe_mode caveat. failsafe_mode (§4) lets a primary keep running during a DCS outage only if it can directly confirm every member via REST. It is a deliberate availability-over-safety lever; with a partial partition it can widen the split-brain window. Document the precondition.

Never run production without a watchdog. CG-Stage: this is a node-level change requiring SSH — propose it in a runbook, don’t self-apply.

7. HAProxy and Keepalived — load balancing done correctly

The real cg-stage HAProxy is a good design (unlike the in-cluster pg-proxy — see the Lesson). Two listeners, both health-checking Patroni’s REST API:

global
    maxconn 1000
    log /dev/log local0
defaults
    mode tcp
    retries 2
    timeout connect 4s
    timeout client 30m
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
    stats auth admin:<strong-password>        # ⚠️ not "password" — and put it behind the VPN

# WRITE endpoint — only the true leader answers /primary with 200
listen primary
    bind 0.0.0.0:5432
    option httpchk GET /primary               # (Patroni also accepts /master)
    http-check expect status 200
    default-server inter 3s fall 2 rise 2 on-marked-down shutdown-sessions
    server pgsql-m  pgsql-m:6432  check port 8008    # forward to PgBouncer, health-check Patroni REST
    server pgsql-s1 pgsql-s1:6432 check port 8008
    server pgsql-s2 pgsql-s2:6432 check port 8008

# READ endpoint — every healthy replica answers /replica with 200
listen replicas
    bind 0.0.0.0:5433
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 2 rise 2 on-marked-down shutdown-sessions
    server pgsql-m  pgsql-m:6432  check port 8008
    server pgsql-s1 pgsql-s1:6432 check port 8008
    server pgsql-s2 pgsql-s2:6432 check port 8008

⚠️ Lesson (CG-012, the false-UP trap). A Layer-4 TCP check only proves the port is open. A PgBouncer/proxy in front of a dead PostgreSQL still accepts TCP, so an L4 check reports the backend UP during a total outage — exactly what the in-cluster proxy/pg-proxy did while the database was down for 12 days. Never put a bare L4 check on a DB backend. option httpchk GET /primary against Patroni :8008 returns 200 only on the node genuinely able to serve the role.

RTO note (critique): HAProxy detection latency = inter × fall (here 3s × 2 = 6s) and is the missing half of the client-facing RTO — see §13.

Keepalived, with a weighted script that probes HAProxy liveness (so the VIP fails over on HAProxy death, not just host death):

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"   # or a port probe
    interval 2
    weight -20                              # drop priority if HAProxy is dead → VIP migrates
}
vrrp_instance VI_1 {
    state MASTER                            # BACKUP on the peer
    interface ens192
    virtual_router_id 51                    # unique per VRRP domain
    priority 100                            # peer lower, e.g. 90
    advert_int 1
    authentication { auth_type PASS; auth_pass '<strong-shared-secret>' }
    virtual_ipaddress { 10.50.26.150 }
    track_script { chk_haproxy }
}

⚠️ Add (critique). Alert on VRRP state transitions and dual-MASTER — a VRRP partition can produce two VIP holders. Applications connect only to the VIP, never to node IPs.

8. PgBouncer connection pooling

[databases]
* = host=127.0.0.1 port=5432               # local PostgreSQL; HAProxy sits in front of PgBouncer

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction                    # multiplex thousands of clients onto a small backend set
max_client_conn = 5000                     # 2–3× peak clients
default_pool_size = 25                     # ≈ peak TPS × avg txn seconds; sum of pools < max_connections
reserve_pool_size = 5
server_lifetime = 3600
server_reset_query = DISCARD ALL
auth_type = scram-sha-256                  # ⚠️ needs PgBouncer 1.21+ (see friction note); real stack is 1.24

⚠️ Add (critique) — scram + transaction pooling friction. Transaction-mode pooling with scram-sha-256 needs PgBouncer 1.21+ with SCRAM pass-through / auth_query. The real stack is 1.24.0, so this works — but it is a known integration snag worth verifying. Prepared statements in transaction mode need either driver-side server-prepared statements disabled, or PgBouncer max_prepared_statements > 0.

Sizing rule: sum(default_pool_size across users/dbs) < PostgreSQL max_connections. Tune from SHOW POOLS — rising cl_waiting / maxwait means raise default_pool_size. Export pool stats to Prometheus. In this topology PgBouncer sits on each DB node behind HAProxy, so it always pools to the local PostgreSQL and HAProxy routes writes to whichever node is the leader.

9. Security hardening and compliance

-- Authentication: scram-sha-256 only. Set BEFORE creating roles.
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
-- on an existing cluster: force every role to re-hash
--   \password <role>  (or ALTER ROLE ... PASSWORD ...) then verify:
SELECT count(*) FROM pg_authid WHERE rolpassword LIKE 'md5%';   -- must be 0

# TLS — enforce via hostssl-only pg_hba, not merely ssl=on
ssl = on
ssl_min_protocol_version = 'TLSv1.2'       # target 1.3
ssl_ciphers = 'HIGH:!aNULL:!MD5'
# pg_hba.conf — most-specific first, narrow CIDRs, hostssl only, no trust, no 0.0.0.0/0
# hostssl  cgerp   cgerp_app   10.50.25.0/24   scram-sha-256

Clients use sslmode=verify-full and channel_binding=require (blocks MITM/downgrade — critique).

At-rest encryption: community PostgreSQL 17 has no built-in TDE. Use LUKS/volume encryption on PGDATA + separate WAL + backup volumes, keys in KMS/TPM held separately from the DBAs; pgcrypto for the most sensitive columns; pgBackRest encrypts backups independently (§10).

Least privilege: NOLOGIN owner/group roles + per-app NOSUPERUSER NOCREATEROLE NOCREATEDB login roles; REVOKE CREATE ON SCHEMA public FROM PUBLIC; ALTER DEFAULT PRIVILEGES; RLS on multi-tenant tables; superuser only for named break-glass DBAs.

⚠️ Lesson (CG-Stage anti-patterns). The post_init.sh creates a shared admin SUPERUSER, and the ERP deployments carry plaintext passwords (securepassword, rmpassword) in env (CG-007). Replace with per-service least-privilege roles and Vault-issued secrets.

Secrets: HashiCorp Vault PostgreSQL secrets engine (dynamic/rotated), delivered as 0600 files — never env vars, ConfigMaps or committed manifests.

Audit: pgaudit in shared_preload_libraries, scoped (ddl,role,write + object logging on regulated tables — never all on OLTP), log_connections/disconnections = on, shipped to a SIEM with retention + integrity controls.

Certificate lifecycle (critique — these VMs are outside k8s cert-manager): own CA management, server-cert rotation and expiry alerting for the DB tier. An expired server cert is a full outage.

Benchmark & patching: baseline against the CIS PostgreSQL Benchmark, remediate in risk order, schedule drift re-scans, track minor versions within a patch SLA, minimise extensions/PLs.

Network: dedicated DB subnet; firewall allows 5432/6432 only from the app + pooler subnets; listen_addresses on the internal interface; no public exposure.

10. Backup and disaster recovery — pgBackRest

Replication is not backup. Replication faithfully propagates a DROP TABLE, page corruption or ransomware to every standby in milliseconds. Only point-in-time backups recover from those.

[cgerp]
pg1-path=/var/lib/postgresql/17/main
[global]
repo1-path=/var/lib/pgbackrest
repo1-type=s3                              # off-host object storage
repo1-s3-bucket=cgerp-pgbackrest
repo1-cipher-type=aes-256-cbc              # client-side encryption; repo1-cipher-pass from Vault
repo1-retention-full=4                     # weekly full × 4 = ~35 days online
repo1-retention-diff=14
process-max=4
compress-type=zst
backup-standby=y                           # take the backup from a replica, offload the primary

# postgresql.conf — continuous WAL archiving enables PITR from day one
archive_mode = on
archive_command = 'pgbackrest --stanza=cgerp archive-push %p'
archive_timeout = 60s                      # bound idle-WAL loss → RPO ≤ 60s even with no writes

pgbackrest --stanza=cgerp stanza-create
pgbackrest --stanza=cgerp check            # validates the archive path end-to-end
# schedule: weekly full + daily diff + hourly incr
pgbackrest --stanza=cgerp --type=full backup

⚠️ CRITICAL (critique) — archive on EVERY node. After a failover the new primary must archive to the same stanza and continue the timeline, so archive_command + repo credentials are configured cluster-wide (Patroni-managed). WAL-archive failure fills pg_wal → read-only FS → DB down: add a dedicated alert on pg_wal size / archive-queue depth, separate from the generic disk alert.

3-2-1-1-0: ≥3 copies, 2 media, 1 off-site, 1 immutable (object storage with Object Lock / versioning + soft-delete — a WORM copy ransomware can’t delete), 0 restore errors.

Standardise node rebuild on pgbackrest restore --type=standby + delta restore so Patroni reattaches a rebuilt node quickly.

Verify + drill (non-negotiable): data-page checksums on; scheduled pgbackrest verify; monitor last-backup age and WAL archive continuity (gap count = 0); automated restore/DR drills (monthly restore test, quarterly timed end-to-end PITR into an isolated host) with row-count / consistency / app smoke checks and recorded achieved RPO/RTO.

⚠️ Reject a nightly pg_dump on the DB node as the DR strategy. A one-off pg_dump (as taken on cg-stage, CG-004) is a useful supplementary logical export, not a backup strategy: not scheduled, not off-host, not PITR, not restore-tested.

⚠️ GDPR tension (critique). Immutable WORM backups retain deleted PII for the retention window, which conflicts with the right-to-erasure (Art. 17). Document the legal basis / retention justification alongside the Art. 32 at-rest control.

11. Monitoring, observability and alerting

Build this before go-live. It is the single control that converts a silent multi-day outage into a five-minute response.

Exporters (one per instance, primary and every replica — lag is only meaningful on standbys): postgres_exporter running as a pg_monitor role (never superuser, DSN in a secret), Patroni /metrics on :8008 (≥2.1.0), etcd /metrics, node_exporter on every DB / DCS / HAProxy VM (disk-free on PGDATA / pg_wal / etcd dir), pg_stat_statements for latency.

Critical alerts (each linked to a runbook):

Alert	Condition
PatroniHasNoLeader	no `/primary` for 1 min
PostgresDown	`pg_up==0`, distinguished from a scrape failure (`pg_exporter_last_scrape_error`)
ReplicationLag	LSN-diff bytes and seconds — warn 30s / crit 300s
InactiveReplicationSlot	a slot pinning WAL (ties to the disk-fill risk, §4)
SyncStandbyBelowRequired	in strict mode, below `synchronous_node_count` → writes about to stall
PatroniPaused	cluster left in `patronictl pause` → auto-failover silently OFF
ConnSaturation	connections vs `max_connections` > 0.8 / ≥ 1.0
XIDWraparound	`age(datfrozenxid)` warn 300M / crit 1B (ceiling 2³¹); also oldest-`xmin` / idle-in-txn as the leading indicator
DiskPressure	any of PGDATA / pg_wal / etcd > 85 %
EtcdQuota	`db_total_size` > 95 % of quota
BackupFailed / WALGap	last-backup age high, or archive gap ≠ 0
CertExpiry	server/CA cert < 30 days (DB tier is outside cert-manager)
ClockSkew	chrony offset high across nodes
VRRPFlap / DualMaster	Keepalived state transitions / two VIP holders

⚠️ Add (critique). Also aggregate the PostgreSQL server logs themselves (Loki/ELK) and HAProxy/Keepalived metrics — the pgaudit→SIEM path covers audit events, not the DB’s own logs or the LB tier.

Synthetic probe (independent of the exporters): a blackbox PostgreSQL-protocol probe — send an SSLRequest (8 bytes: length 8, code 80877103); a healthy server replies S/N, an EOF means no backend. This is the exact probe that revealed the cg-stage outage that the L4 check hid.

Dashboards & routing: Grafana per layer (Patroni dashboard ID 18870, postgres, etcd, node) kept in Git; Alertmanager routed to a real off-hours channel (PagerDuty/Opsgenie/SMS) with a Watchdog dead-man’s-switch so a monitoring outage pages too. Do not co-locate the monitoring stack on a DB node. Define SLIs/SLOs (availability at the VIP, p99 latency, replication freshness) and layer multiwindow multi-burn-rate alerts on top of the cause-based alerts above.

⚠️ Lesson (CG-006). The cg-stage cluster had no metrics-server, no Prometheus, no alerting — the OTel collector exported to debug (discarded). That is why the database was down for 12 days before anyone noticed. Monitoring is the deliverable, not an add-on.

12. Lifecycle operations: upgrades, patching, maintenance, runbooks

Manage the whole cluster as IaC (Ansible role or a Kubernetes operator) in Git, with an identical staging cluster. Encode the procedures below as tested playbooks, not tribal knowledge.

Minor upgrades (rolling, replicas first): patch replicas → patronictl switchover to an upgraded replica → patch the old primary. Write blip ~5–10 s, zero read downtime with ≥2 replicas. Shrink the blip further with a PgBouncer PAUSE/RESUME around the switchover to drain in-flight transactions.

Major upgrades: pg_upgrade --link under patronictl pause (with --check first), then reinit standbys and run the generated analyze/extension scripts; or a logical-replication cut-over (~10–15 s). Rehearse in staging with a full backup as rollback.

⚠️ Add (critique). pg_upgrade --link is destructive to the primary data dir — a failed --link upgrade cannot be rolled back in place. Require --check + a tested restore path first.

Planned stops: patronictl pause before any planned stop/move (OS patch, kernel reboot, major upgrade) so Patroni doesn’t auto-failover mid-work; always resume and confirm a healthy leader + streaming replicas after. One node at a time; reboot standbys freely; switchover off the primary before patching it; respect the PDB and confirm lag ≈ 0 before the next node.

Also roll (critique): Patroni and etcd version upgrades themselves (rolling), not just PostgreSQL.

Application-side resilience (critique — DB HA is undermined without it): the app must connection-retry with backoff, transaction-retry on 40001/57P01, set sane statement_timeout/connect_timeout, and be idempotent — otherwise a clean 10 s failover still surfaces as application errors. PgBouncer must target the VIP/HAProxy primary port, not a node IP, or it won’t follow the leader (server_check_query, server_lifetime, server_reset_query).

Read-path consistency (critique): routing reads to VIP:5433 (async replicas) serves stale data. Either the app tolerates replication lag on reads, or specific reads are pinned to the primary / use remote_apply. HAProxy can’t parse SQL, so this is an application contract that must be documented — otherwise read-scaling is a correctness bug.

Incident runbooks (pre-written, rehearsed). First two diagnostics are always patronictl list + journalctl -u patroni:

Incident	Response
Divergent timeline (replica `start failed`, “requested timeline N is not a child…”)	`pg_rewind` then `patronictl reinit <cluster> <node>`, one at a time; the leader holds the good data. This is `cg-stage` CG-019.
Leader loss	verify promotion + app repoint via `/primary`; rebuild the dead node from pgBackRest
DCS quorum loss	`failsafe_mode` keeps the primary serving; treat etcd recovery (snapshot restore / member replace) as top priority
etcd NOSPACE / near-quota	`compact --physical` → rolling `defrag` → `alarm disarm`; verify Patroni re-acquires the lease. This is `cg-stage` CG-001.
Disk-pressure / read-only FS	free space (WAL archive stuck? inactive slot?), remount rw, restart PostgreSQL; root-cause the slot/archive

Change management: snapshot/backup before → patronictl pause where needed → execute → verify (leader healthy, replicas streaming, lag ≈ 0, app returns 200s) → resume → log to the ops trail.

13. Acceptance validation and DR drills — capture the evidence

Every NFR you commit to in [[Database-Tender-NFR-Responses]] must be proven here and the artefact kept (dashboards, drill reports, config baselines):

Timed failover drill — capture leader-loss-to-page (≤ 60 s) and the three distinct RTOs: (1) Patroni promotion (ttl + promotion, ~10–25 s); (2) end-to-end client recovery = promotion
- HAProxy detection (inter × fall, ~6 s) + PgBouncer/app reconnect; (3) DR-restore RTO = rebuild-from-pgBackRest after cluster loss (minutes–hours, scales with DB size + WAL replay). Report all three — they are very different numbers.
RPO=0 kill-primary test — the promoted node holds the last acknowledged commit; pg_stat_replication.sync_state = sync; Patroni /synchronous returns 200.
PITR drill — restore latest + replay to an arbitrary target time within retention on an isolated host; row-count / consistency / app smoke checks; record achieved RPO/RTO and measured restore duration for the real DB size.
Split-brain partition test — the isolated old primary self-fences (watchdog reboot) and never accepts concurrent writes; only one node answers /primary 200.
Security acceptance — CIS conformance report; pg_stat_ssl = true on all non-loopback backends (including the walsender); 0 md5 verifiers; secret-scan clean; pgaudit events visible in the SIEM.

Appendix A — the `cg-stage` cluster as-built vs this guide

Aspect	`cg-stage` as-built	This guide
Stack	PG 17.4, Patroni 4.0.5, etcd 3.5.21, PgBouncer 1.24, HAProxy 2.9, Keepalived, Ubuntu 24.04	same
etcd	3 members on DB nodes; no auto-compaction/quota → CG-001 outage	3/5 members, auto-compaction + quota + TLS + snapshots
Auth	`password_encryption = md5`	scram-sha-256 + channel binding
WAL archiving / PITR	none	pgBackRest + continuous archiving + drills
Replication slots	`use_slots` on, no `max_slot_wal_keep_size`	bounded + inactive-slot alert
LB health check	✅ `httpchk /master` on `:8008` (correct!)	same (contrast the in-cluster `pg-proxy` L4 trap)
Watchdog / fencing	not configured	required (hardware where kernel/storage hangs are in scope)
Monitoring	none (CG-006) → 12-day silent outage	Prometheus + Patroni/etcd/node exporters + paging
Secrets	plaintext (CG-007), shared `admin` SUPERUSER	Vault + least-privilege roles
Storage	shared volume, ~48 GiB vs 100 GB spec (CG-014)	separate PGDATA / WAL / etcd / backup volumes

Appendix B — sources

Consolidated from the 2026-07 best-practice research (Patroni, etcd, PostgreSQL 17, pgBackRest, PgBouncer, CIS Benchmark, ISO/IEC 25010 docs). Per-dimension source URLs are retained in the research transcript under subagents/workflows/…/journal.jsonl; the authoritative primaries are: patroni.readthedocs.io · etcd.io/docs · postgresql.org/docs/17 · pgbackrest.org · pgbouncer.org · CIS PostgreSQL Benchmark · cert-manager/keepalived/haproxy upstream docs.