javohir.dev
KOENUZ
All projectsCompleted

PG Resilience Lab

An HA/DR lab that breaks a PostgreSQL cluster in ten ways and measures RTO, RPO and ledger integrity

GitHub code

What this project proves

  • Ten failures × 3 runs: 0 of 91,700 acknowledged transfers lost, 0 applied twice, ledger always balanced
  • Measured that cancelling a commit waiting for sync replication reports success and loses it (72 rows); fencing switched to terminate, 0 lost
  • Two 17 → 18 upgrade paths compared: pg_upgrade --link 22.6 s write gap, logical replication + HAProxy cutover 2.4 s
  • Recovered an accidental DROP TABLE with PITR to the xid just before it, found with pg_waldump; median 32 s
Role
Solo: cluster design, fault injection and measurement automation, analysis, six ADRs and a Korean runbook
Period
2026.10 – 2026.10
Stack
  • PostgreSQL
  • repmgr
  • pgBackRest
  • HAProxy
  • Docker
  • k6
  • Bash
  • Python
  • NestJS

Key numbers

ItemValueTypeEvidence
Transfers lost after a success reply (30 runs)0 / 91,700MeasuredView evidence
Median RTO · primary kill -97.07 sMeasuredView evidence
Median RTO · 45 s network isolation27.91 sMeasuredView evidence
Median RTO · split brain28.2 sMeasuredView evidence
Median RTO · DROP TABLE → PITR32.03 sMeasuredView evidence
17 → 18 logical replication cutover write gap2.43 sMeasuredView evidence
17 → 18 pg_upgrade --link write gap22.6 sMeasuredView evidence
Commits released by cancel, reported as success and lost72 / 620MeasuredView evidence
Security checks passed18 / 18MeasuredView evidence

The problem

Most HA setups stop at 'it is configured'; nobody measures how many seconds it takes for writes to return when the primary really dies, or whether an acknowledged transaction can vanish. This lab sends 20 LedgerCore transfers per second while injecting ten failures three times each: primary stop, kill -9, server shutdown and restart, network isolation, a full WAL disk, a deleted data directory, a lost table file, an accidental DROP TABLE and split brain. Every run computes RTO, transfers lost after a success reply (RPO), double applications and the double-entry invariant.

Architecture

Three PostgreSQL 17 nodes (repmgr 5.5, one primary and two standbys) and a witness sit on the replication network; the LedgerCore API and HAProxy sit on the app network, so cutting only the replication network reproduces split brain with clients still reaching the old primary. synchronous_standby_names is 'ANY 1 (pg1, pg2, pg3)'. A health agent on each node (:8008) answers 200 on /primary only when the node is not in recovery, is not fenced and has a synchronous standby that replied within 3 s; HAProxy checks it every second. An isolated primary turns read-only and terminates sessions. Backups go to a dedicated pgBackRest repository host in TLS server mode (asynchronous WAL archiving, backups taken from a standby), and every connection is verify-full TLS from a lab CA. A runner container drives the k6 load, fault injection, healing and analysis.

Data model

Key constraints and indexes

  • synchronous_standby_names = 'ANY 1 (pg1, pg2, pg3)': writes continue when one standby is lost
  • HAProxy :5000 only routes to a primary with a live synchronous quorum
  • Every connection is hostssl with verify-full and scram-sha-256; network superuser logins are rejected; credcheck requires 16 characters
  • ledger_app role: statement_timeout 5 s · lock_timeout 2 s · idle_in_transaction_session_timeout 10 s

Key design decisions

ADR-0001 repmgr + witness

Context
Three nodes needed automatic failover, and I wanted to work with the mechanism directly using the tooling from my course.
Decision
repmgr 5.5 with a witness, primary_visibility_consensus, standby_disconnect_on_failover, and fencing the primary when its children disconnect
Trade-off
No DCS lock, so split-brain protection has to come from quorum routing and fencing

ADR-0002 Sync quorum ANY 1

Context
Naming a single synchronous standby, as in FIRST 1 (pg2), stops every write when pg2 stops.
Decision
synchronous_standby_names = 'ANY 1 (pg1, pg2, pg3)'
Trade-off
With pg2 stopped for 25 s, FIRST 1 produced 12,119 failed attempts and 213 abandoned transfers; ANY 1 produced none. Writes still stop if both standbys are gone

ADR-0003 Quorum-aware HAProxy health check

Context
In the first runs an isolated primary that came back was advertised to HAProxy for about 18 s before it fenced itself.
Decision
/primary returns 200 only when the node is not in recovery, not fenced and has a synchronous standby that replied within 3 s
Trade-off
If every standby is slow, a healthy primary also leaves the write pool; synchronous replication would be blocking its commits anyway

