Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCvoX-0092u7-V9 for pgsql-general@arkaria.postgresql.org; Fri, 31 May 2024 06:32:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sCvoX-009I2r-PX for pgsql-general@arkaria.postgresql.org; Fri, 31 May 2024 06:32:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCvoX-009I2i-AA for pgsql-general@lists.postgresql.org; Fri, 31 May 2024 06:32:33 +0000 Received: from mail-yb1-xb30.google.com ([2607:f8b0:4864:20::b30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sCvoU-002mRO-5N for pgsql-general@lists.postgresql.org; Fri, 31 May 2024 06:32:31 +0000 Received: by mail-yb1-xb30.google.com with SMTP id 3f1490d57ef6-dfa4ad7f6dfso1557043276.1 for ; Thu, 30 May 2024 23:32:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1717137149; x=1717741949; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=gWP0kVKycrhhSigHiyvzWC9ecblQNh0OI/s4RKBPGaE=; b=FQFx3YSykv0fGxnoXdUti+6P6lbnfzageTjiEXF0qbI0MGCn3z+VcaXdCHOPZBO6lG YAITP3cVd/2RSqo/maK3P2fP3rWh9OWDxPeOO++U42pB2CPD4vfurf28NKkS8Wz1lFMs eNduVrjW7bMTSvPaEsEZNnIr1oCMlM/mLPiaKPcJ4MZu4m0bcCSKpMBW6kU8VoGfCM/7 qfO56YK7tJ3NE4DEJHxKSOTzRyoNz3cTAgTH+zHhcQvldVTv+KQmK36dWe1NR4iUWsNM D5Ux1KslIWkLLCGYMucxSywbaQ9NxQE9ufE8+m6QZoGOBNe5r9ln+fKqBHrtUgn0pVad NqGA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1717137149; x=1717741949; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=gWP0kVKycrhhSigHiyvzWC9ecblQNh0OI/s4RKBPGaE=; b=kNA2rVxgOL6XdbOHOpe9uDtM7fQjRjkqYDanRRMQviJDve25cuXjTwhCDcrKliRZwI Dx+zOKhhAFDgdi/RoMC/Z40/xRKXQmqWtfL1MnL6lKKE89gbMdMudKo1oFzOOlr/a0xG s7pa1HDlH7tdISSRQBun9UK7wRJ/1crYeNY0ECsuxy1Wp6JZybOyasnfGeXWkxaiBIuG oFiVLJPyEHdvBg6Nn+u44xuOnuKwjGf6CR3TDgJfARLPVPurl4YyaS0BjBhO26Js8du0 brMGvd9MiKqhVwjwJsfgN1BodDXnmzsPR+euGf2oL0xRrstR5xg4PCt27+SBraqKLN0d xOaA== X-Forwarded-Encrypted: i=1; AJvYcCV7voS6WhuoMHgywGmK9X+oQIwIJKZ2mly8+XjQXMEOwWEyfPVpsiZ7zvu+vNdOM3wKbB2F1WIx40VKhFxJmFmOpmjLnDgVpF82zTVyWE5yoI/x X-Gm-Message-State: AOJu0YxmW0G2aKLhOJrbORqcGuV7hCMt7jJIx5WxYYR7E6/V2Z+zQFuY q20SGdLHmgu90s3e0ucP3ieZdEwNuhy928EN01TC3oMXkovBkKNZnJpV3inBKWPcIwU7iw6MWd2 tYT4G7Hn3jLX2dRdM7BQELy7L3Mo= X-Google-Smtp-Source: AGHT+IG52VB6farNnTHC/+MfEFJjjiS1sK0MjB/3xiA3dw0UBKRtHlGFrpPiGBqG2C/1uMS8LqqyK9rdp5eXGdu/5Ms= X-Received: by 2002:a25:905:0:b0:dd1:491e:bf0 with SMTP id 3f1490d57ef6-dfa73ddc114mr862635276.60.1717137149290; Thu, 30 May 2024 23:32:29 -0700 (PDT) MIME-Version: 1.0 References: <20240528090041.674647fd79cc4b54692dbfb1@sraoss.co.jp> In-Reply-To: From: Mukesh Tanuku Date: Fri, 31 May 2024 12:02:17 +0530 Message-ID: Subject: Re: [pgpool-general: 9106] Postgres/pgpool HA failover process To: Bo Peng , pgsql-general@lists.postgresql.org, pgpool-general@pgpool.net Content-Type: multipart/alternative; boundary="000000000000d075860619ba257e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d075860619ba257e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable BTW here is the network configuration we set as UTOCONNECT_PRIORITY=3D120 BOOTPROTO=3Ddhcp DEVICE=3Deth0 DHCPV6C=3Dyes HWADDR=3D IPV6INIT=3Dyes ONBOOT=3Dyes TYPE=3DEthernet USERCTL=3Dno Regards Mukesh Tanuku On Wed, May 29, 2024 at 12:53=E2=80=AFPM Mukesh Tanuku wrote: > Thanks Bo for your response to my email. > > Below is the parameters: > > > > * if_up_cmd =3D '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 lab= el > enp0s8:0' if_down_cmd =3D '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev > enp0s8' arping_cmd =3D '/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 "" 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=E2=80=AFAM Bo Peng wro= te: > >> 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 sa= me >> > configuration given in this example >> > >> >> 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 delega= te >> > 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 o= r >> > 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 o= f >> 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 =3D '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 la= bel >> enp0s8:0' >> if_down_cmd =3D '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8' >> arping_cmd =3D '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s= 8' >> >> You need to make sure the commands work. >> >> > b. Any challenges we see for non-root user to work with VIP? because w= e >> run >> > the pgpool with non-root user. >> >> You need to make sure the non-root user can execute ip and arping comman= d >> with sudo >> without a password. >> >> -- >> Bo Peng >> SRA OSS LLC >> TEL: 03-5979-2701 FAX: 03-5979-2702 >> URL: https://www.sraoss.co.jp/ >> > --000000000000d075860619ba257e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
BTW here is the network configuration=C2=A0we set as
<= br>
UTOCONNECT_PRIORITY=3D120
BOOTPROTO=3Ddhcp
DEVICE=3Deth0
DHCPV6C=3Dyes
HWADDR=3D<MAC Address>
IPV6INIT=3Dyes
ONBOOT=3Dyes
TYPE=3DEthernet
USERCTL=3Dno

Regards
= Mukesh Tanuku

On Wed, May 29, 2024 at 12:53=E2=80=AFPM Mukesh T= anuku <mukesh.postgres@gmai= l.com> wrote:
Thanks Bo for your response to my email.

Below is the parameters:

=C2=A0 if_up_cmd =3D '/usr/bi= n/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
=C2=A0= if_down_cmd =3D '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8&= #39;
=C2=A0 arping_cmd =3D '/usr/bin/sudo /usr/sbin/arping -U $_IP_$= -w 1 -I enp0s8'

The above commands ar= e working on Leader node. and once the VIP is up successfully then we can r= un the pcp commands=C2=A0via VIP host work only from the leader node locall= y. if we try to execute the PCP comands=C2=A0via VIP=C2=A0
throws= an error=C2=A0
ERROR: connection to host "= ;<VIP>" failed with error "No route to host"

What's the possible cause in this case?=C2= =A0

Regards
Mukesh Tanuku

=

On Tue, May 28, 2024 at 5:30=E2=80=AFAM Bo Peng <pengbo@sraoss.co.jp> wrote:<= br>
Hi,

> Hello Everyone,
>
> We are working on to setup a High available solution for postgres and<= br> > running some test cases
> based on that we need some clarification on the HA setup as per the sa= me
> configuration given in this example
> <https://www.pgpool.net/docs/42/e= n/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 delega= te
> 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 o= r
> 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 o= f 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 dele= gate IP.

Below is the parameters:

=C2=A0 if_up_cmd =3D '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp= 0s8 label enp0s8:0'
=C2=A0 if_down_cmd =3D '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev e= np0s8'
=C2=A0 arping_cmd =3D '/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 w= e run
> the pgpool with non-root user.

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

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