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

[Bo Peng]

Hi,

This is expected behavior.

VIP is assigned to Pgpool nodes, not PostgreSQL nodes.
The application connects to PostgreSQL via Pgpool-II
instead of connecting to PostgreSQL directly.

On Wed, 13 Sep 2023 17:05:35 +0200
Marian Pompura <marian.pompura at gmail.com> wrote:

> 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


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/