ADR-0006 Terminate, not cancel, during a sync wait

Context
Sessions waiting for a synchronous standby on a partitioned primary have to be cleared, and statement_timeout does not interrupt that wait.
Decision
Fencing uses only pg_terminate_backend; the runbook forbids cancel on SyncRep waits
Trade-off
Clients get a connection error and must retry; LedgerCore retries the same transfer safely with its idempotency key

Before and after

Beforepg_cancel_backend · acked 620 rows → 72 lostView evidence
Afterpg_terminate_backend · acked 2,460 rows → 0 lostView evidence
What changed
Releasing commits that wait for synchronous replication with pg_cancel_backend during a partition makes PostgreSQL report them as successful, and the rows are gone after failover. Fencing now uses only pg_terminate_backend and the runbook forbids cancel (ADR-0006).

Incident write-up

Symptom
During the ack-leak experiment both standbys failed to promote, logging recovery ended before configured recovery target was reached.
Root cause
The PITR in scenario 09 had left recovery_target_xid in postgresql.auto.conf, and every standby cloned afterwards copied it, so each promotion waited for a recovery target that had already passed.
Fix
ALTER SYSTEM RESET recovery_target_* now runs right after PITR, and clone and rejoin defensively strip recovery_target settings.
Prevention
Scenario 09's heal step now takes a fresh full backup after PITR, and the failure did not come back in the 30-run campaign after the fix, which included three PITRs.

Tests and quality

Tests
—
Coverage
—
CI
—
Test tools
장애 시나리오 10종 × 3회, 실험 8종, 보안 점검 18개(security/run.sh), bash -n, LedgerCore 단위 테스트 3개(연결 끊김 수정)

Deployment and operations

Deployment
Docker Compose on one host. On Windows, .\lab.ps1 init writes a .env with random passwords and .\lab.ps1 up builds the images, issues certificates, bootstraps the cluster, takes the first full backup and installs the pg_cron jobs in one go.
Backups
A dedicated pgBackRest repository host (TLS server mode, zstd), asynchronous WAL archiving, full weekly, differential daily and incremental every 4 hours, taken from a standby. Every day the latest backup is restored to a scratch directory and checked for ledger integrity and checksums (restore 1.8 s + recovery 1.4 s, 0 violations). An S3 repo2 (aes-256-cbc) is configured but not measured.
Rollback
If pg_upgrade fails, the old 17 data directory is brought back automatically. .\lab.ps1 baseline rejoins or rebuilds broken nodes and switches pg1 back to primary.
Monitoring
A health agent per node (:8008), HAProxy stats (:8404), and repmgr events from every node collected into a timeline relative to the injection. PG Insight was used to watch the sync quorum, WAL archiving failures and table bloat.

Screenshots

Median and min-max RTO for the ten failure scenarios (3 runs each)
PG Insight showing the lab primary's sync quorum (2 responding / 1 required), slots and WAL archiving
With the pgBackRest repository stopped, WAL archiving fails and the Health Score drops from 85 to 70

Limits and retrospective

Current limits

  • Every container runs on one host, so the lab validates the HA mechanisms (detection, election, fencing, routing, recovery) rather than survival of a real machine failure; synchronous replication also looks cheaper than it is.
  • The numbers were measured in a 2 vCPU Linux sandbox with Ubuntu-based substitute images of the same packages; the repository Dockerfile uses postgres:17-bookworm and PGDG packages.
  • repmgr has no DCS leader lock, so split-brain protection is the combination of a sync quorum, quorum-aware routing and a soft fence; Patroni + etcd gives stronger guarantees.
  • The S3 repository (repo2) is configured but was not part of the measurements; there is no PgBouncer.

If I built it again

  • Run the same scenarios on Patroni + etcd and compare RTO and split-brain behaviour with repmgr.
  • Run it across at least two machines to measure the real network cost of synchronous replication.

Key code

Current status and next steps

Across 30 runs, 91,700 transfers received a success reply; none were lost, none were applied twice and the ledger never went out of balance. Median RTO was 6.7 to 7.5 s for process failures (stop, kill -9, restart, full disk, lost file), 24.7 s for a server shutdown, 27.9 s for network isolation, 28.2 s for split brain, 32.0 s for PITR and 35.9 s for a deleted data directory. The first runs exposed twelve problems that put data at risk or kept the cluster from healing itself, all fixed, including a LedgerCore bug where losing the database connection crashed the whole API process. For 17 → 18, pg_upgrade --link left a 22.6 s write gap and logical replication with a HAProxy cutover 2.4 s, both with nothing lost.