2026-09-01 · run 2026-09-01-ha-sync-and-clean-failover

Postgres HA: sync replication + clean (promote-while-up) failover

Summary

This closes the last two untested legs of the Postgres HA line (2026-08-26-ha-failover-mid-migration covered the crash case). Two results:

1. Synchronous replication works as advertised. With synchronous_commit=remote_apply + synchronous_standby_names=* the replica reports sync_state=sync, idle replay lag ~44 ms right after the clone settling to ~0.7 ms in steady state, and a committed realm write is immediately visible on the hot standby — the primary does not ack until the standby has applied the WAL.
2. A clean failover has a different failure shape than a crash. Promoting the replica while the primary is alive leaves the old primary running with sync replication but no standby — so its writes block indefinitely (the probe CREATE TABLE never completed). That is a safety property (the old primary cannot accept split-brain writes), but it means the old primary wedges until stopped. The switchover must therefore be: promote → repoint Keycloak → stop the old primary, and an operator who promotes first without quiescing writes will find the old primary hung, not failed.

Environment

Procedure

COMPOSE_FILE=docker-compose.yml:docker-compose.ha.yml
HA_SYNC_COMMIT=remote_apply HA_SYNC_STANDBYS='*'
docker compose up -d db                       # primary, sync settings
./bin/ha-init-replica.sh                       # clone + start standby
docker compose up -d keycloak                  # writes now ack only via replica
# create a realm to prove write visibility on the standby
POST /admin/realms {"realm":"ha-sync"}

# clean failover: promote while the primary is STILL UP
docker compose exec -u postgres db-replica pg_ctl promote -D /var/lib/postgresql/data
# probe: write to the OLD primary (sync, no standby) -> BLOCKS, CREATE TABLE never commits

# switchover
sed KC_DB_URL -> jdbc:postgresql://db-replica:5432/keycloak
docker compose up -d --force-recreate keycloak  # repoint
docker compose stop db                          # break split brain

Results

Sync replication (steady state)

Clean failover (promote-while-up)

What this teaches

• Sync replication is the "no data loss at failover" configuration: a commit is not acknowledged until the standby has applied it, so the failover target is always current (contrast the async crash case, where the standby is a committed-prefix snapshot with possible lag).
• The clean failover is operator-hazardous in the other direction. The crash case is bounded data loss; the clean case is a wedge. Promote the replica while the primary lives, and with sync replication the old primary blocks on every subsequent write — silent, indefinite, and easy to mistake for a healthy node that just "went slow". The pre-flight for a maintenance failover is: stop the old primary (or quiesce all writers) before promoting the replica.
• The switchover order the lab already uses (promote → repoint → stop old primary) is correct for async; for sync it leaves a brief window where the old primary is wedged rather than split-brained — safer, but the depends_on gotcha from the crash record still applies.

Verification