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 1pkeRd-0006Ar-Ls for pgsql-general@arkaria.postgresql.org; Fri, 07 Apr 2023 05:15:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pkeRc-0007OB-HW for pgsql-general@arkaria.postgresql.org; Fri, 07 Apr 2023 05:15:28 +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 1pkeRc-0007O2-4r for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 05:15:28 +0000 Received: from mail-yb1-xb33.google.com ([2607:f8b0:4864:20::b33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pkeRZ-001Nc1-DW for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 05:15:27 +0000 Received: by mail-yb1-xb33.google.com with SMTP id i20so1539931ybg.10 for ; Thu, 06 Apr 2023 22:15:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680844523; x=1683436523; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=Y1vKhs8eAC7yZKOjiNSlNkVzSORg1FUmPldNflXYw1w=; b=p0JEo3qiCWwZZeBKbmcffFXMAas1PVQxAGIHNSPijxTzqe0/evsqdLuH9LeXi9/zNV 4QrSQJtXVTWy1SyXt8HSuo6OS78fXUvcavphbBStLKYhBTZ5lwpjYpj9WfR7WkRu1H4b lYb2pAI9Nty3JCczEuGfU6UKODumlbIqmZlYMbmiIzuek/eiqkuO6EYQPA41Ndu6WxYs RPzHV1yKUSNTASaTSk6xEJtoTbHkFnO5UAs9sQrmmnnING2tWVCUMoz1AnOV9taSEe7U XnOBmlt0yf4SUxX3Y/MPKRLL385kDi5DWrcSPrWwvrq5iL+TbUnk1PLvg0mWQXYlfmOh LXtQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680844523; x=1683436523; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Y1vKhs8eAC7yZKOjiNSlNkVzSORg1FUmPldNflXYw1w=; b=7Zjsqjyl32iTTDLhCJkFGDJHATTzY3FRr1p1sFAmNOd/qjNHiU6SQfD/hRE0c3fq5u DtCehoCt4jEEPWQo762seY/FGygL/zjImQiGokLDhu+Qwmn/zSWpCG51n9S+yMt4nbum TayaqCUELZy92cnctwSp75vxspfy/EKvG56sEUYWU66iQvTkF4L484ivdmqRlz2ZgYyP NgngRC44T75UlvGgMriiyKL/rUtbcN6V5Rxzn6WBM2XhpXbSBHvsExzH8z0Ki3rrhdGM Jf9CvN6e1guU8ZzLDfokkHQ84CP3BIgMUGn65XP3tN2IgtOEEmdNvH309DD6ujJybi6n 38Bg== X-Gm-Message-State: AAQBX9d/97quhUUtVGYlIkzXRjJPopwDcGztjqM2vDZ3YSeXEeGUrxJr BP4AJ7UYuTloOIBtQEHarca5G+UxirQ= X-Google-Smtp-Source: AKy350bbxNwAQQUVC3Qtr6iy1U44jOjAcwLQ4dX+koO4trz1mVsh4CHKMk+VS35w5+SQOo026e63sQ== X-Received: by 2002:a25:4011:0:b0:b8b:f05d:4e79 with SMTP id n17-20020a254011000000b00b8bf05d4e79mr1779959yba.34.1680844523202; Thu, 06 Apr 2023 22:15:23 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:285e:1e89:8b2f:497f? ([2600:1700:bd2c:b810:285e:1e89:8b2f:497f]) by smtp.googlemail.com with ESMTPSA id f11-20020a25b08b000000b00b7767ca7466sm901202ybj.3.2023.04.06.22.15.22 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 06 Apr 2023 22:15:22 -0700 (PDT) Message-ID: <0fd18202-5dba-9cf0-bd40-4156e39f9b9b@gmail.com> Date: Fri, 7 Apr 2023 00:15:22 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: Patroni vs pgpool II Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <20230405.165015.9256683667589504.t-ishii@sranhm.sra.co.jp> <20230406174158.62c47a30@karst> <20230407.131659.169784800159734443.t-ishii@sranhm.sra.co.jp> From: Ron In-Reply-To: <20230407.131659.169784800159734443.t-ishii@sranhm.sra.co.jp> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/6/23 23:16, Tatsuo Ishii wrote: >>>> But, I heard PgPool is still affected by Split brain syndrome. >>> 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. >> 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 would >> avoid that. > Ok, "split brain" means here that there are two or more PostgreSQL > primary serves exist. > > 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. > > 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). > > In this situation if w2 promotes pg1 because w0 seems to be down, then > the system ends up with two primary servers: split brain. > > 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. > > 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. And this concept is quite old.  (It's also what Windows clustering uses.) -- Born in Arizona, moved to Babylonia.