<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.StylE-mailovZprvy17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="CS" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><a name="_Hlk30747702"><span lang="EN-US">Hello,<o:p></o:p></span></a></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I have been testing an ability of pgPool to recover from certain disaster scenarios and found one particular situation which ended in the database being completely unavailable without
manual intervention. As for the cluster – I have 2 servers with a pgPool service (version 4.1.5) with Watchdog and 2 servers with a PostgreSQL service (version 11.11) configured with a streaming replication. By default the servers have following roles:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">PG1 – MASTER pgPool node with VIP address brought up.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">PG2 – STANDBY pgPool node.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">DB1 – PRIMARY PostgreSQL server.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">DB2 – STANDBY PostgreSQL server.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">The problematic situation came up, when I tried to disconnect server PG1 from the network without shutting it down. The result was that VIP was brought up on PG2 server, but since
the PGP1 lost all communication with all other servers, it retained the VIP address as well (thinking it was actually PG2 server that went down, effectively causing a split brain situation between PG1 and PG2) and at the same time had the health check failed
for both DB servers. At this moment it was still fine from the outside, since PGP2 was working as a connection point for any client with both DB servers connected.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">However – when I connected the PG1 server to the network again, it started a communication with PG2 server via Watchdog and probably went on to solve the split brain situation by
demoting the PG2 server to STANDBY role and bringing down its VIP address. But at the same it caused the degeneration of both DB servers, probably because the healthcheck from the PG1 node was failing during the time out of the network. The result was that
both DB nodes were down and the database became inaccessible to clients.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I am sending you a log from the PG1 server which describes the situation.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Now when I tried the same with the PG2 server (which is the STANDBY node by default), it pretty much went the same route the first half of the scenario (it promoted itself to MASTER
status and brought up the VIP address, while being disconnected from the network), but when I connected it back to the network, the VIP on the server was brought down and both DB server were still connected to the cluster without any impact for the client
– so it returned to the default state.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">After that I came up with a solution to use the trusted_servers parameter to „kill“ the pgPool service on the PG1 server in case of it being out of the network, while NOT using
it on the PG2 server in the case that both PG servers went shortly out of the network so it doesn’t cause both PG servers to be effectively dead for the client. Can you please advise whether this solution will work in general and won’t cause any additional
problems with the database being unavailable, or if I should rather find a different solution? I sadly don’t have the option to add a third PG server to basically prevent a split brain situation.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I am sending you the pgpool.conf from the PG2 server (the config on PG1 was the same except for IP addresses in the Watchdog section being reversed).<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Let me know, if you have any idea how to handle this situation. Thank you for your time.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">With best regards,<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Vladimír Láznička<o:p></o:p></span></span></p>
</div>
</body>
</html>