A trading platform's order-history and allocations services are Spring Boot apps using HikariCP, connecting through a PgBouncer pair to a PostgreSQL 15 cluster managed by Patroni, addressed by a DNS name with a 300-second TTL that a failover hook updates. At 10:42 during market hours the primary's storage stalled and Patroni promoted the replica in the second rack. Since then about a third of order-history writes fail with 'cannot execute INSERT in a read-only transaction', and the allocations team says a few of their writes 'succeeded' at 10:43.
The interviewer asks“Patroni promoted the replica 40 seconds after the primary's host stalled, but twenty minutes later the order-history service is still failing writes and some instances seem to be connected to the old primary. Walk me through what you check, in what order, and how you fix it now and for good.”
First I rule out split brain, because writes 'succeeding' after promotion means the old primary may still be accepting them: I check pg_is_in_recovery on both nodes and fence the old one immediately if it's writable. Then I follow the address through each layer: the DNS record and its 300-second TTL, the JVM's own DNS cache, PgBouncer's existing server connections, and HikariCP's pooled connections, which can live up to 30 minutes and pass validation on a demoted read-only node. I drain them in order with PgBouncer RECONNECT and Hikari soft eviction or a rolling restart, and reconcile any writes that landed on the old timeline before it's rewound. The permanent fix is routing through an endpoint that follows the Patroni leader, like HAProxy checking the /primary endpoint and killing sessions when a node is marked down, plus shorter pool lifetimes, fencing via watchdog, and regular failover drills.
A free account unlocks the rest of it, and saves your progress across every track.
strace Trace the system calls a process makesltrace Trace the library calls a program makeslsof List open files, sockets and the processes holding themdmesg Read the kernel's message logperf Profile where CPU time goes, kernel and user spacegdb Debug crashes, core dumps and hung processescoredumpctl Find and open core dumps captured by systemdsar Collect and replay system activity history