<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0">
Hi,
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0">> Hi,</div>
<div class="ContentPasted0">> Thank you for your response and script but what about situation when standby goes down? Your script covers situation when primary fall and failover is performing. Is there any way to detect standby failure and perform needed modifications
on primary to let him not wait forever?</div>
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0">Even if the standby goes down, the failover script (specified in "failover_command") will be executed.</div>
<div class="ContentPasted0">You can add the settings into the failover script.</div>
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0">Here is the sample script:</div>
<div class="ContentPasted0">https://github.com/pgpool/pgpool2/blob/master/src/sample/scripts/failover.sh.sample#L58</div>
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0">-----------</div>
<div class="ContentPasted0">## If Standby node is down, skip failover.</div>
<div class="ContentPasted0">if [ $OLD_PRIMARY_NODE_ID != "-1" -a $FAILED_NODE_ID != $OLD_PRIMARY_NODE_ID ]; then</div>
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0"> ...</div>
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0"> # changing synchronous_standby_names</div>
<div class="ContentPasted0"> ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \</div>
<div class="ContentPasted0"> -c "ALTER SYSTEM SET synchronous_standby_names TO ''"</div>
<div class="ContentPasted0"> ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \</div>
<div class="ContentPasted0"> -c "SELECT pg_reload_conf()"</div>
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0"> ...</div>
<div><br class="ContentPasted0">
</div>
<div class="ContentPasted0"> echo failover.sh: end: standby node is down. Skipping failover.</div>
<div class="ContentPasted0"> exit 0</div>
<div class="ContentPasted0">fi</div>
<div class="ContentPasted0">-----------</div>
<br>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>差出人:</b> Andrzej Rozmus <Andrzej.Rozmus@asseco.pl><br>
<b>送信日時:</b> 2023年7月6日 16:39<br>
<b>宛先:</b> Bo Peng <pengbo@sraoss.co.jp><br>
<b>件名:</b> RE: [pgpool-general: 8843] Re: performing reload of primary config after standby node became unavailable</font>
<div> </div>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt;">
<div class="PlainText">Hi,<br>
Thank you for your response and script but what about situation when standby goes down? Your script covers situation when primary fall and failover is performing. Is there any way to detect standby failure and perform needed modifications on primary to let
him not wait forever?<br>
Best regards<br>
<br>
Andrzej Rozmus<br>
Starszy Technolog<br>
Departament Systemów Administracji Rządowej<br>
Dział Ministerstwo Finansów<br>
Asseco Poland S.A.<br>
ul. Branickiego 13<br>
02-972 Warszawa<br>
tel. kom. +48 502 270 059<br>
andrzej.rozmus@asseco.pl<br>
<br>
<br>
<br>
-----Original Message-----<br>
From: pgpool-general <pgpool-general-bounces@pgpool.net> On Behalf Of Bo Peng<br>
Sent: Monday, June 19, 2023 4:41 AM<br>
To: Andrzej Rozmus <Andrzej.Rozmus@asseco.pl><br>
Cc: Sebastian Wierzbicki <sebastian.wierzbicki@asseco.pl>; pgpool-general@pgpool.net; kazimierz.gorka (kazimierz.gorka@mf.gov.pl) <kazimierz.gorka@mf.gov.pl><br>
Subject: [pgpool-general: 8843] Re: performing reload of primary config after standby node became unavailable<br>
<br>
Hi,<br>
<br>
> Hello Tatsuo,<br>
> Probably title of my post may seem to be strange but I wonder if there is any way to force pgpool to perform any action (for example run a custom script) after detection of failure on standby node? Pgpool works very fine when detecting failures on primary,
but sometimes it would be very helpful if it was able also react for standby node unavailability. I set up synchronous replication with two node postgres cluster and as you obviously expect everything goes fine until standby node goes down with any reason
- all transaction hang and wait for commit from standby. If pgpool could force primary node in any way to reload its configuration into asynchronous mode postgresql cluster could be still available for dml operations. Do You know if this is possible with current
mechanism implemented in pgpool to achieve such effect?<br>
> Thank you in advance for your help.<br>
<br>
Normally if you are using two nodes with synchronous replication, the configuration of synchronous replication (synchronous_standby_names) need to be modified in the failover script which is specified in failover_command parameter after standby goes down.<br>
<br>
You need to modify the failover script to include the logic used to change server configuration parameters.<br>
<br>
Below is an example.<br>
(I assume you are uing the sample scipts.)<br>
<br>
----------------------------<br>
if [ $OLD_PRIMARY_NODE_ID != "-1" -a $FAILED_NODE_ID != $OLD_PRIMARY_NODE_ID ]; then<br>
<br>
...<br>
<br>
# changing synchronous_standby_names<br>
${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \<br>
-c "ALTER SYSTEM SET synchronous_standby_names TO ''"<br>
${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \<br>
-c "SELECT pg_reload_conf()"<br>
...<br>
fi<br>
----------------------------<br>
--<br>
Bo Peng <pengbo@sraoss.co.jp><br>
SRA OSS LLC<br>
TEL: 03-5979-2701 FAX: 03-5979-2702<br>
URL: <a href="https://www.sraoss.co.jp/">https://www.sraoss.co.jp/</a><br>
_______________________________________________<br>
pgpool-general mailing list<br>
pgpool-general@pgpool.net<br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</div>
</span></font></div>
</body>
</html>