<div dir="ltr"><span style="font-size:small;text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">Hi </span><span style="font-size:small;text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">Piotr,</span><div><br></div><div><div class="gmail_quote"><div dir="ltr">On Thu, Jul 26, 2018 at 3:43 PM Piotr Gbyliczek <<a href="mailto:P.Gbyliczek@node4.co.uk">P.Gbyliczek@node4.co.uk</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>
<br>
<br>
<table width="800" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td colspan="8" valign="top"><table width="800" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td colspan="8" valign="top"><table width="800" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td width="40" rowspan="2" valign="top"><table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><img border="0" src="cid:118072611430900941@uk-mta-31.uk.mimecast.lan"></td></tr></tbody></table></td>
<td width="9" rowspan="2"> </td>
<td width="480" valign="top" cellpadding="0" cellspacing="0">
<span style="font-size:20px;font-weight:bold;font-family:Arial;margin:0;padding:0">Piotr Gbyliczek</span>
<hr style="height:0;border-top-style:solid;border-top-color:#e05206;color:#e05206;margin:4px 0px;border-width:1px 0 0">
<span style="font-weight:lighter;font-size:14px;font-family:Arial;margin:0;padding:0">Solutions Engineer</span>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr><td height="4" style="font-size:4px" cellpadding="0" cellspacing="0"> </td></tr>
<tr>
<td width="56%" valign="top">
<span style="font-size:11px;font-family:Arial;margin:0;padding:0">
ddi. <strong>08454 210444</strong><br>
t. <strong>0845 123 2222</strong>
<br>e. <strong style="color:#303a3d;text-decoration:none"><a href="mailto:P.Gbyliczek@node4.co.uk" target="_blank">P.Gbyliczek@node4.co.uk</a></strong></span>
</td>
<td width="44%" align="left" valign="top"><span style="font-size:11px;font-family:Arial;margin:0;padding:0"><strong>Nottingham Office</strong><br>Node4 Ltd, The POD,<br>10 Bottle Ln, Nottingham, NG1 2HL</span></td>
</tr>
</tbody>
</table>
<p> </p>
</td>
<td colspan="8" align="right" valign="top">
<table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><a href="http://www.node4.co.uk" target="_blank"><img border="0" alt="Visit www.node4.co.uk" src="cid:118072611430901141@uk-mta-31.uk.mimecast.lan"></a></td></tr></tbody></table>
<table width="100%" height="46" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td colspan="8" height="12" style="font-size:4px" cellpadding="0" cellspacing="0"></td>
</tr>
<tr>
<td> </td>
<td align="right"><table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><a href="http://www.node4.co.uk" target="_blank"><img border="0" alt="Visit www.node4.co.uk" src="cid:118072611430901441@uk-mta-31.uk.mimecast.lan"></a></td></tr></tbody></table></td>
<td> </td>
<td align="right"><table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><a href="http://www.twitter.co.uk/Node4Ltd" target="_blank"><img border="0" alt="Visit Node4 on Twitter" src="cid:118072611430901241@uk-mta-31.uk.mimecast.lan"></a></td></tr></tbody></table></td>
<td> </td>
<td align="right"><table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><a href="https://www.linkedin.com/company/node4-ltd" target="_blank"><img border="0" alt="Visit Node4 on Linkedin" src="cid:118072611430901341@uk-mta-31.uk.mimecast.lan"></a></td></tr></tbody></table></td>
<td> </td>
<td align="right"><table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><a href="http://www.facebook.com/Node4" target="_blank"><img border="0" alt="Visit Node4 on Facebook" src="cid:118072611430901041@uk-mta-31.uk.mimecast.lan"></a></td></tr></tbody></table></td>
</tr>
</tbody>
</table>
<p> </p>
</td>
<td width="10" rowspan="2"> </td>
<td width="40" rowspan="2" valign="top"><table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><img border="0" src="cid:118072611430900941@uk-mta-31.uk.mimecast.lan"></td></tr></tbody></table></td>
</tr>
</tbody>
</table></td>
</tr>
</tbody>
</table>
<table width="800" border="0" cellpadding="0" cellspacing="0">
<tbody><tr>
<td>
<table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><a href="http://info.node4.co.uk/cloud-transformation-consultancy-services-0" target="_blank"><img border="0" src="cid:118072611430901541@uk-mta-31.uk.mimecast.lan"></a></td></tr></tbody></table></td><td>
<table border="0" cellspacing="0" cellpadding="0"><tbody><tr><td><a href="http://info.node4.co.uk/mid-market-it-priorities-in-2018" target="_blank"><img border="0" src="cid:118072611430900841@uk-mta-31.uk.mimecast.lan"></a></td></tr></tbody></table>
</td>
</tr>
</tbody></table>
<p> </p>
</td>
</tr>
<tr>
<td colspan="8">
On Tuesday, 24 July 2018 13:05:07 BST Muhammad Usama wrote:<br>
<br>
Hi Muhammad,<br>
<br>
> trusted_servers config could be used to mitigate the split-brain syndrome<br>
> in older versions of Pgpool-II but<br>
> if you are on 3.6.7 which has the quorum aware watchdog system you don't<br>
> require to use trusted_servers config<br>
> and leave it empty, of course if you don't have some other specific<br>
> requirements as the one I described in above example.<br>
> <br>
> Please let me know if you need further clarification or explanation.<br>
<br>
Would that work in cluster with 2 pgpool nodes ? I'm guessing not, as "quorum" <br>
suggests 3+ servers to be effective in my mind. However after your explanation <br>
it seems to me that pgpool does not behave logically in regards to <br>
"trusted_servers" and introduces some risks by this. </td></tr></tbody></table></div></blockquote><div><br></div><div><div style="font-size:small;text-decoration-style:initial;text-decoration-color:initial;background-color:rgb(255,255,255)">Yes that is correct the recommendation for guarding against split-brain is to use</div><div style="font-size:small;text-decoration-style:initial;text-decoration-color:initial;background-color:rgb(255,255,255)">odd number (minimum 3) Pgpool nodes. </div><br class="gmail-Apple-interchange-newline"> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><table width="800" border="0" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="8">
<br>
I mean, why would any pgpool process stop itself due to failure to communicate <br>
the trusted servers when there is communication with peer over the watchdog <br>
channel ? I understand your hypothetical environment, and indeed, we don't <br>
want to have application/clients connecting to the database via multiple <br>
routes due to delegate IP being up on more than one pgpool server after <br>
network partition. <br>
<br>
But that suggest the communication between nodes is severed, and ping to <br>
trusted servers helps establish if given pgpool still has access to network, <br>
in which case it is plausible that it is expected to have VIP on itself. In <br>
case that it can't ping outside, there is even no point of bringing up the <br>
VIP, as there is network issue and little expectation of traffic from app/<br>
clients will get to it. <br>
<br>
Thinking of it, does this opens us up for a case where network partition runs <br>
between pgpool nodes, and outside connectivity is not affected ? Both nodes <br>
will bring up VIP then, and potentially make a bit of mess..<br>
<br>
Anyway, in case that watchdog is communicating fine with other nodes, but <br>
trusted servers can't be contacted, watchdog should take priority, and dictate <br>
that nothing should be done, imho. <br></td></tr></tbody></table></div></blockquote><div><br></div><div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">As far as "trusted_servers" is concerned it is not designed to tackle split-brain of<br class="gmail-Apple-interchange-newline"></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">pgpool-II nodes. The current watchdog in Pgpool-II uses the quorum to avoid split-brain</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">syndrome and even in 2 nodes cluster it is pretty good in recovering from the split-brain</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">if it happens because of node isolation or some other reason.</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"> <span style="text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">"trusted_servers" is only used to kill the isolated Pgpool-II node and there could be</span></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><span style="text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">many reasons to do that depending upon the requirements. Other than the hypothetical</span></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><span style="text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">scenario I shared earlier ( which you are right, is a very rare) it can be very useful to protect</span></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><span style="text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">the accidental promotion of standby PosggreSQL because of network partitioning.</span></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">For example consider a cluster design with two machines. each having one Pgpool-II and one</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">PostgreSQL instance, and now if for some reason or network glitch one machine losses the network</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">connectivity, Effectively both Pgpool-II nodes will loose the connection with the other Pgpool-II node</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">and PostgreSQL servers hosted on the other machine, but still they will have a connection with their</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">local PostgreSQL instance and both Pgpool-II nodes will eventually conclude that the other PostgreSQL</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">server and other Pgpool-II node is down.</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">After that the watchdog on the standby Pgpool-II node will promote itself to active (since we only have 2</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">nodes and only 1 node is enough to complete the quorum), but its not a big disaster as yet because</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">one Pgpool-II node has no network connection at the moment and even if we have two Pgpool-II nodes</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">thinking themselves as an active Pgpool-II the clients will only see one active Pgpool-II node present in the network,</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">and as soon as the network of the failed-network machine will come back, watchdog will find out about</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">the split-brain and will immediately recover from it.</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">The other thing which is even worse is that both Pgpool-II nodes in this situation will conclude that PostgreSQL instance</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">on other machine is dead and the local one should be promoted to the primary. and will perform failover and promotion.</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">Now when the network on the failed-network machine will come back we will end up with having two primary PostgreSQL</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">instances. and that would be not easily recoverable. But if the trusted_servers is configured on the failed-network machine</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">the Pgpool-II on that machine would kill itself as soon as it become isolated without performing the promotion and failover.</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">So just to summaries this. the trusted_servers have a different use case than to solve</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">the split-brain of Pgpool-II nodes. and for some environments and uses cases the trusted_servers</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">config may look useless but for others it may be a life saver, but it is not designed to complement</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">the guarding of watchdog split-brain.</div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div><div style="font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><br></div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><table width="800" border="0" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="8">
<br>
I think that it may be an edge case, and solution for that would be to get <br>
another pgpool node running. is there a way to make a witness node in pgpool ? <br>
I'm unable to add third pgpool node due to cost, but could install pgpool to <br>
another server to make it 3 nodes. However, that server would be unable to <br>
take production traffic, so should be never able to bring up VIP.<br></td></tr></tbody></table></div></blockquote><div><br></div><div>Yes you use the third Pgpool-II node and set it's wd_priority to lower than other two</div><div>nodes so it will have a least chance to become active.</div><div><br></div><div>Thanks</div><div>Best regards</div><div>Muhammad Usama</div><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><table width="800" border="0" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="8">
<br>
Regards,<br>
Piotr<br>
<br>
-- <br>
<small style="font-family:Arial;color:#8e908f">
<br>Node4 Limited is registered in England No: 04759927 and has its registered office at Millennium Way, Pride Park, Derby, DE24 8HZ<br>
The information contained in this email is confidential and is intended for the exclusive use of the email addressee shown.<br>
If you are not the addressee, any disclosure, reproduction, distribution or other dissemination or use of this communication is strictly prohibited.<br>
If you have received this mail in error, please notify our mail manager at <a href="mailto:abuse@node4.co.uk" style="color:#e05206;text-decoration:none" target="_blank">abuse@node4.co.uk</a> and delete it from your system.<br>
Opinions expressed in this email are those of the individual not the company, unless specifically indicated to that effect.
<hr style="height:0;border-top-style:solid;border-top-color:#d1d4d3;margin:4px 0px;border-width:1px 0 0">
This email has been scanned by Node4's Email Security System.
<hr style="height:0;border-top-style:solid;border-top-color:#d1d4d3;margin:4px 0px;border-width:1px 0 0">
This email message has been delivered safely and archived online by Mimecast.</small>
</td>
</tr>
</tbody>
</table>
</div>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</blockquote></div></div></div>