Pgpool health check failed causing failover

1

Hello Team,

I am new to PG and PGPOOL. we have a production environment in which recently while doing DR drill activity , pgpool failover has been triggered. What I see from the logs is during health check for node 0 from node 1 it failed and subsequently raised failover request. Now which password it uses? pgpool.conf and pool_passwd has same password for pgpool user.
Below is the log snippet from server2

Aug 28 01:36:51 kpgpgsdbdr02 pgpool[106683]: [19-1] 2024-08-28 01:36:51: pid 106683: ERROR: failed to authenticate
Aug 28 01:36:51 kpgpgsdbdr02 pgpool[106683]: [19-2] 2024-08-28 01:36:51: pid 106683: DETAIL: invalid authentication message response type, Expecting ‘R’ and received ‘E’
Aug 28 01:36:51 kpgpgsdbdr02 pgpool[106683]: [20-1] 2024-08-28 01:36:51: pid 106683: LOG: health check failed on node 0 (timeout:0)
Aug 28 01:36:51 kpgpgsdbdr02 pgpool[106683]: [21-1] 2024-08-28 01:36:51: pid 106683: LOG: received degenerate backend request for node_id: 0 from pid [106683]
Aug 28 01:36:51 kpgpgsdbdr02 pgpool[104529]: [26-1] 2024-08-28 01:36:51: pid 104529: LOG: failover request from local pgpool-II node received on IPC interface is forwarded to master watchdog node “kpgpgpdbdr02:5432 Linux kpgpgpdbdr02”
Aug 28 01:36:51 kpgpgsdbdr02 pgpool[104529]: [26-2] 2024-08-28 01:36:51: pid 104529: DETAIL: waiting for the reply…
Aug 28 01:36:51 kpgpgsdbdr02 pgpool[106683]: [22-1] 2024-08-28 01:36:51: pid 106683: LOG: degenerate backend request for 1 node(s) from pid [106683], will be handled by watchdog

could you please help me with which password it is using and where is it?

2

PG version 11.22 , pgpool: 4.1.15

3

Hi Rajkamal,
Welcome to the Percona community forum!

To perform a health check, pgpool connects to PostgreSQL servers using the credentials specified in the health_check_user, health_check_password, and health_check_database parameters. Make sure to set the appropriate values for these configuration settings in pgpool.

For more details on health check configuration, you can refer to the documentation here: Health Check Configuration.

4

we have them set in pgpool.conf file and the password is matching with the production environment. like below

health_check_period = 5
# Health check period
# Disabled (0) by default
health_check_timeout = 30
# Health check timeout
# 0 means no timeout
health_check_user = ‘pgpool’
# Health check user
health_check_password = ‘md5cf745787eb2696049aaf8cfe27649e8d’
# Password for health check user
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password

health_check_database = ‘’
# Database name for health check. If ‘’, tries ‘postgres’ frist,
health_check_max_retries = 3
# Maximum number of times to retry a failed health check before giving up.
health_check_retry_delay = 1
# Amount of time to wait (in seconds) between retries.
connect_timeout = 10000

but failing with authentication error. is there anything I am missing?

5

The health check configurations look fine. Can you verify if the pg_hba allows health_check_user (pgpool) connections to use md5 authentication from the host where Pgpool-II is hosted?