<story><title>PostgreSQL HA cluster failure: a post-mortem</title><url>https://gocardless.com/blog/incident-review-api-and-dashboard-outage-on-10th-october/</url></story><parent_chain><item><author>foobarbazetc</author><text>I’ve been involved in various Postgres roles (developer on it, consultant for it for BigCo, using it at my startup now) for around 18 years.&lt;p&gt;I’ve never in that time seen a pacemaker&amp;#x2F;corosync&amp;#x2F;etc&amp;#x2F;etc configuration go well. Ever. I have seen corrupted DBs, fail overs for no reason, etc. The worst things always happen when the failover doesn’t go according to plan and someone accidentally nukes the DB at 2am.&lt;p&gt;The lesson I’ve taken from this is it’s better to have 15-20 minutes of downtime in the unlikely event a primary goes down and run a manual failover&amp;#x2F;takeover script then it is to rely on automation. pgbouncer makes this easy enough.&lt;p&gt;That said, there was a lot of bad luck involved in this incident.</text></item></parent_chain><comment><author>scurvy</author><text>Our PostgreSQL failover plan is very pedestrian, but it works well (even across datacenters). We run streaming replication off of the primary to a pair of replicas, with one replica in another datacenter. The primary write DB advertises a loopback IP out OSPF into a top-of-rack switch, where it&amp;#x27;s aggregated by BGP and distributed throughout our network. There&amp;#x27;s a health check script [0] running every 3 seconds that makes sure PG is happy and that it is still writeable.&lt;p&gt;If we want to failover (nothing automatic), we stop the primary (or it&amp;#x27;s already dead) and the route is withdrawn. An operator touches the recovery file on new primary, the health checker sees that, and the IP is announced back into the network. Yes, it&amp;#x27;s a &amp;quot;VIP&amp;quot;, but it&amp;#x27;s one controlled by our operations team, not automation software. One nice things about this is that you can failover across datacenters (remember it&amp;#x27;s advertised into our network over BGP) without reconfiguring DNS or messing with application servers.&lt;p&gt;While the mechanisms are different, we do something very similar for MySQL with MHA. It&amp;#x27;s still an operator running scripts intentionally though (which is what we want).&lt;p&gt;I will definitely agree with you that manual operator intervention is better than automated failover.&lt;p&gt;[0] &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;unixsurfer&amp;#x2F;anycast_healthchecker&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;unixsurfer&amp;#x2F;anycast_healthchecker&lt;/a&gt;</text></comment>
<story><title>PostgreSQL HA cluster failure: a post-mortem</title><url>https://gocardless.com/blog/incident-review-api-and-dashboard-outage-on-10th-october/</url></story><parent_chain><item><author>foobarbazetc</author><text>I’ve been involved in various Postgres roles (developer on it, consultant for it for BigCo, using it at my startup now) for around 18 years.&lt;p&gt;I’ve never in that time seen a pacemaker&amp;#x2F;corosync&amp;#x2F;etc&amp;#x2F;etc configuration go well. Ever. I have seen corrupted DBs, fail overs for no reason, etc. The worst things always happen when the failover doesn’t go according to plan and someone accidentally nukes the DB at 2am.&lt;p&gt;The lesson I’ve taken from this is it’s better to have 15-20 minutes of downtime in the unlikely event a primary goes down and run a manual failover&amp;#x2F;takeover script then it is to rely on automation. pgbouncer makes this easy enough.&lt;p&gt;That said, there was a lot of bad luck involved in this incident.</text></item></parent_chain><comment><author>thijsvandien</author><text>I&amp;#x27;d generalize this to many kinds of failover setups, DB or non-DB. Practically each and every one I&amp;#x27;ve seen is in itself less reliable than the thing it&amp;#x27;s covering for.</text></comment>