[pgpool-general: 8876] Re: performing reload of primary config after standby node became unavailable

[Bo Peng]

Hi,

> Hi,
> 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?

Even if the standby goes down, the failover script (specified in "failover_command") will be executed.
You can add the settings into the failover script.

Here is the sample script:
https://github.com/pgpool/pgpool2/blob/master/src/sample/scripts/failover.sh.sample#L58

-----------
## If Standby node is down, skip failover.
if [ $OLD_PRIMARY_NODE_ID != "-1" -a $FAILED_NODE_ID != $OLD_PRIMARY_NODE_ID ]; then

    ...

    # changing synchronous_standby_names
    ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \
       -c "ALTER SYSTEM SET synchronous_standby_names TO ''"
    ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \
       -c "SELECT pg_reload_conf()"

    ...

    echo failover.sh: end: standby node is down. Skipping failover.
    exit 0
fi
-----------

________________________________
差出人: Andrzej Rozmus <Andrzej.Rozmus at asseco.pl>
送信日時: 2023年7月6日 16:39
宛先: Bo Peng <pengbo at sraoss.co.jp>
件名: RE: [pgpool-general: 8843] Re: performing reload of primary config after standby node became unavailable

Hi,
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?
Best regards

Andrzej Rozmus
Starszy Technolog
Departament Systemów Administracji Rządowej
Dział Ministerstwo Finansów
Asseco Poland S.A.
ul. Branickiego 13
02-972 Warszawa
tel. kom. +48 502 270 059
andrzej.rozmus at asseco.pl



-----Original Message-----
From: pgpool-general <pgpool-general-bounces at pgpool.net> On Behalf Of Bo Peng
Sent: Monday, June 19, 2023 4:41 AM
To: Andrzej Rozmus <Andrzej.Rozmus at asseco.pl>
Cc: Sebastian Wierzbicki <sebastian.wierzbicki at asseco.pl>; pgpool-general at pgpool.net; kazimierz.gorka (kazimierz.gorka at mf.gov.pl) <kazimierz.gorka at mf.gov.pl>
Subject: [pgpool-general: 8843] Re: performing reload of primary config after standby node became unavailable

Hi,

> Hello Tatsuo,
> 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?
> Thank you in advance for your help.

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.

You need to modify the failover script to include the logic used to change server configuration parameters.

Below is an example.
(I assume you are uing the sample scipts.)

----------------------------
 if [ $OLD_PRIMARY_NODE_ID != "-1" -a $FAILED_NODE_ID != $OLD_PRIMARY_NODE_ID ]; then

    ...

    # changing synchronous_standby_names
    ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \
      -c "ALTER SYSTEM SET synchronous_standby_names TO ''"
    ${PGHOME}/bin/psql -h ${OLD_PRIMARY_NODE_HOST} -p ${OLD_PRIMARY_NODE_PORT} postgres \
      -c "SELECT pg_reload_conf()"
    ...
 fi
----------------------------
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230707/e87f23ba/attachment.htm>