<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Mar 10, 2017 at 11:05 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">Usama,<br>
<br>
I have a question regarding <span id="gmail-60f0c21f-9366-4945-a944-271d5b01c890" class="gmail-GINGER_SOFTWARE_mark">Zone partitioning case</span> described in<br>
<span id="gmail-f08ec8d6-931c-4a43-961d-070c473bdfac" class="gmail-GINGER_SOFTWARE_mark">section</span> 2 in your proposal. In my understanding after the network<br>
<span id="gmail-59e0b95b-6057-4296-b195-ae069e7fcd90" class="gmail-GINGER_SOFTWARE_mark">partitioning</span> happens, Pgpool-II/watchdog in zone 2 will suicide<br>
<span id="gmail-11eec64f-7e81-4765-95f7-30c2decaf84f" class="gmail-GINGER_SOFTWARE_mark">because</span> they cannot acquire quorum. So split-brain or data<br>
<span id="gmail-6460d107-740a-4786-9205-62d7bd8f8533" class="gmail-GINGER_SOFTWARE_mark">inconsistency</span> due to two master node will not happen in even in<br>
Pgpool-II 3.6. Am I missing something?<br></blockquote><div><br></div><div>With the current design of watchdog the Pgpool-II/Watchdog commits suicide in only two cases.</div><div><br></div><div>1- When all network interfaces on the machine becomes unavailable(machine lost all IP addresses).</div><div>2- When connection to the up-stream trusted server becomes unreachable (if <span style="font-variant-ligatures:no-common-ligatures;color:rgb(0,0,0);font-family:menlo;font-size:11px">trusted_servers </span>are configured<span style="font-variant-ligatures:no-common-ligatures;color:rgb(0,0,0);font-family:menlo;font-size:11px">)</span></div><div><br></div><div>So in zone partitioning scenario described in section 2 the Pgpool-II nodes in zone 2 will not commit suicide because none</div><div>of the above two conditions for node suicide exists.</div><div><br></div><div>Also, doing the suicide as soon as the cluster looses the quorum doesn't feel like a good option because if we implement that we will end up with all the Pgpool-II nodes committing suicide as soon as the quorum is lost in the cluster and eventually the Pgpool-II service will become unavailable, and the administrator would require to manually restart Pgpool-II nodes.</div><div>Current implementation makes sure that split-brain does not happen when a quorum is not available and at the same time keep looking for new/old-lost nodes to join back the cluster to make sure minimum possible service disruption happen and cluster recovers automatically without any manual intervention.</div><div><br></div><div><br></div><div>Thanks</div><div>Best regards</div><div>Muhammad Usama</div><div><br></div></div><div class="gmail_quote"><br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<span class="gmail-"><br>
Best regards,<br>
--<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>
<br>
</span>From: Muhammad Usama <<a href="mailto:m.usama@gmail.com">m.usama@gmail.com</a>><br>
Subject: Re: Proposal to make <span id="gmail-5907f04e-272e-4580-92ee-3e9f9e935dc3" class="gmail-GINGER_SOFTWARE_mark">backend</span> node failover mechanism quorum aware<br>
Date: Thu, 9 Mar 2017 00:57:58 +0500<br>
Message-ID: <CAEJvTzXap+qMGLt7SQ-1hPgf=<a href="mailto:aNuAYEsu_JQYd695hac0WagkA@mail.gmail.com">aNu<wbr>AYEsu_JQYd695hac0WagkA@mail.<wbr>gmail.com</a>><br>
<div class="gmail-HOEnZb"><div class="gmail-h5"><br>
> Hi<br>
><br>
> Please use this document. The image quality of the previously shared<br>
> <span id="gmail-19ce23da-e592-4c44-98ef-acbc39c1cf62" class="gmail-GINGER_SOFTWARE_mark">version</span> was not up to the mark.<br>
><br>
> Thanks<br>
> Best regards<br>
> Muhammad Usama<br>
><br>
> On Thu, Mar 9, 2017 at 12:53 AM, Muhammad Usama <<a href="mailto:m.usama@gmail.com">m.usama@gmail.com</a>> wrote:<br>
><br>
>> Hi Ishii-San<br>
>><br>
>> I have tried to create a detailed proposal to explain why and where the<br>
>> <span id="gmail-694389f3-3186-4ccf-a2c3-6f09938ee8ca" class="gmail-GINGER_SOFTWARE_mark">quorum</span> aware <span id="gmail-6cb028a5-bd23-4d14-a4cb-f456e27bdd75" class="gmail-GINGER_SOFTWARE_mark">backend</span> failover mechanism would be useful.<br>
>> Can you please take a look at the attached pdf document and share your<br>
>> <span id="gmail-56f0ae92-279c-4c22-84c9-81175cf1f2f0" class="gmail-GINGER_SOFTWARE_mark">thoughts</span>.<br>
>><br>
>> Thanks<br>
>> Kind Regards<br>
>> Muhammad Usama<br>
>><br>
>><br>
>> On Wed, Jan 25, 2017 at 2:04 PM, Muhammad Usama <<a href="mailto:m.usama@gmail.com">m.usama@gmail.com</a>> wrote:<br>
>><br>
>>><br>
>>><br>
>>> On Wed, Jan 25, 2017 at 9:05 AM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br>
>>><br>
>>>> Usama,<br>
>>>><br>
>>>> > This is correct. If the Pgpool-II is used in <span id="gmail-b7a6b2e0-7228-4201-9ee7-0da00ec8bd0e" class="gmail-GINGER_SOFTWARE_mark">maste</span>-standby mode (With<br>
>>>> > <span id="gmail-8ccbb803-34d7-46c5-9262-65d513367117" class="gmail-GINGER_SOFTWARE_mark">elastic</span> or virtual-IP and clients only connect to one Pgpool-II server<br>
>>>> > <span id="gmail-7cb68c75-7f69-4344-a427-94497186542a" class="gmail-GINGER_SOFTWARE_mark">only</span>) then there is not much issues that could be caused by the<br>
>>>> > <span id="e03071d4-dcf6-4119-9188-8e5b853c5fee" class="gmail-GINGER_SOFTWARE_mark">interruption</span> of link between AZ1 and AZ2 as you defined above.<br>
>>>> ><br>
>>>> > But the issue arrives when the Pgpool-II is used in the master-master<br>
>>>> > <span id="gmail-328fa152-bee9-4a96-95c7-28c713e2ac51" class="gmail-GINGER_SOFTWARE_mark">mode</span><span id="gmail-5cc96e50-ce4f-4d5a-b2e6-97104f80c856" class="gmail-GINGER_SOFTWARE_mark">(</span>clients connect to all available Pgpool-II) then consider the<br>
>>>> > <span id="gmail-bf43f341-8343-49c2-8c2d-705ad79f8a6e" class="gmail-GINGER_SOFTWARE_mark">following</span> scenario.<br>
>>>> ><br>
>>>> > a) Link between AZ1 and AZ2 broke, at that time B1 was master while B2<br>
>>>> <span id="gmail-dd85ad53-6457-46a6-ad9f-1f78730006cf" class="gmail-GINGER_SOFTWARE_mark">was</span><br>
>>>> > <span id="gmail-c334542e-a323-410d-b3d0-be7db6e21a2d" class="gmail-GINGER_SOFTWARE_mark">standby</span>.<br>
>>>> ><br>
>>>> > b) <span id="gmail-d9dcd38f-4153-4e29-98fa-7537905b58de" class="gmail-GINGER_SOFTWARE_mark">Pgpool</span>-C in AZ2 <span id="gmail-0969b5fe-e692-419f-aa8f-47bb1b1927b0" class="gmail-GINGER_SOFTWARE_mark">promote</span> B2 to the master since Pgpool-C is <span id="gmail-8a1b1a96-a7b2-4cbd-ae0e-f383222abd6b" class="gmail-GINGER_SOFTWARE_mark">not able</span><br>
>>>> <span id="gmail-252b79d1-4daf-47bc-8613-80e1f30f8366" class="gmail-GINGER_SOFTWARE_mark">to</span><br>
>>>> > <span id="gmail-31ef66ca-043d-4486-bf0b-814ac8b70346" class="gmail-GINGER_SOFTWARE_mark">connect</span> <span id="gmail-2978425e-2928-4bee-94a2-0eb4346e339f" class="gmail-GINGER_SOFTWARE_mark">old master</span> (B1)<br>
>>>><br>
>>>> I thought Pgpool-C <span id="gmail-ce87657f-5810-4c14-a593-eaa3f8005f84" class="gmail-GINGER_SOFTWARE_mark">sucides</span> because it cannot <span id="gmail-b934c1ed-a17e-40bf-bd8a-59c4b64cbdf6" class="gmail-GINGER_SOFTWARE_mark">get quorum</span> in this case, no?<br>
>>>><br>
>>><br>
>>> No, Pgpool-II only commits suicide only when it loses all <span id="gmail-4d7251e2-788d-4396-a696-ef2efa6adc33" class="gmail-GINGER_SOFTWARE_mark">network</span><br>
>>> <span id="gmail-de6404e5-84f2-42b2-a212-3545fe61de2f" class="gmail-GINGER_SOFTWARE_mark">connections</span>. Otherwise the master watchdog node is de-escalated when the<br>
>>> <span id="gmail-40fe0ce1-28f5-4ffa-8804-a613f5879364" class="gmail-GINGER_SOFTWARE_mark">quorum</span> is lost.<br>
>>> Committing a suicide <span id="gmail-5b968d8b-ce49-4a4c-bc66-f9fd13794a80" class="gmail-GINGER_SOFTWARE_mark">everytime</span> quorum is lost is very risky and not<br>
>>> <span id="gmail-ba3ae4a8-fd53-4991-8b97-83084245c334" class="gmail-GINGER_SOFTWARE_mark">a</span> feasible since it will <span id="e457a9f0-e385-44cf-8cc0-bafc1deb83c7" class="gmail-GINGER_SOFTWARE_mark">shutdown</span> the whole cluster as soon as a quorum<br>
>>> <span id="gmail-3665050c-0cf2-48c3-9d8a-07bfe25d04cf" class="gmail-GINGER_SOFTWARE_mark">loses</span> even because of a small glitch.<br>
>>><br>
>>><br>
>>>> > c) A client connects to Pgpool-C and issues a write statement. It will<br>
>>>> <span id="gmail-3560252e-5039-4261-9f78-e091bf92862e" class="gmail-GINGER_SOFTWARE_mark">land</span><br>
>>>> > <span id="gmail-7e6908b3-ae21-428b-83f2-c95485315f99" class="gmail-GINGER_SOFTWARE_mark">on</span> the B2 PostgreSQL server, which was promoted as <span id="gmail-ab998506-6745-41f7-a601-ed0574746b71" class="gmail-GINGER_SOFTWARE_mark">master</span> in step b.<br>
>>>> ><br>
>>>> > <span id="gmail-f15e2a78-64a4-41c6-82a0-edd97d684ff6" class="gmail-GINGER_SOFTWARE_mark">c</span>-1) Another client connects to Pgpool-A and also issues a write<br>
>>>> <span id="edee6b85-b0a4-4adc-bf01-4d65d5581285" class="gmail-GINGER_SOFTWARE_mark">statement</span><br>
>>>> > <span id="gmail-2bd62610-7ba9-4176-870f-1fd60b38118b" class="gmail-GINGER_SOFTWARE_mark">that</span> will land on the B1 PostgreSQL server as it the master node in AZ.<br>
>>>> ><br>
>>>> > d) The link between AZ1 and AZ2 is restored, but now the PostgreSQL B1<br>
>>>> <span id="gmail-5814871c-098f-45da-a8e0-09fd2b16bed3" class="gmail-GINGER_SOFTWARE_mark">and</span><br>
>>>> > B2 both have different sets of data and with no easy way to get both<br>
>>>> > <span id="gmail-9d1a6c38-bc0d-4e93-b467-7cbcf5c27b78" class="gmail-GINGER_SOFTWARE_mark">changes</span> in one place and restore the cluster to original state.<br>
>>>> ><br>
>>>> > The above scenario will become more complicated if both availability<br>
>>>> <span id="gmail-5b976adb-5845-4e43-b45e-5ef8d09771e7" class="gmail-GINGER_SOFTWARE_mark">zones</span><br>
>>>> > AZ1 and AZ2 have multiple Pgpool-II nodes, since retiring the multiple<br>
>>>> > Pgpool-II <span id="gmail-690d3e4c-500d-4f75-93fa-42a85003fff5" class="gmail-GINGER_SOFTWARE_mark">nodes</span> logic will become more complex when link disruption<br>
>>>> <span id="ece05700-c82d-4ffa-966f-61ddb28ef5b6" class="gmail-GINGER_SOFTWARE_mark">between</span><br>
>>>> > AZ1 and AZ2.<br>
>>>> ><br>
>>>> > So the proposal tries to solve this by making sure that we should<br>
>>>> <span id="gmail-4a126c3d-bdcc-4e1f-b6e9-1c571141aab7" class="gmail-GINGER_SOFTWARE_mark">always</span><br>
>>>> > <span id="e2ea8b4d-4e56-4132-8253-1af87f16807c" class="gmail-GINGER_SOFTWARE_mark">have</span> only one master PostgreSQL node in the cluster and never end up<br>
>>>> <span id="gmail-0c47a6cf-f88c-46f1-b704-6f9815eb3c7b" class="gmail-GINGER_SOFTWARE_mark">in</span> the<br>
>>>> > <span id="gmail-b7d9e59d-ab83-4533-9947-f41ef7ebfa58" class="gmail-GINGER_SOFTWARE_mark">situation</span> where we have different sets of data in different PostgreSQL<br>
>>>> > <span id="gmail-d8957651-ecc0-48d0-a844-b3bbc269ba82" class="gmail-GINGER_SOFTWARE_mark">nodes</span>.<br>
>>>> ><br>
>>>> ><br>
>>>> ><br>
>>>> >> > There is also a question ("[<span id="gmail-6bd4b023-5c08-4e95-9aab-b913a248a87f" class="gmail-GINGER_SOFTWARE_mark">pgpool</span>-general: 5179] Architecture<br>
>>>> Questions<br>
>>>> >> > <<a href="http://www.sraoss.jp/pipermail/pgpool-general/2016-December" rel="noreferrer" target="_blank">http://www.sraoss.jp/<wbr>pipermail/pgpool-general/2016-<wbr>December</a><br>
>>>> /005237.html<br>
>>>> >> >")<br>
>>>> >> > <span id="gmail-80d65dcb-ca37-44f3-83cf-39c68a9d6aad" class="gmail-GINGER_SOFTWARE_mark">posted</span> by a user in <span id="gmail-c100ea7b-77ee-4199-95c2-47472ab7fe95" class="gmail-GINGER_SOFTWARE_mark">pgpool</span>-general mailing list who wants a similar<br>
>>>> <span id="gmail-2ba4af8a-cea0-4827-98a9-4ca4afc30930" class="gmail-GINGER_SOFTWARE_mark">type</span><br>
>>>> >> <span id="gmail-d8b83ce6-0054-4a39-b912-658c9f8f3de6" class="gmail-GINGER_SOFTWARE_mark">of</span><br>
>>>> >> > <span id="gmail-483fbbd4-ad27-4ad1-97ac-fe97c9e158a2" class="gmail-GINGER_SOFTWARE_mark">network</span> that spans over two AWS availability zones and Pgpool-II<br>
>>>> <span id="e3611412-d27c-4d88-b77a-6e90722abe64" class="gmail-GINGER_SOFTWARE_mark">has</span> no<br>
>>>> >> > <span id="gmail-d70556db-b2d2-4ed4-92ba-660959a91282" class="gmail-GINGER_SOFTWARE_mark">good</span> answer to avoid split-brain of <span id="gmail-5e49a1ee-af68-494c-a764-8296c836c192" class="gmail-GINGER_SOFTWARE_mark">backend</span> nodes if the corporate<br>
>>>> <span id="gmail-8ffabccf-78a2-4a3b-92bd-e026d4922425" class="gmail-GINGER_SOFTWARE_mark">link</span><br>
>>>> >> > <span id="gmail-ca6e8bca-dc74-4235-b414-6ff5dd82be1b" class="gmail-GINGER_SOFTWARE_mark">between</span> two zones suffers a glitch.<br>
>>>> >><br>
>>>> >> That seems totally different story to me because <span id="gmail-c1494263-0deb-400e-8e89-7e250503dd8c" class="gmail-GINGER_SOFTWARE_mark">there</span> two independent<br>
>>>> >> <span id="gmail-499f50e9-5bb5-4d56-8532-79062ed2ab3a" class="gmail-GINGER_SOFTWARE_mark">streaming</span> replication primary servers in the east and west regions.<br>
>>>> >><br>
>>>> >><br>
>>>> > I think the original question statement was a little bit confusing.<br>
>>>> <span id="gmail-a0fe73f3-866d-474e-8072-d27332b4485e" class="gmail-GINGER_SOFTWARE_mark">How</span> I<br>
>>>> > <span id="gmail-749c1579-a240-4806-8a69-6064486df05d" class="gmail-GINGER_SOFTWARE_mark">understand</span> the user requirements later in the thread was that.<br>
>>>> > The user has a couple of PostgreSQL nodes in two availability zones<br>
>>>> (<span id="gmail-fd30f623-7b19-49f2-914b-75dda0c98997" class="gmail-GINGER_SOFTWARE_mark">total</span><br>
>>>> > 4 PG nodes) and all four nodes are part of the single streaming<br>
>>>> <span id="e9841f27-ea21-4b16-8c64-9e1b354e918b" class="gmail-GINGER_SOFTWARE_mark">replication</span><br>
>>>> > <span id="gmail-6be9470d-32e3-476d-922c-6a67c3498121" class="gmail-GINGER_SOFTWARE_mark">setup</span>.<br>
>>>> > Both zones have two Pgpool-II nodes each. (Total 4 Pgpool-II nodes in<br>
>>>> <span id="gmail-d622f8fc-0138-482c-b41b-71512a4c55cd" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> > <span id="e56c0bd0-1b55-4e43-a449-09245b0f0a1f" class="gmail-GINGER_SOFTWARE_mark">cluster</span>).<br>
>>>> > Each availability zone has one application server that connects to one<br>
>>>> <span id="gmail-3d0262d8-5d2e-4068-a4ba-a4438115463f" class="gmail-GINGER_SOFTWARE_mark">of</span><br>
>>>> > <span id="gmail-22c64778-2cd2-41ce-bbc2-2cfb6f6517fc" class="gmail-GINGER_SOFTWARE_mark">two</span> Pgpool-II in the that availability zone. (That makes it<br>
>>>> <span id="gmail-d555e90c-786b-41f5-b3fe-446463373650" class="gmail-GINGER_SOFTWARE_mark">master</span>-master<br>
>>>> > <span id="gmail-525efab7-f7a0-4b71-95a8-83c8ffc78d4f" class="gmail-GINGER_SOFTWARE_mark">Pgpool</span> setup). And the user is concerned about split-brain of<br>
>>>> PostgreSQL<br>
>>>> > <span id="ead58249-dd1c-4ea6-8ad0-c294eca038b3" class="gmail-GINGER_SOFTWARE_mark">servers</span> when the corporate link between zones becomes unavailable.<br>
>>>> ><br>
>>>> > Thanks<br>
>>>> > Best regards<br>
>>>> > Muhammad Usama<br>
>>>> ><br>
>>>> ><br>
>>>> ><br>
>>>> >> Best regards,<br>
>>>> >> --<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>
>>>> >><br>
>>>> >> > Thanks<br>
>>>> >> > Best regards<br>
>>>> >> > Muhammad Usama<br>
>>>> >> ><br>
>>>> >> ><br>
>>>> >> ><br>
>>>> >> >><br>
>>>> >> >> Best regards,<br>
>>>> >> >> --<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>
>>>> >> >><br>
>>>> >> >> >> > Hi Hackers,<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > This is the proposal to make the failover of <span id="gmail-a95d15c8-9415-45be-8c63-c3bf73efd004" class="gmail-GINGER_SOFTWARE_mark">backend</span><br>
>>>> PostgreSQL<br>
>>>> >> <span id="gmail-5600f020-48dd-4741-901d-d6685c9991cb" class="gmail-GINGER_SOFTWARE_mark">nodes</span><br>
>>>> >> >> >> > <span id="gmail-23adea5d-42e1-4621-85ee-7ffaf518da0e" class="gmail-GINGER_SOFTWARE_mark">quorum</span> aware to make it more robust and fault tolerant.<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > Currently Pgpool-II proceeds to <span id="gmail-5f08774a-fe2f-4780-ac8e-3b0d439198d4" class="gmail-GINGER_SOFTWARE_mark">failover</span> the <span id="gmail-dfd29e58-f4dc-4a53-b400-93d82822e947" class="gmail-GINGER_SOFTWARE_mark">backend</span> node as<br>
>>>> <span id="gmail-b3627470-335e-493e-81fb-428e4f0884e5" class="gmail-GINGER_SOFTWARE_mark">soon</span><br>
>>>> >> <span id="gmail-3ed20bd6-f50b-4a2d-86b3-9f4761f9b377" class="gmail-GINGER_SOFTWARE_mark">as</span><br>
>>>> >> >> <span id="gmail-26ae27a4-b50d-4c8c-bcfa-0f812f1bacce" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> >> > <span id="gmail-a4b6f5c0-6c46-4d52-ada0-1a3c0ce89c5b" class="gmail-GINGER_SOFTWARE_mark">health</span> check detects the failure or in case of an error<br>
>>>> <span id="gmail-c53c2398-7a59-4b20-bda7-4966b3eca6a3" class="gmail-GINGER_SOFTWARE_mark">occurred</span> on<br>
>>>> >> >> <span id="gmail-4034df3e-5947-4016-8e70-bad83591913c" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> >> > <span id="gmail-75ae8e7d-fc04-4158-bf0f-27d69dc6cab9" class="gmail-GINGER_SOFTWARE_mark">backend</span> connection (when fail_over_on_backend_error is set).<br>
>>>> This<br>
>>>> >> <span id="gmail-7c9500b8-c977-4f3c-bc5a-ac46a0856934" class="gmail-GINGER_SOFTWARE_mark">is</span><br>
>>>> >> >> <span id="gmail-1e4bbb06-8c79-43aa-b90b-bac6a51db40e" class="gmail-GINGER_SOFTWARE_mark">good</span><br>
>>>> >> >> >> > <span id="gmail-f1767031-58ca-498c-8e4a-dca644cbb943" class="gmail-GINGER_SOFTWARE_mark">enough</span> for the standalone Pgpool-II server.<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > But consider the scenario where we have more than one<br>
>>>> Pgpool-II<br>
>>>> >> (Say<br>
>>>> >> >> >> > <span id="gmail-4110c824-1342-48f6-a2f9-d74178594092" class="gmail-GINGER_SOFTWARE_mark">Pgpool</span>-A, Pgpool-B and Pgpool-C) in the cluster connected<br>
>>>> <span id="e8556ba8-3c4c-4875-bb6c-3d1cdfe8ca06" class="gmail-GINGER_SOFTWARE_mark">through</span><br>
>>>> >> >> >> <span id="gmail-5e50103e-79d4-4c2e-8132-3665eb55db57" class="gmail-GINGER_SOFTWARE_mark">watchdog</span><br>
>>>> >> >> >> > <span id="gmail-c82ce22a-ccf6-46e6-b245-5a8075288f33" class="gmail-GINGER_SOFTWARE_mark">and</span> each Pgpool-II node is configured with two PostgreSQL<br>
>>>> <span id="gmail-a856a20a-b935-47c6-adf7-2830172c44b2" class="gmail-GINGER_SOFTWARE_mark">backends</span><br>
>>>> >> >> (B1<br>
>>>> >> >> >> <span id="gmail-28a65052-c60a-4d02-83cf-a3b9d783510c" class="gmail-GINGER_SOFTWARE_mark">and</span><br>
>>>> >> >> >> > B2).<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > Now if due to some network glitch or an issue, Pgpool-A fails<br>
>>>> <span id="gmail-ba2d6b31-bfaa-403b-8d39-b7d9158fb04f" class="gmail-GINGER_SOFTWARE_mark">or</span><br>
>>>> >> <span id="gmail-06ab905b-cfd4-49ca-a47b-7f8b00c1d608" class="gmail-GINGER_SOFTWARE_mark">loses</span><br>
>>>> >> >> >> <span id="gmail-ac6d30e8-3bf6-40bf-b0aa-d7431e74d8e3" class="gmail-GINGER_SOFTWARE_mark">its</span><br>
>>>> >> >> >> > <span id="gmail-c7cc9272-c2fe-4188-bf33-ad27c515fce7" class="gmail-GINGER_SOFTWARE_mark">network</span> connection with <span id="gmail-bac1a9e0-0721-45ab-aa35-640caed5dab2" class="gmail-GINGER_SOFTWARE_mark">backend</span> B1, The Pgpool-A will detect<br>
>>>> <span id="gmail-7a2673fc-b829-40bc-b3ca-0a55d19ba5bf" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> <span id="gmail-19fa5382-be5e-44dd-a037-cc896acf6fc4" class="gmail-GINGER_SOFTWARE_mark">failure</span><br>
>>>> >> >> >> > <span id="gmail-aacab466-f668-4313-9794-190f518d7094" class="gmail-GINGER_SOFTWARE_mark">and</span> detach (failover) the B1 <span id="gmail-036ca760-a631-4991-a0ff-0f167aa3e8fe" class="gmail-GINGER_SOFTWARE_mark">backend</span> and also pass this<br>
>>>> <span id="gmail-8fd12536-0004-437b-890c-4d2349e2fb2b" class="gmail-GINGER_SOFTWARE_mark">information</span><br>
>>>> >> >> <span id="gmail-4fef67b1-0355-4375-9b56-1dd7b5a2c9ae" class="gmail-GINGER_SOFTWARE_mark">to</span><br>
>>>> >> >> >> <span id="gmail-bcd78967-9210-4e88-bcaf-cb494a5abcf3" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> >> > <span id="gmail-3d712844-59c3-4213-a16f-a57a0b4f9824" class="gmail-GINGER_SOFTWARE_mark">other</span> Pgpool-II nodes (Pgpool-II B and Pgpool-II C), Although<br>
>>>> <span id="gmail-277b2b3a-3f54-4bbf-b80e-86f1290a99be" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> <span id="gmail-2cf819c6-e32b-4352-85ed-d8d663137483" class="gmail-GINGER_SOFTWARE_mark">Backend</span><br>
>>>> >> >> >> > B1 was perfectly healthy and it was also reachable from<br>
>>>> <span id="gmail-bbce3e3b-0dae-4b91-9b87-5b1f6a6a8b72" class="gmail-GINGER_SOFTWARE_mark">Pgpool</span>-B<br>
>>>> >> <span id="gmail-c85d5d0b-44d0-4bf8-b619-927f935dca2b" class="gmail-GINGER_SOFTWARE_mark">and</span><br>
>>>> >> >> >> > <span id="gmail-161bfb6b-e65c-4826-bb8b-3dc77194dae9" class="gmail-GINGER_SOFTWARE_mark">Pgpool</span>-C nodes, But still because of a network glitch between<br>
>>>> >> >> <span id="gmail-f3b95b0f-6f6f-4927-b27a-f7a6eda34dbe" class="gmail-GINGER_SOFTWARE_mark">Pgpool</span>-A<br>
>>>> >> >> >> <span id="gmail-0f18034c-dfc8-46c6-8918-96984fa6083c" class="gmail-GINGER_SOFTWARE_mark">and</span><br>
>>>> >> >> >> > <span id="gmail-b082a280-68a5-441e-a86a-e4b76214774b" class="gmail-GINGER_SOFTWARE_mark">Backend</span> B1, it will get detached from the cluster and the<br>
>>>> <span id="gmail-c9ccd297-32dc-4251-b6c0-472922aa5f0e" class="gmail-GINGER_SOFTWARE_mark">worst</span><br>
>>>> >> <span id="gmail-1d43c9f3-4969-4c26-977a-235d2b2c0f9a" class="gmail-GINGER_SOFTWARE_mark">part</span><br>
>>>> >> >> <span id="gmail-a02e7c18-5dfb-4197-a34a-e8d39110b58a" class="gmail-GINGER_SOFTWARE_mark">is</span>,<br>
>>>> >> >> >> <span id="gmail-b459ca75-96cb-4ce1-9ba2-0f06f62db0e3" class="gmail-GINGER_SOFTWARE_mark">if</span><br>
>>>> >> >> >> > <span id="gmail-9af4d438-6164-42c6-a18e-c2a5fee9fc2f" class="gmail-GINGER_SOFTWARE_mark">the</span> B1 was a master PostgreSQL (in master-standby<br>
>>>> <span id="gmail-b198f4aa-7d38-4d06-b343-f32ffeb2d77e" class="gmail-GINGER_SOFTWARE_mark">configuration</span>),<br>
>>>> >> <span id="gmail-7a71f4b9-6df1-4cd1-a4be-1db36ec175fc" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> >> > Pgpool-II <span id="gmail-61cdeae2-7871-4fdb-8471-f128d10c92c7" class="gmail-GINGER_SOFTWARE_mark">failover</span> would also promote the B2 PostgreSQL node<br>
>>>> <span id="gmail-17db1226-b287-4ef3-886f-fc6ea1adceb4" class="gmail-GINGER_SOFTWARE_mark">as</span> a<br>
>>>> >> <span id="gmail-dff6e2fd-7157-4334-9f52-c6e2d44ad782" class="gmail-GINGER_SOFTWARE_mark">new</span><br>
>>>> >> >> >> > <span id="gmail-46cbb1eb-0b3a-431a-8349-3c6a6409703e" class="gmail-GINGER_SOFTWARE_mark">master</span>, <span id="gmail-1ee054dc-857d-4759-beed-178fbabf604e" class="gmail-GINGER_SOFTWARE_mark">hense</span> making the way for split-brain and/or data<br>
>>>> >> <span id="gmail-5669508d-4192-461c-bf09-7b819f658390" class="gmail-GINGER_SOFTWARE_mark">corruptions</span>.<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > So my proposal is that when the Watchdog is configured in<br>
>>>> Pgpool-II<br>
>>>> >> >> <span id="gmail-fc632796-870b-40ae-9f2c-57ac6e3a3ca6" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> >> > <span id="e486469d-b9b3-42cf-8126-38dcec338613" class="gmail-GINGER_SOFTWARE_mark">backend</span> health check of Pgpool-II should consult with <span id="gmail-8e68f5ad-4f78-41ee-be2d-44ae3d70984d" class="gmail-GINGER_SOFTWARE_mark">other</span><br>
>>>> >> <span id="gmail-70d408ac-fce1-421f-96d4-b89b3873e554" class="gmail-GINGER_SOFTWARE_mark">attached</span><br>
>>>> >> >> >> > Pgpool-II nodes over the watchdog to decide if the Backend<br>
>>>> <span id="gmail-8b4966d3-7a87-4b10-a0e5-8d2af9b54f75" class="gmail-GINGER_SOFTWARE_mark">node</span> is<br>
>>>> >> >> >> <span id="gmail-25e411b9-d71c-4536-bb3a-4c7d5cc71d8c" class="gmail-GINGER_SOFTWARE_mark">actually</span><br>
>>>> >> >> >> > <span id="gmail-0fb8ab08-3e90-4c0d-99c9-20c80b173db3" class="gmail-GINGER_SOFTWARE_mark">failed</span> or if it is just a localized glitch/false alarm. And<br>
>>>> <span id="gmail-772aacd6-6dfc-48e5-812e-5529d84d5213" class="gmail-GINGER_SOFTWARE_mark">the</span><br>
>>>> >> >> <span id="gmail-5ad9a7c9-7f2e-4593-86ff-779691f3fdc4" class="gmail-GINGER_SOFTWARE_mark">failover</span><br>
>>>> >> >> >> <span id="gmail-3358e091-e463-4e4b-9922-44a677f4242e" class="gmail-GINGER_SOFTWARE_mark">on</span><br>
>>>> >> >> >> > <span id="gmail-c8c2d8b5-bbde-43ae-818b-65b366fe50a8" class="gmail-GINGER_SOFTWARE_mark">the</span> node should only be performed, when the majority of<br>
>>>> <span id="gmail-2f6dbbd0-afc9-4c77-a69e-5a2520f13c5f" class="gmail-GINGER_SOFTWARE_mark">cluster</span><br>
>>>> >> >> <span id="gmail-bfcf8aa3-4206-4fd9-bb90-7d3ee0bac0cb" class="gmail-GINGER_SOFTWARE_mark">members</span><br>
>>>> >> >> >> > <span id="gmail-f2e08266-107e-4b9a-8a29-aae02d440ea5" class="gmail-GINGER_SOFTWARE_mark">agrees</span> on the failure of nodes.<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > This quorum aware architecture of failover will prevents the<br>
>>>> <span id="gmail-38e68e77-35b1-4081-9c16-200849f27220" class="gmail-GINGER_SOFTWARE_mark">false</span><br>
>>>> >> >> >> > <span id="gmail-5438e1b4-1a7d-42e4-8206-b5c00775acdf" class="gmail-GINGER_SOFTWARE_mark">failovers</span> and split-brain scenarios in the Backend nodes.<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > What are your thoughts and suggestions on this?<br>
>>>> >> >> >> ><br>
>>>> >> >> >> > Thanks<br>
>>>> >> >> >> > Best regards<br>
>>>> >> >> >> > Muhammad Usama<br>
>>>> >> >> >><br>
>>>> >> >><br>
>>>> >><br>
>>>><br>
>>><br>
>>><br>
>><br>
</div></div></blockquote></div><br></div></div>