[pgpool-general: 8924] Pgpool-II - failed to acquire the delegate IP address - issue with VIP delegation

[Marian Pompura]

Hello guys,

pgpool-II version 4.3.5 (tamahomeboshi)
PostgreSQL version 15.4

I have 3 node PgPool-2 configured on Ubuntu 20.04.2 LTS.
Server1 is PRIMARY
Server2,3 STANDBY nodes. I also use watchdog for IP delegation during
failover.
I followed up configuration from
https://www.pgpool.net/docs/43/en/html/example-cluster.html
* some steps required small adjustment for ubuntu (especially
failover,escalation,follow_primary and other scripts that are used for
online recovery and failover)

Regarding failover operations. Everything run smoothly without issue.
What is going wrong is VIP address delegation.
When I test failover scenario (e.g. stop primary instance) failover occured
but VIP address stay on previous/failed primary instance.
I checked escalation.sh content, privileges on execute, sudo privileges for
postgres user on arping and ip.
Everything checked few times but I can't find problem. Only thing that
helped was stop pgpool2 on previous/failed primary instance too.
Then VIP was delegated to another node.

What I found in pgpool.log (verbose logging) is:

2023-09-13 14:24:22.935: watchdog_utility pid 901050: LOG:  watchdog
escalation successful
2023-09-13 14:24:22.935: watchdog_utility pid 901050: LOCATION:
 wd_escalation.c:119
2023-09-13 14:24:24.059: watchdog_utility pid 901050: LOG:  failed to
acquire the delegate IP address
2023-09-13 14:24:24.059: watchdog_utility pid 901050: DETAIL:  'if_up_cmd'
failed
2023-09-13 14:24:24.059: watchdog_utility pid 901050: LOCATION:  wd_if.c:182
2023-09-13 14:24:24.059: watchdog_utility pid 901050: WARNING:  watchdog
escalation failed to acquire delegate IP
2023-09-13 14:24:24.060: watchdog_utility pid 901050: LOCATION:
 wd_escalation.c:140
2023-09-13 14:24:24.062: watchdog pid 899237: LOG:  watchdog escalation
process with pid: 901050 exit with SUCCESS.

This is pgpool.conf (same on all sevrers)

backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
backend_hostname0 = 'server1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/15/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'
backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/15/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'
backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/15/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server3'
enable_pool_hba = on
log_destination = 'stderr'
log_connections = off
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
sr_check_user = 'pgpool'
sr_check_password = ''
follow_primary_command = '/etc/pgpool2/follow_primary.sh %d %h %p %D %m %H
%M %P %r %R'
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
failover_command = '/etc/pgpool2/failover.sh %d %h %p %D %m %H %M %P %r %R
%N %S'
recovery_user = 'postgres'
recovery_password = 'pgpool'
recovery_1st_stage_command = 'recovery_1st_stage'
use_watchdog = on
hostname0 = 'server1'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'server2'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'server3'
wd_port2 = 9000
pgpool_port2 = 9999
delegate_IP = 'XXX.XXX.XXX.XXX' -- #mocked
if_cmd_path = '/sbin'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens160 label
ens160:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens160'
arping_path = '/usr/sbin'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens160'
wd_escalation_command = '/etc/pgpool2/escalation.sh'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
heartbeat_hostname0 = 'server1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'server2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'server3'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30

Could anyone experience similar problem and solve it ?

Thanks!
Marian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230913/46f16274/attachment.htm>