Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkgo7-0003lX-Kl for pgsql-general@arkaria.postgresql.org; Fri, 07 Apr 2023 07:46:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pkgo5-0003Dv-J4 for pgsql-general@arkaria.postgresql.org; Fri, 07 Apr 2023 07:46:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkgo5-0003Dl-6d for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 07:46:49 +0000 Received: from mail1.dalibo.net ([51.159.93.128] helo=mail.dalibo.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pkgo1-001P2W-S2 for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 07:46:48 +0000 Received: from karst (larco.ioguix.net [78.202.0.6]) by mail.dalibo.com (Postfix) with ESMTPSA id D7C132007F; Fri, 7 Apr 2023 09:45:57 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=dalibo.com; s=a; t=1680853558; bh=NpLwH0IFVxDwFSGmlBiHYZZiVNPIVfrJlixSM0aweBs=; h=Date:From:To:Cc:Subject:In-Reply-To:References:From; b=lL7pFZa2DwLUMsP0jSVDoN5v/ITK1OdKWGVLeG4BmDPBEXr+zCg8PUi6usGhbrO4V UAwVb5jQiOiDGPM3mzH6u1YArlmIa/KmiqCrrrch+j3R0FEQEZiISkU0NFC/IWgzrG iBL0ZQd6GBHSyFqs95b01yWUTl9FJh93pLVDgbtc= Date: Fri, 7 Apr 2023 09:45:57 +0200 From: Jehan-Guillaume de Rorthais To: Tatsuo Ishii Cc: inzamam.shafiq@hotmail.com, cyberdemn@gmail.com, pgsql-general@lists.postgresql.org Subject: Re: Patroni vs pgpool II Message-ID: <20230407094557.3def3121@karst> In-Reply-To: <20230407.131659.169784800159734443.t-ishii@sranhm.sra.co.jp> References: <20230405.165015.9256683667589504.t-ishii@sranhm.sra.co.jp> <20230406174158.62c47a30@karst> <20230407.131659.169784800159734443.t-ishii@sranhm.sra.co.jp> Organization: Dalibo MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 07 Apr 2023 13:16:59 +0900 (JST) Tatsuo Ishii wrote: > >> > But, I heard PgPool is still affected by Split brain syndrome. =20 > >>=20 > >> Can you elaborate more? If more than 3 pgpool watchdog nodes (the > >> number of nodes must be odd) are configured, a split brain can be > >> avoided. =20 > >=20 > > Split brain is a hard situation to avoid. I suppose OP is talking about > > PostgreSQL split brain situation. I'm not sure how PgPool's watchdog wo= uld > > avoid that. =20 >=20 > Ok, "split brain" means here that there are two or more PostgreSQL > primary serves exist. >=20 > Pgpool-II's watchdog has a feature called "quorum failover" to avoid > the situation. To make this work, you need to configure 3 or more > Pgpool-II nodes. Suppose they are w0, w1 and w2. Also suppose there > are two PostgreSQL servers pg0 (primary) and pg1 (standby). The goal > is to avoid that both pg0 and pg1 become primary servers. >=20 > Pgpool-II periodically monitors PostgreSQL healthiness by checking > whether it can reach to the PostgreSQL servers. Suppose w0 and w1 > detect that pg0 is healthy but pg1 is not, while w2 thinks oppositely, > i.e. pg0 is unhealthy but pg1 is healthy (this could happen if w0, w1, > pg0 are in a network A, but w2 and pg1 in different network B. A and B > cannot reach each other). >=20 > In this situation if w2 promotes pg1 because w0 seems to be down, then > the system ends up with two primary servers: split brain. >=20 > With quorum failover is enabled, w0, w1, and w2 communicate each other > to vote who is correct (if it cannot communicate, it regards other > watchdog is down). In the case above w0 and w1 are majority and will > win. Thus w0 and w1 just detach pg1 and keep on using pg0 as the > primary. On the other hand, since wg2 looses, and it gives up > promoting pg1, thus the split brain is avoided. >=20 > Note that in the configuration above, clients access the cluster via > VIP. VIP is always controlled by majority watchdog, clients will not > access pg1 because it is set to down status by w0 and w1. >=20 > > To avoid split brain, you need to implement a combinaison of quorum and > > (self-)fencing. > >=20 > > Patroni quorum is in the DCS's hands. Patroni's self-fencing can be ach= ieved > > with the (hardware) watchdog. You can also implement node fencing throu= gh > > the "pre_promote" script to fence the old primary node before promoting= the > > new one. > >=20 > > If you need HA with a high level of anti-split-brain security, you'll n= ot be > > able to avoid some sort of fencing, no matter what. > >=20 > > Good luck. =20 >=20 > Well, if you define fencing as STONITH (Shoot The Other Node in the > Head), Pgpool-II does not have the feature. And I believe that's part of what Cen was complaining about: =C2=AB It is basically a daemon glued together with scripts for which you are=20 entirely responsible for. Any small mistake in failover scripts and=20 cluster enters=C2=A0 a broken state. =C2=BB If you want to build something clean, including fencing, you'll have to handle/dev it by yourself in scripts > However I am not sure STONITH is always mandatory. Sure, it really depend on how much risky you can go and how much complexity= you can afford. Some cluster can leave with a 10 minute split brain where some = other can not survive a 5s split brain. > I think that depends what you want to avoid using fencing. If the purpose= is > to avoid having two primary servers at the same time, Pgpool-II achieve t= hat > as described above. How could you be so sure? See https://www.alteeve.com/w/The_2-Node_Myth =C2=AB * Quorum is a tool for when things are working predictably * Fencing is a tool for when things go wrong =C2=BB Regards,