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

[Bo Peng]

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/