<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<p><br>
</p>
Hi The Pgpool Community,</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
I'm not sure my understanding of the question is clean enough but to me it seems the pgpool faces two different cases which I want to highlight:</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
1) It starts and detects all backends (say we have 2 backends) are not is recovery mode</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
At this moment if pgpool continues starting than it will damage data which is not acceptable. </div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
2) The backends eventually become Masters (split brain).</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
In this case there should be some logic that detects (on the fly) the Split Brain case, probably the lifecheck task can do that - query pg_is_in_recovery().<br>
<br>
Here what we see and what will be possible to extend:<br>
Would it be possible to tirgger some script in both cases and provide parameters into it which will be responsible for the choosing of the incorrect "Master". It seems the pgpool has to be shipped with the default script but it still should be possible to update
it. Thus the customers will be free to consider different self developed scenarios - how to resolve the Split Brain.</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
<br>
<div id="divtagdefaultwrapper" style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<font face="Calibri,Arial,Helvetica,sans-serif" size="2"><span style="font-size: 9pt;">Best Regards,</span></font><br>
<font face="Calibri,Arial,Helvetica,sans-serif" size="2"><span style="font-size: 9pt;">Sergey</span></font></div>
<div id="divtagdefaultwrapper" style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<font face="Calibri,Arial,Helvetica,sans-serif"><font size="2"><span style="font-size: 9pt;">HA Architect as </span></font><font size="2"><span style="font-size: 9pt;">Od</span></font><font size="2"><span style="font-size: 9pt;">in</span></font></font></div>
<div id="divtagdefaultwrapper" style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<font face="Calibri,Arial,Helvetica,sans-serif"><font size="2"><span style="font-size: 9pt;">http://www.odin.com</span></font></font></div>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
<div style="color: rgb(0, 0, 0);">
<div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> pgpool-hackers-bounces@pgpool.net <pgpool-hackers-bounces@pgpool.net> on behalf of pgpool-hackers-request@pgpool.net <pgpool-hackers-request@pgpool.net><br>
<b>Sent:</b> Thursday, January 12, 2017 6:00 AM<br>
<b>To:</b> pgpool-hackers@pgpool.net<br>
<b>Subject:</b> pgpool-hackers Digest, Vol 63, Issue 14</font>
<div> </div>
</div>
</div>
<font size="2"><span style="font-size:10pt;">
<div class="PlainText">Send pgpool-hackers mailing list submissions to<br>
pgpool-hackers@pgpool.net<br>
<br>
To subscribe or unsubscribe via the World Wide Web, visit<br>
<a href="http://www.sraoss.jp/mailman/listinfo/pgpool-hackers" id="LPlnk525560" previewremoved="true">
http://www.sraoss.jp/mailman/listinfo/pgpool-hackers</a><br>
or, via email, send a message with subject or body 'help' to<br>
pgpool-hackers-request@pgpool.net<br>
<br>
You can reach the person managing the list at<br>
pgpool-hackers-owner@pgpool.net<br>
<br>
When replying, please edit your Subject line so it is more specific<br>
than "Re: Contents of pgpool-hackers digest..."<br>
<br>
<br>
Today's Topics:<br>
<br>
1. [pgpool-hackers: 1979] New feature candidate: verify standby<br>
node while finding primary node (Tatsuo Ishii)<br>
<br>
<br>
----------------------------------------------------------------------<br>
<br>
Message: 1<br>
Date: Thu, 12 Jan 2017 11:34:59 +0900 (JST)<br>
From: Tatsuo Ishii <ishii@sraoss.co.jp><br>
To: pgpool-hackers@pgpool.net<br>
Subject: [pgpool-hackers: 1979] New feature candidate: verify standby<br>
node while finding primary node<br>
Message-ID: <20170112.113459.213399609554012313.t-ishii@sraoss.co.jp><br>
Content-Type: Text/Plain; charset=us-ascii<br>
<br>
This is a proposal for a new feature toward Pgpool-II 3.7.<br>
<br>
Currently Pgpool-II finds a primary node and standby node like this<br>
(it happens while Pgpool-II starting up or failover):<br>
<br>
1) Issue "SELECT pg_is_in_recovery()" to a node in question.<br>
<br>
2) If it returns "t", then decide the node is standby. Go to next node<br>
(go back to step 1).<br>
<br>
3) If it returns other than that, then decide the node is<br>
the primary. Other nodes are regarded as standby.<br>
<br>
This logic works mostly well except in an unusual scenario like this:<br>
<br>
i) We have two nodes: node 0 is primary, node 1 is standby.<br>
<br>
ii) A stupid admin issues "pg_ctl promote" to the standby node and node 1 becomes<br>
a stand alone PostgreSQL.<br>
<br>
In this case, eventually node 1 will be behind to node 0, because no<br>
replication happens. If replication delay check is enabled, Pgpool-II<br>
avoids to send queries to node 1 because of the replication<br>
delay. However, if the replication delay check is not enabled or the<br>
replication delay threshold is large, user will not notice the<br>
situation.<br>
<br>
Also the scenario is known as "split brain" which users want to<br>
avoid. I think we need to do something here.<br>
<br>
Here is the modified procedure to avoid it.<br>
<br>
1) Issue "SELECT pg_is_in_recovery()" to a node in question.<br>
<br>
2) If it returns "t", then decide the node is standby. Go to next node<br>
(go back to step 1).<br>
<br>
3) If it returns other than that, then decide the node is the<br>
primary. Check remaining nodes whether they are actually standby or<br>
not by issuing "SELECT pg_is_in_recovery()". Additionally we could<br>
use pg_stat_wal_receiver view to check if it actually connects to<br>
the primary node if the PostgreSQL version is 9.6 or higher.<br>
<br>
Question is, what if the checking in #3 reveals that the node in<br>
question is not "proper" standby.<br>
<br>
- Do we want to add new status code other than "up", "down", "not<br>
connected" and "unused"?<br>
<br>
- Do we want to automatically detach the node so that Pgpool-II does<br>
not use the node?<br>
<br>
- Do we want to the check more ferequetly, say a similar timing as<br>
health checking?<br>
<br>
Comments, suggestions are welcome.<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" id="LPlnk628704" previewremoved="true">
http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:http://www.sraoss.co.jp<br>
<br>
<br>
------------------------------<br>
<br>
_______________________________________________<br>
pgpool-hackers mailing list<br>
pgpool-hackers@pgpool.net<br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-hackers" id="LPlnk536740" previewremoved="true">http://www.pgpool.net/mailman/listinfo/pgpool-hackers</a><br>
<br>
<br>
End of pgpool-hackers Digest, Vol 63, Issue 14<br>
**********************************************<br>
</div>
</span></font></div>
</div>
</div>
</body>
</html>