Read/write endpoint confusion

Read/write endpoint confusion
Type: incident simulation · Topic: HA & Failover · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-08/06-read-write-endpoint-confusion

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the application's write endpoint pointed at the read-only standby
(app_endpoints.write_endpoint = 'replica'). Reads worked, so monitoring looked
healthy, but every write failed — the endpoint was aimed at a node in recovery.

How it was found: the app_endpoints registry showed the write endpoint on the
replica; pg_is_in_recovery() confirmed that node was read-only.

The mitigation: repoint the write endpoint to the writable primary
(`pgpg action repoint-write-endpoint`); writes then went to a node that accepts
them.

Lesson: an HA incident isn't resolved until the app's WRITE endpoint points at the
writable primary. A successful read does not prove write-readiness — check
pg_is_in_recovery() on the write target. Promotion, an index, or a read-only check
doesn't fix a mis-pointed endpoint.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Reads work but writes fail. Check where the app sends writes: SELECT key, value FROM app_endpoints; the write_endpoint points at 'replica'. Then confirm that target is read-only: SELECT pg_is_in_recovery(); on the replica is true.
2. The write endpoint is aimed at the read-only standby — a read succeeding doesn't mean writes will. The writable node is the primary.
3. Repoint writes to the primary: `pgpg action <session> repoint-write-endpoint`. Then app_endpoints.write_endpoint = 'primary'. Don't promote anything, don't add an index, and don't accept a read-only check as 'fixed'.