<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
h2
{mso-style-priority:9;
mso-style-link:"Überschrift 2 Zchn";
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:18.0pt;
font-family:"Calibri",sans-serif;
font-weight:bold;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Nur Text Zchn";
margin:0cm;
font-size:10.0pt;
font-family:"Arial",sans-serif;
mso-fareast-language:EN-US;}
span.NurTextZchn
{mso-style-name:"Nur Text Zchn";
mso-style-priority:99;
mso-style-link:"Nur Text";
font-family:"Arial",sans-serif;}
span.berschrift2Zchn
{mso-style-name:"Überschrift 2 Zchn";
mso-style-priority:9;
mso-style-link:"Überschrift 2";
font-family:"Calibri",sans-serif;
mso-fareast-language:DE;
font-weight:bold;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoPlainText">Hi Tatsuo, <o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><span lang="EN-GB">thank you so much for the detailed explanation. That makes all sense to me – one detail in the combination of acting software is not matching from my point of view: Why is pgpool trying to prevent a split brain scenario?
I’ll explain my thoughts and maybe you can point out at which point pgpool is misconfigured or the misunderstanding.
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB">As I mentioned, I'm using containers packed by Bitnami, which are using
<a href="https://hub.docker.com/r/bitnami/pgpool">pgpool as a proxy for the clients to find always primary instance</a> and
<a href="https://hub.docker.com/r/bitnami/postgresql-repmgr">repmgr and pg to provide a HA solution for Postgres</a>.
<o:p></o:p></span></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:35.4pt">
<b><span lang="EN-GB" style="font-size:18.0pt;mso-fareast-language:DE">What is Pgpool-II?<o:p></o:p></span></b></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:35.4pt">
<span lang="EN-GB" style="mso-fareast-language:DE">Pgpool-II is the PostgreSQL proxy. It stands between PostgreSQL servers and their clients providing connection pooling, load balancing, automated failover, and replication.<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<h2 style="margin-left:35.4pt"><span lang="EN-GB">What is PostgreSQL HA?<o:p></o:p></span></h2>
<p style="margin-left:35.4pt"><span lang="EN-GB">This PostgreSQL cluster solution includes the PostgreSQL replication manager, an open-source tool for managing replication and failover on PostgreSQL clusters.<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB">As far as I understand, Bitnami designed it like this, that Postgres in combination with
<a href="https://www.repmgr.org/">repmgr</a> should do all the High Availability work. Pgpool should “just” forward connections from the clients to the primary instance of postgres.
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB">In the error case I’m describing here, just pgpool has a problem with the situation. The cluster itself is working. The connection to every pg and pgpool is working. It is just that one pgpool remains unhealthy because
it can’t determine a primary because it has marked the server, which is indeed primary, as down.
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB">From my point of view (and I’m just the user in the middle of the software giants postgres, repmgr, pgpool and Bitnami), I think there is a gap in configuration of pgpool to just act as proxy. Nothing to do during
failover nor nothing extra. Therefore I decided to start with my investigation here at the pgpool mailing list. As far as I understand it, if pgpool would ignore/disable the pgpool_status file, my scenario would be fine. Maybe there is another configuration
which does exactly what Bitnami expected from pgpool to do. I think you are the very right person who could know this. Therefore the question: What do I need to configure that pgpool is just acting as a proxy (which was the intention of Bitnami)? OR How can
I disable the pgpool_status file (I already was thinking to delete it in the entry point of the container)?
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB">Thank you so much, I appreciate your expertise,
<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB">best regards, Daniel<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span style="mso-fareast-language:DE"><div dir="ltr" style="mso-line-height-rule:exactly;-webkit-text-size-adjust:100%;font-size:1px;direction:ltr;"><table cellpadding="0" cellspacing="0" border="0" style="width:100%;font-size:1px;"><tr style="font-size:0;"><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;"><tr style="font-size:0;"><td align="left" style="padding:0;vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;color:#000001;font-style:normal;font-weight:700;white-space:nowrap;"><tr style="font-size:13.33px;"><td align="left" style="vertical-align:top;font-family:Arial;">AZO GmbH & Co. KG<span style="font-family:remialcxesans;font-size:1px;color:#FFFFFF;line-height:1px;"></span></td></tr><tr style="font-size:0;"><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="width:100%;font-size:0;color:#000001;font-style:normal;font-weight:400;white-space:nowrap;"><tr style="font-size:13.33px;"><td align="left" style="vertical-align:top;font-family:Arial;">Rosenberger Str. 28</td></tr><tr style="font-size:0;"><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;color:#000001;font-style:normal;font-weight:400;white-space:nowrap;"><tr style="font-size:13.33px;"><td align="left" style="vertical-align:top;font-family:Arial;">D‑</td><td align="left" style="vertical-align:top;font-family:Arial;">74706</td><td align="left" style="vertical-align:top;font-family:Arial;"> <br /></td><td align="left" style="vertical-align:top;font-family:Arial;">Osterburken</td></tr></table></td></tr></table></td></tr></table></td></tr><tr style="font-size:0;"><td align="left" style="padding:13px 0 0;vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;color:#000001;font-style:normal;font-weight:700;white-space:nowrap;"><tr style="font-size:13.33px;"><td align="left" style="padding:0;vertical-align:top;font-family:Arial;">Tel.: </td><td align="left" style="padding:0;vertical-align:top;font-family:Arial;font-weight:400;"><a href="tel:+49%206291%2092-6449" target="_blank" id="LPlnk689713" style="text-decoration:none;color:#000001;">+49 6291 92-6449</a></td></tr><tr style="font-size:13.33px;"><td align="left" style="padding:0;vertical-align:top;font-family:Arial;">Mob.: </td><td align="left" style="padding:0;vertical-align:top;font-family:Arial;font-weight:400;"><a href="tel:+49%20162%209919448" target="_blank" id="LPlnk689713" style="text-decoration:none;color:#000001;">+49 162 9919448</a></td></tr><tr style="font-size:13.33px;"><td align="left" style="padding:0;vertical-align:top;font-family:Arial;">Fax: </td><td align="left" style="padding:0;vertical-align:top;font-family:Arial;font-weight:400;"><a href="fax:+49%206291%209290449" target="_blank" id="LPlnk689713" style="text-decoration:none;color:#000001;">+49 6291 9290449</a></td></tr><tr style="font-size:13.33px;"><td align="left" style="padding:0;vertical-align:top;font-family:Arial;">Mail: </td><td align="left" style="padding:0;vertical-align:top;font-family:Arial;font-weight:400;"><a href="mailto:Daniel.Camarena@azo.com" target="_blank" id="LPlnk689713" style="text-decoration:none;color:#000001;"><span style="text-decoration:underline;">Daniel.Camarena@azo.com</span></a></td></tr><tr style="font-size:13.33px;"><td align="left" style="padding:0;vertical-align:top;font-family:Arial;">Web: </td><td align="left" style="padding:0;vertical-align:top;font-family:Arial;font-weight:400;"><span style="text-decoration:underline;">www.azo.com</span></td></tr></table></td></tr><tr style="font-size:18px;color:#E46713;font-style:normal;font-weight:700;white-space:nowrap;"><td align="left" style="padding:13px 0 0;vertical-align:top;font-family:Arial;">AZO. <span style="color:#000001;">We Love Ingredients.</span><br /></td></tr><tr style="font-size:0;"><td align="left" style="padding:13px 0 0;vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;"><tr style="font-size:0;"><td align="left" style="background-color:#E46713;padding:5px;vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;"><tr style="font-size:0;"><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;line-height:normal;"><tr style="font-size:0;"><td align="left" style="padding:0 5px 0 0;vertical-align:top;"><a href="https://www.facebook.com/azogroup" target="_blank" id="LPlnk689713" style="text-decoration:none;"><img src="cid:image948446.png@89BC667F.90610B79" height="25" border="0" alt="" style="height:25px;min-height:25px;max-height:25px;font-size:0;" /></a></td></tr></table></td><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;line-height:normal;"><tr style="font-size:0;"><td align="left" style="padding:0 5px 0 0;vertical-align:top;"><a href="https://www.linkedin.com/company/azo-gmbh-co-kg/mycompany/verification/" target="_blank" id="LPlnk689713" style="text-decoration:none;"><img src="cid:image532165.png@97372968.CD71C369" height="25" border="0" alt="" style="height:25px;min-height:25px;max-height:25px;font-size:0;" /></a></td></tr></table></td><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;line-height:normal;"><tr style="font-size:0;"><td align="left" style="padding:0 5px 0 0;vertical-align:top;"><a href="https://twitter.com/AZO_GmbH_CoKG" target="_blank" id="LPlnk689713" style="text-decoration:none;"><img src="cid:image686896.png@4FE9B4CD.7896F724" height="25" border="0" alt="" style="height:25px;min-height:25px;max-height:25px;font-size:0;" /></a></td></tr></table></td><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;line-height:normal;"><tr style="font-size:0;"><td align="left" style="padding:0 5px 0 0;vertical-align:top;"><a href="https://www.xing.com/pages/azogmbh-co-kg" target="_blank" id="LPlnk689713" style="text-decoration:none;"><img src="cid:image828015.png@6E2AB672.02ADFCB8" height="25" border="0" alt="" style="height:25px;min-height:25px;max-height:25px;font-size:0;" /></a></td></tr></table></td><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;line-height:normal;"><tr style="font-size:0;"><td align="left" style="padding:0 5px 0 0;vertical-align:top;"><a href="https://www.youtube.com/c/AZOGroup" target="_blank" id="LPlnk689713" style="text-decoration:none;"><img src="cid:image916231.png@EB3E9D59.8DCF0B93" height="25" border="0" alt="" style="height:25px;min-height:25px;max-height:25px;font-size:0;" /></a></td></tr></table></td><td align="left" style="vertical-align:top;line-height:normal;"><a href="https://www.instagram.com/azo_karriere/" target="_blank" id="LPlnk689713" style="text-decoration:none;"><img src="cid:image928808.png@7211D301.29F72EBF" height="25" border="0" alt="" style="height:25px;min-height:25px;max-height:25px;font-size:0;" /></a></td></tr></table></td></tr></table></td></tr><tr style="font-size:0;"><td align="left" style="padding:13px 0 5px;vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;"><tr style="font-size:0;"><td align="left" style="vertical-align:top;"><table cellpadding="0" cellspacing="0" border="0" style="font-size:0;line-height:normal;"><tr style="font-size:0;"><td align="left" style="padding:2px;vertical-align:top;"><a href="https://www.azo.com/en-de/fakuma2023?mtm_campaign=Fakuma_2023&mtm_kwd=Email-Signatur_ENGLISCH" target="_blank" id="LPlnk689713" style="text-decoration:none;"><img src="cid:image223832.png@D497CD4D.39F1D74E" width="468" height="60" border="0" alt="" style="width:468px;min-width:468px;max-width:468px;height:60px;min-height:60px;max-height:60px;font-size:0;" /></a></td></tr></table></td></tr></table></td></tr><tr style="font-size:10.67px;color:#000001;font-style:normal;font-weight:400;white-space:normal;"><td align="left" style="padding:5px 0 0;border-top:solid 1px #C0C0C0;border-right:none;border-bottom:none;border-left:none;vertical-align:top;font-family:Arial;">KG: Sitz Osterburken, Register-Gericht Mannheim HRA 450086, Persönlich haftende Gesellschafterin: AZO Beteiligungs GmbH, Sitz Osterburken, Register-Gericht Mannheim HRB 450261<br />Geschäftsführer: Rainer Zimmermann | Daniel Auerhammer | Dr. Matthias Fechner | Jan-Wilko Helms | Dennis Künkel<br /><br />Diese E-Mail einschließlich ihrer Anhänge ist vertraulich. Wir bitten Sie, eine fehlgeleitete E-Mail zu löschen und uns eine Nachricht zukommen zu lassen. Wir haben die E-Mail vor dem Versenden auf Virenfreiheit geprüft. Eine Haftung für Virenfreiheit schließen wir jedoch aus.<br /><br />This e-mail and its attachments are confidential. If you are not the intended recipient of this e-mail message, please delete it and inform us accordingly. This e-mail was checked for viruses when sent, however we are not liable for any virus contamination.<br /></td></tr><tr style="font-size:0;line-height:normal;"><td align="left" style="padding:13px 0 0;vertical-align:top;"><img src="cid:image283648.png@B4F70C2A.1F53DE00" height="50" border="0" alt="" style="height:50px;min-height:50px;max-height:50px;font-size:0;" /></td></tr></table></td></tr></table></div>-----Ursprüngliche Nachricht-----<br>
Von: Tatsuo Ishii <ishii@sraoss.co.jp> <br>
Gesendet: Dienstag, 17. Oktober 2023 02:49<br>
An: Camarena Daniel <Daniel.Camarena@azo.com><br>
Cc: pgpool-general@pgpool.net<br>
Betreff: Re: AW: [pgpool-general: 8942] pgpool 4.4.4: reading status file: 1 th backend is set to down status</span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">[Sie erhalten nicht h?ufig E-Mails von <a href="mailto:ishii@sraoss.co.jp">
<span style="color:windowtext;text-decoration:none">ishii@sraoss.co.jp</span></a>. Weitere Informationen, warum dies wichtig ist, finden Sie unter
<a href="https://aka.ms/LearnAboutSenderIdentification"><span style="color:windowtext;text-decoration:none">https://aka.ms/LearnAboutSenderIdentification</span></a> ]<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">> Hi Tatsuo,<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> thanks for your reply and the explication.<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> To comment your answers:<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>> > 1. Is there a file which buffers pg states?<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>> If you mean "pg_status" column in show pool_nodes command, no. It is obtained from PostgreSQL on the fly when show pool_nodes command gets executed.<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> Yes. But it seems that out of pg_status is formed a resulting state which is in the column status of show pool_nodes (see results below) and this indicates that the service is down - and pgpool is acting like this. See below the log
of pgpool: It indicates, that is marking 0 th node as down because of the "status file".<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">I think you are talking about "status" column, rather than "pg_status"<o:p></o:p></p>
<p class="MsoPlainText">column in show pool_nodes command. The "status" column is set to down by pgpool. The "status" is set according to the content of pgpool_status file upon starting up of pgpool. If it's set to "down", it is not set to "up" until pcp_attach_node
or pcp_recovery_node is executeds. The reason is explained below.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">>> > 2. How did the system get into this state?<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>> I am not familiar with bitnami pgpool nor repmgr. So what I can do
<o:p></o:p></p>
<p class="MsoPlainText">>> is answer from the point of pgpool view. It was caused by either
<o:p></o:p></p>
<p class="MsoPlainText">>> failover triggered by health check (pgpool detects error / shutdown
<o:p></o:p></p>
<p class="MsoPlainText">>> of PostgreSQL), or pcp_detach_node gets executed. I cannot tell
<o:p></o:p></p>
<p class="MsoPlainText">>> either unless looking into pgpool log and pgpool.conf<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> Pg0 had tons of these messages:<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-11 11:19:03.522 GMT [956538] FATAL: remaining connection
<o:p></o:p></p>
<p class="MsoPlainText">> slots are reserved for non-replication superuser connections<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-11 11:19:03.525 GMT [956537] FATAL: remaining connection
<o:p></o:p></p>
<p class="MsoPlainText">> slots are reserved for non-replication superuser connections<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-11 11:19:03.542 GMT [956539] FATAL: remaining connection
<o:p></o:p></p>
<p class="MsoPlainText">> slots are reserved for non-replication superuser connections<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-11 11:19:03.545 GMT [956540] FATAL: remaining connection
<o:p></o:p></p>
<p class="MsoPlainText">> slots are reserved for non-replication superuser connections<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> Pg1 has right now, as I was examining the system the same messages. Sometimes they appear and it seems that because of the a failover occurs - like you described before.<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> Should I just increase max_connections, default 100, to 200 to prevent the problem?<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Yes, I think so. The above FATAL error could cause the health_check to trigger failover depending on the setting of pgpool.conf.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">> In the meanwhile I have found a file in the logs folder of pgpool. It has the following content:<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> root@c8bdc87693d4:/opt/bitnami/pgpool/logs# cat pgpool_status<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> down<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> up<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> up<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> As pgpool has a line during startup<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-16 05:28:21.670: main pid 1: LOG: reading status file:
<o:p></o:p></p>
<p class="MsoPlainText">> 0 th backend is set to down status<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> I thought this file is read and the status of pg0 is overridden by this.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Yes, your guess is correct. This is necessary to prevent "split brain"<o:p></o:p></p>
<p class="MsoPlainText">problem. Suppose you have PostgreSQL node 0 (primary) and node 1 (standby).<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">1) node 0 goes down by admin.<o:p></o:p></p>
<p class="MsoPlainText">2) node 1 becomes new primary node.<o:p></o:p></p>
<p class="MsoPlainText">3) whole system (pgpool, node 0 and node 1) restarts.<o:p></o:p></p>
<p class="MsoPlainText">4) node 0 starts as primary, node 1 starts as primary.<o:p></o:p></p>
<p class="MsoPlainText">5) now you have two primary nodes, this is the split brain.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">The pgpool_status file prevents this situation. In #3, pgpool sets the node 0 status to down by reading pgpool_status file. Therefore it prevents the situation #5.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">> show pool_nodes; returns the following:<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> <o:p></o:p></p>
<p class="MsoPlainText">> ---------+-----------+------+--------+-----------+-----------+--------<o:p></o:p></p>
<p class="MsoPlainText">> -+---------+------------+-------------------+-------------------+-----<o:p></o:p></p>
<p class="MsoPlainText">> --------------+------------------------+---------------------<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 0 | 10.0.10.7 | 5432 | down | up | 0.333333 | standby | primary | 0 | false | 0 | | | 2023-10-16 05:29:17<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 1 | 10.0.10.8 | 5432 | up | up | 0.333333 | standby | standby | 5 | false | 0 | | | 2023-10-16 05:29:17<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2 | 10.0.10.9 | 5432 | up | up | 0.333333 | standby | standby | 11 | true | 0 | | | 2023-10-16 05:29:17<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> (3 rows)<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> Indicates, that pg_role of pg0 is primary but the resulting role is standby, as resulting status is down, even if pg_status is up.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">That's an exepcted behavior. Since pgpool recognizes node 0 as "down"<o:p></o:p></p>
<p class="MsoPlainText">by reading pgpool_status file, it does not check node 0 to see whether node 0 is primary or not. Pgpool only checks node 1 and node 2, and concluded that there's no primary.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">I think you can use pcp_attach_node on node 0.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">> As orchestration always starts pgpool new, I post the startup sequence of the container:<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">[snip]<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-16 05:28:21.757: main pid 1: LOG: <o:p></o:p></p>
<p class="MsoPlainText">> find_primary_node_repeatedly: waiting for finding a primary node<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-16 05:28:21.793: main pid 1: LOG: find_primary_node: standby
<o:p></o:p></p>
<p class="MsoPlainText">> node is 1<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">> 2023-10-16 05:28:21.793: main pid 1: LOG: find_primary_node: standby
<o:p></o:p></p>
<p class="MsoPlainText">> node is 2<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Here, pgpool was looked for primary node only against node 1 and 2 by the reason above.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">> Last but not least pgpool.conf you requested. I left the comments in the file:<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Thanks. I noticed that "failover_command = 'echo...". This means when primary godes down, pgpool does not elect new primary. I am not sure if this is correct idea. Maybe the orchestration tool does something which is out of scope of
pgpool?<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Best reagards,<o:p></o:p></p>
<p class="MsoPlainText">--<o:p></o:p></p>
<p class="MsoPlainText">Tatsuo Ishii<o:p></o:p></p>
<p class="MsoPlainText">SRA OSS LLC<o:p></o:p></p>
<p class="MsoPlainText">English: <a href="http://www.sraoss.co.jp/index_en/"><span style="color:windowtext;text-decoration:none">https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sraoss.co.jp%2Findex_en%2F&data=05%7C01%7CDaniel.Camarena%40azo.com%7C3d68f823f983480244ac08dbceaad313%7Ce3767d377c424eb68b3f60c7e9578e74%7C0%7C0%7C638331005321726555%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=RHzSwJ%2BKHsfMKMrFbgq1vxsam1NfW2K0cJM55j7fZ64%3D&reserved=0</span></a><o:p></o:p></p>
<p class="MsoPlainText">Japanese:https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sraoss.co.jp%2F&data=05%7C01%7CDaniel.Camarena%40azo.com%7C3d68f823f983480244ac08dbceaad313%7Ce3767d377c424eb68b3f60c7e9578e74%7C0%7C0%7C638331005321726555%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2BDawLC1r81yoJe%2Fk7KbcPIWO5b%2B57qv1LP5gcne5lQc%3D&reserved=0<o:p></o:p></p>
</div>
</body>
</html>