<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Jan 12, 2017 at 7:34 AM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">This is a proposal for a new feature toward Pgpool-II 3.7.<br>
<br>
Currently Pgpool-II finds a primary node and <span id="gmail-8d8d1ac0-9299-4356-9c13-6d3b1a4bbb16" class="gmail-GINGER_SOFTWARE_mark">standby node</span> like this<br>
(<span id="gmail-837bc609-1e5a-4703-988f-8dabc35d8812" class="gmail-GINGER_SOFTWARE_mark">it</span> happens while Pgpool-II <span id="gmail-f1905aed-21a0-4f93-8a25-60286a404d69" class="gmail-GINGER_SOFTWARE_mark">starting</span> up or failover):<br>
<br>
1) Issue "SELECT pg_is_in_recovery<span id="gmail-14c34356-d034-4ff9-80c5-51de4bf72668" class="gmail-GINGER_SOFTWARE_mark">(</span>)" to a node in question.<br>
<br>
2) If it returns "t", then decide the node is standby. Go to <span id="gmail-0d686025-1dcd-45f4-b319-5b69f4aa1ab8" class="gmail-GINGER_SOFTWARE_mark">next node</span><br>
(<span id="gmail-25936c40-4484-4403-a077-fd6117a137ee" class="gmail-GINGER_SOFTWARE_mark">go</span> back to step 1).<br>
<br>
3) If it returns other than that, then decide the node is<br>
<span id="ef85d495-e3d4-4c46-8921-f08ea742a823" class="gmail-GINGER_SOFTWARE_mark GINGER_SOFTWARE_mark">the</span> primary. Other nodes are regarded as standby.<br>
<br>
This logic works mostly <span id="ed919f4d-679f-41d3-b70b-8995941c7156" class="gmail-GINGER_SOFTWARE_mark GINGER_SOFTWARE_mark">well except</span> in an unusual scenario like this:<br>
<br>
i) We have two <span id="gmail-c0cfb6a5-ab3a-4eb9-bade-27623933dbfb" class="gmail-GINGER_SOFTWARE_mark">nodes</span>: 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>
<span id="gmail-d3ce172a-d33c-4615-9556-da3882455b02" class="gmail-GINGER_SOFTWARE_mark">a</span> stand alone PostgreSQL.<br>
<br>
In this case, eventually node 1 will be behind to node 0, because no<br>
<span id="gmail-8c1cd5b4-22f9-4e2b-829e-a101cd7b9407" class="gmail-GINGER_SOFTWARE_mark">replication</span> happens. If <span id="gmail-c862fd22-ab67-430f-833b-600cde8016cf" class="gmail-GINGER_SOFTWARE_mark">replication delay check</span> is enabled, Pgpool-II<br>
<span id="gmail-2c2c412f-d9cc-4aa4-a45a-6c38c1b8464f" class="gmail-GINGER_SOFTWARE_mark">avoids</span> to send queries to node 1 because of the replication<br>
<span id="gmail-a457e64f-764a-4f2f-b4bc-ad9a570d823f" class="gmail-GINGER_SOFTWARE_mark">delay</span>. However, if the replication delay check is not enabled or the<br>
<span id="gmail-5e40266c-d977-4874-8091-6f0a747090d2" class="gmail-GINGER_SOFTWARE_mark">replication delay threshold</span> is large, <span id="gmail-297562fb-32a1-473c-926b-940c750150d5" class="gmail-GINGER_SOFTWARE_mark">user</span> will not notice the<br>
<span id="gmail-f19c5aed-95ca-4227-b8cc-ae5ff3f36fdc" class="gmail-GINGER_SOFTWARE_mark">situation</span>.<br>
<br>
Also the scenario is known as "split brain" which users want to<br>
<span id="gmail-0520c6a9-cb05-488e-a834-5be2b6e6b09f" class="gmail-GINGER_SOFTWARE_mark">avoid</span>. 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<span id="gmail-5d9c78b4-310b-42aa-9ae4-159808943eea" class="gmail-GINGER_SOFTWARE_mark">(</span>)" to a node in question.<br>
<br>
2) If it returns "t", then decide the node is standby. Go to <span id="gmail-6b696f20-3e0e-4912-818f-b8cf0ed5aa01" class="gmail-GINGER_SOFTWARE_mark">next node</span><br>
(<span id="gmail-d080e52d-8062-4526-8d9d-0b3d4490cc26" class="gmail-GINGER_SOFTWARE_mark">go</span> back to step 1).<br>
<br>
3) If it returns other than that, then decide the node is the<br>
<span id="gmail-1ba5d9e6-d53e-465a-b926-a2febf0780c2" class="gmail-GINGER_SOFTWARE_mark">primary</span>. Check remaining <span id="gmail-2e4b52f9-5773-43e4-83bd-ef8b45b027db" class="gmail-GINGER_SOFTWARE_mark">nodes whether</span> they are actually standby or<br>
<span id="gmail-a1933b57-d75e-4656-acd4-37fc334c98bd" class="gmail-GINGER_SOFTWARE_mark">not</span> by issuing "SELECT pg_is_in_recovery<span id="e8b3191d-0a86-4e81-9453-8536d866aff1" class="gmail-GINGER_SOFTWARE_mark GINGER_SOFTWARE_mark">(</span>)". <span id="gmail-1e6f3ed3-0acc-4239-b077-03ef7d249e54" class="gmail-GINGER_SOFTWARE_mark">Additionally we</span> could<br>
<span id="gmail-ab03472d-f934-4b38-b514-72a609849e81" class="gmail-GINGER_SOFTWARE_mark">use</span> <span id="gmail-4db062fa-3a7d-401e-ac1e-6c6af0719808" class="gmail-GINGER_SOFTWARE_mark">pg_stat_wal_receiver view</span> to check if it actually connects to<br>
<span id="gmail-4e2e49c7-aa68-44ff-acbc-904973d46163" class="gmail-GINGER_SOFTWARE_mark">the</span> primary node if the PostgreSQL version is 9.6 or higher.<br>
<br></blockquote><div><br></div><div>I think <span id="gmail-4eda404f-baca-425e-9d9f-9d21c8065426" class="gmail-GINGER_SOFTWARE_mark">it's</span> a very good feature to have especially with the PostgreSQL version is 9.6 and higher if we can verify that all the configured backends belongs to the same cluster and are in the correct state. Because although unlikely, but the misconfigurations like that are very hard to catch otherwise.</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<span id="gmail-6b53677a-b376-490a-b666-50ff84a4fc1b" class="gmail-GINGER_SOFTWARE_mark">Question</span> is, what if the checking in #3 reveals that the node in<br>
<span id="e7344f4e-a74e-4cc6-8293-63218a072f15" class="gmail-GINGER_SOFTWARE_mark GINGER_SOFTWARE_mark">question</span> is not "proper" standby.<br>
<br>
- Do we want to add new status code other than "up", "down", "not<br>
<span id="gmail-fa746d03-7f4e-469a-a1a2-511213ee50e9" class="gmail-GINGER_SOFTWARE_mark">connected</span>" and "unused"?<br></blockquote><div><br></div><div>In my opinion, adding a new status "not connected" or "invalid node" should be more helpful.</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
- Do we want to automatically detach the node so that Pgpool-II does<br>
<span id="gmail-fe6df0a1-2c99-423a-a52d-16c884a7dc50" class="gmail-GINGER_SOFTWARE_mark">not</span> use the node?<br></blockquote><div><br></div><div>I think as soon as Pgpool-II detects that the backend node is not connected to the valid primary PostgreSQL, we should perform the failover on that node to make sure that all sessions using the node should immediately stop using it and also the database administrator can take desired and appropriate actions on that node in the failover script.</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
- Do we want to the check <span id="gmail-97c01067-683b-406a-99f7-2707c9e702a3" class="gmail-GINGER_SOFTWARE_mark">more ferequetly</span>, say a similar timing as<br>
<span id="e4bdb3db-5460-4db7-bed8-c2dc458913f8" class="gmail-GINGER_SOFTWARE_mark GINGER_SOFTWARE_mark">health</span> checking?<br></blockquote><div> </div><div>May be we can change the health check query so that it can serve both purposes. I.e. Check the health and also the status of the backend node at same time.</div><div><br></div><div>Thanks</div><div>Best regards</div><div>Muhammad Usama</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
Comments, suggestions are welcome. </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
______________________________<wbr>_________________<br>
<span id="ee3e8f59-bfea-44e0-8670-aa2d830a4560" class="gmail-GINGER_SOFTWARE_mark GINGER_SOFTWARE_mark">pgpool</span>-hackers mailing list<br>
<a href="mailto:pgpool-hackers@pgpool.net">pgpool-hackers@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-hackers" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/<wbr>listinfo/pgpool-hackers</a><br>
</blockquote></div><br></div></div>