Re: [pgpool-general: 9106] Postgres/pgpool HA failover process

[Mukesh Tanuku <mukesh.postgres@xxxxxxxxx>]

BTW here is the network configuration we set as

UTOCONNECT_PRIORITY=120
BOOTPROTO=dhcp
DEVICE=eth0
DHCPV6C=yes
HWADDR=<MAC Address>
IPV6INIT=yes
_ONBOOT_=yes
TYPE=Ethernet
USERCTL=no

Regards

Mukesh Tanuku

On Wed, May 29, 2024 at 12:53 PM Mukesh Tanuku <mukesh.postgres@xxxxxxxxx> wrote:

Thanks Bo for your response to my email.

Below is the parameters:

  if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
  if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8'
  arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'

The above commands are working on Leader node. and once the VIP is up successfully then we can run the pcp commands via VIP host work only from the leader node locally. if we try to execute the PCP comands via VIP 

throws an error 

ERROR: connection to host "<VIP>" failed with error "No route to host"

What's the possible cause in this case? 

Regards

Mukesh Tanuku

On Tue, May 28, 2024 at 5:30 AM Bo Peng <pengbo@xxxxxxxxxxxx> wrote:

Hi,

> Hello Everyone,
>
> We are working on to setup a High available solution for postgres and
> running some test cases
> based on that we need some clarification on the HA setup as per the same
> configuration given in this example
> <https://www.pgpool.net/docs/42/en/html/example-cluster.html>

If you want to configure a new cluster,
we recommend using the latest version.

https://www.pgpool.net/docs/latest/en/html/example-cluster.html

> Question - 01
> Regarding the active client connections that are connecting via delegate
> VIP, what happens to those connections during the failover process? If
> either the primary DB goes down (gracefully/crash) then the
> active connection will get hung till the new primary DB is available or
> those active connections get terminated?

The existing connections will be disconnected if primary DB is down.
However, the subsequent connections will be routed to the new primary.

> What if the same case for Leader pgpool service as well?

Yes.

> Questions - 02
> Configuring the delegate IP:
> a. Are there any standards/limitations for choosing the unused IP as
> delegate IP? I mean like the IP must be within the same subnet range of all
> the nodes in the cluster?

No limitations in Pgpool-II, it depends on the OS.
Pgpool-II using "ip addr ..." command to assign or release a delegate IP.

Below is the parameters:

  if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
  if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8'
  arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'

You need to make sure the commands work.

> b. Any challenges we see for non-root user to work with VIP? because we run
> the pgpool with non-root user.

You need to make sure the non-root user can execute ip and arping command with sudo
without a password.

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