[pgpool-general: 6179] Re: Fwd: repeat the check of the trusted servers

Sat Aug 4 00:53:01 JST 2018

Hi Piotr,

On Thu, Jul 26, 2018 at 3:43 PM Piotr Gbyliczek <P.Gbyliczek at node4.co.uk>
wrote:

>
>
>   Piotr Gbyliczek
> ------------------------------
> Solutions Engineer
>
> ddi. *08454 210444*
> t. *0845 123 2222*
> e. *P.Gbyliczek at node4.co.uk <P.Gbyliczek at node4.co.uk>* *Nottingham Office*
> Node4 Ltd, The POD,
> 10 Bottle Ln, Nottingham, NG1 2HL
>
>
> [image: Visit www.node4.co.uk] <http://www.node4.co.uk>
>
> [image: Visit www.node4.co.uk] <http://www.node4.co.uk>
> [image: Visit Node4 on Twitter] <http://www.twitter.co.uk/Node4Ltd>
> [image: Visit Node4 on Linkedin]
> <https://www.linkedin.com/company/node4-ltd>
> [image: Visit Node4 on Facebook] <http://www.facebook.com/Node4>
>
>
>
> <http://info.node4.co.uk/cloud-transformation-consultancy-services-0>
> <http://info.node4.co.uk/mid-market-it-priorities-in-2018>
>
> On Tuesday, 24 July 2018 13:05:07 BST Muhammad Usama wrote:
>
> Hi Muhammad,
>
> > trusted_servers config could be used to mitigate the split-brain syndrome
> > in older versions of Pgpool-II but
> > if you are on 3.6.7 which has the quorum aware watchdog system you don't
> > require to use trusted_servers config
> > and leave it empty, of course if you don't have some other specific
> > requirements as the one I described in above example.
> >
> > Please let me know if you need further clarification or explanation.
>
> Would that work in cluster with 2 pgpool nodes ? I'm guessing not, as
> "quorum"
> suggests 3+ servers to be effective in my mind. However after your
> explanation
> it seems to me that pgpool does not behave logically in regards to
> "trusted_servers" and introduces some risks by this.
>

Yes that is correct the recommendation for guarding against split-brain is
to use
odd number (minimum 3) Pgpool nodes.

>
> I mean, why would any pgpool process stop itself due to failure to
> communicate
> the trusted servers when there is communication with peer over the
> watchdog
> channel ? I understand your hypothetical environment, and indeed, we don't
> want to have application/clients connecting to the database via multiple
> routes due to delegate IP being up on more than one pgpool server after
> network partition.
>
> But that suggest the communication between nodes is severed, and ping to
> trusted servers helps establish if given pgpool still has access to
> network,
> in which case it is plausible that it is expected to have VIP on itself.
> In
> case that it can't ping outside, there is even no point of bringing up the
> VIP, as there is network issue and little expectation of traffic from app/
> clients will get to it.
>
> Thinking of it, does this opens us up for a case where network partition
> runs
> between pgpool nodes, and outside connectivity is not affected ? Both
> nodes
> will bring up VIP then, and potentially make a bit of mess..
>
> Anyway, in case that watchdog is communicating fine with other nodes, but
> trusted servers can't be contacted, watchdog should take priority, and
> dictate
> that nothing should be done, imho.
>

As far as "trusted_servers" is concerned it is not designed to tackle
split-brain of
pgpool-II nodes. The current watchdog in Pgpool-II uses the quorum to avoid
split-brain
syndrome and even in 2 nodes cluster it is pretty good in recovering from
the split-brain
if it happens because of node isolation or some other reason.

 "trusted_servers" is only used to kill the isolated Pgpool-II node and
there could be
many reasons to do that depending upon the requirements. Other than the
hypothetical
scenario I shared earlier ( which you are right, is a very rare) it can be
very useful to protect
the accidental promotion of standby PosggreSQL because of network
partitioning.

For example consider a cluster design with two machines. each having one
Pgpool-II and one
PostgreSQL instance, and now if for some reason or network glitch one
machine losses the network
connectivity, Effectively both Pgpool-II nodes will loose the connection
with the other Pgpool-II node
and PostgreSQL servers hosted on the other machine, but still they will
have a connection with their
local PostgreSQL instance and both Pgpool-II nodes will eventually conclude
that the other PostgreSQL
server and other Pgpool-II node is down.
After that the watchdog on the standby Pgpool-II node will promote itself
to active (since we only have 2
nodes and only 1 node is enough to complete the quorum), but its not a big
disaster as yet because
one Pgpool-II node has no network connection at the moment and even if we
have two Pgpool-II nodes
thinking themselves as an active Pgpool-II the clients will only see one
active Pgpool-II node present in the network,
and as soon as the network of the failed-network machine will come back,
watchdog will find out about
the split-brain and will immediately recover from it.

The other thing which is even worse is that both Pgpool-II nodes in this
situation will conclude that PostgreSQL instance
on other machine is dead and the local one should be promoted to the
primary. and will perform failover and promotion.
Now when the network on the failed-network machine will come back we will
end up with having two primary PostgreSQL
instances. and that would be not easily recoverable. But if the
trusted_servers is configured on the failed-network machine
the Pgpool-II on that machine would kill itself as soon as it become
isolated without performing the promotion and failover.

So just to summaries this. the trusted_servers have a different use case
than to solve
the split-brain of Pgpool-II nodes. and for some environments and uses
cases the trusted_servers
config may look useless but for others it may be a life saver, but it is
not designed to complement
the guarding of watchdog  split-brain.

>
> I think that it may be an edge case, and solution for that would be to get
> another pgpool node running. is there a way to make a witness node in
> pgpool ?
> I'm unable to add third pgpool node due to cost, but could install pgpool
> to
> another server to make it 3 nodes. However, that server would be unable to
> take production traffic, so should be never able to bring up VIP.
>

Yes you use the third Pgpool-II node and set it's wd_priority to lower than
other two
nodes so it will have a least chance to become active.

Thanks
Best regards
Muhammad Usama

> Regards,
> Piotr
>
> --
>
> Node4 Limited is registered in England No: 04759927 and has its registered
> office at Millennium Way, Pride Park, Derby, DE24 8HZ
> The information contained in this email is confidential and is intended
> for the exclusive use of the email addressee shown.
> If you are not the addressee, any disclosure, reproduction, distribution
> or other dissemination or use of this communication is strictly prohibited.
> If you have received this mail in error, please notify our mail manager at
> abuse at node4.co.uk and delete it from your system.
> Opinions expressed in this email are those of the individual not the
> company, unless specifically indicated to that effect.
> ------------------------------
> This email has been scanned by Node4's Email Security System.
> ------------------------------
> This email message has been delivered safely and archived online by
> Mimecast.
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430901741.jpg
Type: image/jpeg
Size: 10120 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0003.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430902041.png
Type: image/png
Size: 1315 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0006.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430902241.png
Type: image/png
Size: 16076 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0007.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430902441.png
Type: image/png
Size: 19451 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430902641.png
Type: image/png
Size: 16233 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0009.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430902841.png
Type: image/png
Size: 16168 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0010.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430903241.png
Type: image/png
Size: 17391 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0011.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430903441.jpg
Type: image/jpeg
Size: 10763 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0004.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 118072611430903441.jpg
Type: image/jpeg
Size: 10763 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180803/0f5725a1/attachment-0005.jpg>