ibarwick
commented
5 years ago Hi
Thanks for the detailed report; working through the backlog here.
First of all, I want to give my thanks for providing repmgr suite for managing postgres replication and failover scenarios, we have used it with great success in my organization.
Much appreciated!
it would be very useful if one could provide a option to "repmgr standby promote" not to exit with 0 unless the other standby(s) begins to follow the new primary.
This sounds potentially useful and implementable with reasonable effort; we'll look into it.
Could you provide the repmgr.conf files for each node (suitably anonymized if necessary)? That would help to rule out certain issues and/or reliably reproduce the situation.
Thanks!
MannerMan
Hello!
First of all, I want to give my thanks for providing repmgr suite for managing postgres replication and failover scenarios, we have used it with great success in my organization.
I would like to describe an incident that occurred in our production environment some days ago however, were our repmgrd failover setup could not perform a recovery, due to some unfortunate circumstances. I will begin by describing our setup:
We have one primary database, with two attached standby replicas. All servers run repmgr(d) 4.1.1 and postgres 10.5. Our traffic is sent to the database through a pgbouncer node in front of the cluster. Repmgr is configured with a custom promote_command, which triggers a wrapper script that routes traffic in pgbouncer to the node being promoted, and then performing the actual promotion. Since we use repmgrd, the remaining standby is automatically instructed to follow the new primary that was promoted. Should promotion fail (i.e. "repmgr standby promote" exit with a non-zero status), the wrapper script will re-route the traffic in pgbouncer back to the original primary.
This setup has worked very well for us, both in testing the automatic failover, and doing real production switchovers (although manual, through a very similar script to the one used as promote_command).
But of course, when things go wrong in the real world, things tend not to go quite as expected :) And this was the case for us this weekend, when a virtualization host crashed due to a memory error. One of the virtual machines running on this host was a primary database server for one of our repmgrd clusters. Now, the automatic re-routing of traffic and promotion of a new standby did work flawlessly, but due to a another H/A mechanism in the virtualization layer, the old primary came back online, just as a new secondary had been promoted, but before the other standby was instructed to follow the new primary. This seems to have caused the other standby to abort its attempt to follow the new primary, since it could now reach the old primary again.
repmgr.log from this standby:
We do use synchronous replication, with the "synchronous_standby_names ANY 1 (..)" option on all nodes to ensure that there is always have at least one replica attached for the node acting as primary to be able to accept writes. We use this essentially as split-brain protection, since this ensures that at least two nodes in the cluster of three are in agreement to accept writes. And in this incident, this protection did work as intended and prevented the lone standby that was promoted from accepting writes. But since the "repmgr standby promote" command succeeded, traffic was routed to the new (read-only) primary, causing some service disruption.
It seems we were very unlucky here, had the old primary come back a few seconds sooner, promotion should have failed and the service would have come back online. If the old primary had come back a few seconds later than it did, the standby node should have followed the new primary, and the service would have come back online. Murphy's law I guess :)
I'm now looking into ways to mitigate this specific case, i.e. were promotion of a new primary is successful, but before the other standby has begun to follow the new primary, the old primary comes back online. It's probably possible to engineer something in our promotion wrapper script, that verifies with "repmgr cluster show" or similar that the other standby is following the new primary. I would like to avoid putting too much logic/complexity in this script though. For our use-case, it would be very useful if one could provide a option to "repmgr standby promote" not to exit with 0 unless the other standby(s) begins to follow the new primary. That would trigger our existing mitigation and re-route traffic to the old primary. "repmgr standby switchover" appears to have such an option with '--siblings-follow' (which we use for manual switchovers).
I am open to any suggestion in trying to mitigate this specific case however. Please let me know if any further information or logs are required!