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 1pkk19-0003R1-Iy for pgsql-general@arkaria.postgresql.org; Fri, 07 Apr 2023 11:12:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pkk18-00005I-Bd for pgsql-general@arkaria.postgresql.org; Fri, 07 Apr 2023 11:12:30 +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 1pkk17-000056-UR for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 11:12:30 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pkk13-001Qir-D6 for pgsql-general@lists.postgresql.org; Fri, 07 Apr 2023 11:12:29 +0000 Received: by mail-yb1-xb36.google.com with SMTP id k17so48648908ybm.11 for ; Fri, 07 Apr 2023 04:12:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680865943; x=1683457943; h=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=BPDmDJmIYp1QTP5hvdl6GeB9yZauEwPBqLeRKvWDVTY=; b=CSiVMgpRYXT9h5vUHlMqOIyjzjrzHpBpMW7s+DsnUALaWyB8ZXzaHDG0cB+D3WYULF B7+IEttu95MFZ0ZyOcuZu0bZ3A9LNxTCGTbZKGkT6hJf+Uictz87oabQC6Eb4ELnIGOV cQqTittMhL5FIFyAtqqgf7wZzy5lAlOBdltE7sxOJpeYy3VYbnXUFr5laR4ppErNHM5R oPrSqgtTLOiJLFyPdWXFfvGJoFYL0MbhkJ58vg4nc2tww4kKcgwiHLjGGf1HAT6xVr+W pZ5xBCITXq2+9k2rn5DZqwZz80VV7mNugEtsRRdPr1FOZZza3RRBEd/xWm83Cd6bNUZf F5nQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680865943; x=1683457943; h=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=BPDmDJmIYp1QTP5hvdl6GeB9yZauEwPBqLeRKvWDVTY=; b=RkmKOn0jhQ+UiHNSpGWfF31v9/InXP0AvO9N1/3aU11HzXwpWkUTeD86JdsBOJPE7B CdFKlyojACtj5agUROl0ddaPtRok7jAQpmltD8MaCvYD/2TKn96FmUBOuvlFw+VtiFdl XsPWZ9YQD1c/IQIjjXMyzhDLwk1cOgE7BMr4tY23fy7I5A+Y6JpqkxXP/NOfVfhah02+ +2Xf8rsg6IaBTPUcNOS8Pn4ALVMFvAgoD6508lktjrg0Tc6fTydNBxeJfW1Jx5mIbTJZ GPj2JgO17RsgdBh9sP2MS0qCFwf2AYmA2vtCl9ivpbQIv+L4g0qpoUi+9qJ8gghFOHGg M/Aw== X-Gm-Message-State: AAQBX9cjrwXVKoYEw/2+ptlH6Ughhf1bVpSgvPeguQqLzQluyZPBn3T1 vM6sJfyx9IByvi7p37b9CA6I5jcweDY= X-Google-Smtp-Source: AKy350b44lKNE2LbwofCZaoaHJARKU+UdPF/JItFo06XNKZHgskLdh3nQVjkcucOnHfLriIZlDVENw== X-Received: by 2002:a25:ad83:0:b0:b8a:f6d8:34aa with SMTP id z3-20020a25ad83000000b00b8af6d834aamr2820921ybi.8.1680865943272; Fri, 07 Apr 2023 04:12: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 p187-20020a2574c4000000b00b8c01fec4a4sm740948ybc.49.2023.04.07.04.12.22 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 07 Apr 2023 04:12:23 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------0E6zjzUFLIuyMe1rvRb0i0hx" Message-ID: Date: Fri, 7 Apr 2023 06:12: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: <20230406174158.62c47a30@karst> <20230407.131659.169784800159734443.t-ishii@sranhm.sra.co.jp> <20230407094557.3def3121@karst> <20230407.180405.1284467321161872462.t-ishii@sranhm.sra.co.jp> <20230407124612.7a6d7231@karst> From: Ron In-Reply-To: <20230407124612.7a6d7231@karst> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0E6zjzUFLIuyMe1rvRb0i0hx Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 4/7/23 05:46, Jehan-Guillaume de Rorthais wrote: > On Fri, 07 Apr 2023 18:04:05 +0900 (JST) > Tatsuo Ishii wrote: > >>> And I believe that's part of what Cen was complaining about: >>> >>> « >>> It is basically a daemon glued together with scripts for which you are >>> entirely responsible for. Any small mistake in failover scripts and >>> cluster enters  a broken state. >>> » >>> >>> If you want to build something clean, including fencing, you'll have to >>> handle/dev it by yourself in scripts >> That's a design decision. This gives maximum flexibility to users. > Sure, no problem with that. But people has to realize that the downside is that > it left the whole complexity and reliability of the cluster in the hands of > the administrator. And these are much more complicated and racy than > a simple promote node. > > Even dealing with a simple vIP can become a nightmare if not done correctly. > >> Please note that we provide step-by-step installation/configuration >> documents which has been used by production systems. >> >> https://www.pgpool.net/docs/44/en/html/example-cluster.html > These scripts rely on SSH, which is really bad. What if you have a SSH failure > in the mix? > > Moreover, even if SSH wouldn't be a weakness by itself, the script it doesn't > even try to shutdown the old node or stop the old primary. That does not matter, when only PgPool does the writing to the database. > You can add to the mix that both Pgpool and SSH rely on TCP for availability > checks and actions. You better have very low TCP timeout/retry... > > When a service lose quorum on a resource, it is supposed to shutdown as fast as > possible... Or even self-fence itself using a watchdog device if the shutdown > action doesn't return fast enough. Scenario: S0 - Running Postgresql as primary, and also PgPool. S1 - Running Postgresql as secondary, and also PgPool. S2 - Running only PgPool.  Has the VIP. There's no /need/ for Postgresql or PgPool on server 0 to shut down if it loses contact with S1 and S2, since they'll also notice that that S1 has disappeared.  In that case, they'll vote S1 into degraded state, and promote S1 to be the Postgresql primary. A good question is what happens when S0 and S1 lose connection to S2 (meaning that S2 loses connection to them, too).  S0 and S1 then "should" vote that S0 take over the VIP.  But, if S2 is still up and can connect to "the world", does it voluntarily decide to give up the VIP since it's all alone? -- Born in Arizona, moved to Babylonia. --------------0E6zjzUFLIuyMe1rvRb0i0hx Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 4/7/23 05:46, Jehan-Guillaume de Rorthais wrote:
On Fri, 07 Apr 2023 18:04:05 +0900 (JST)
Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

And I believe that's part of what Cen was complaining about:

«
  It is basically a daemon glued together with scripts for which you are 
  entirely responsible for. Any small mistake in failover scripts and 
  cluster enters  a broken state.
»

If you want to build something clean, including fencing, you'll have to
handle/dev it by yourself in scripts  
That's a design decision. This gives maximum flexibility to users.
Sure, no problem with that. But people has to realize that the downside is that
it left the whole complexity and reliability of the cluster in the hands of
the administrator. And these are much more complicated and racy than 
a simple promote node.

Even dealing with a simple vIP can become a nightmare if not done correctly.

Please note that we provide step-by-step installation/configuration
documents which has been used by production systems.

https://www.pgpool.net/docs/44/en/html/example-cluster.html
These scripts rely on SSH, which is really bad. What if you have a SSH failure
in the mix? 

Moreover, even if SSH wouldn't be a weakness by itself, the script it doesn't
even try to shutdown the old node or stop the old primary.

That does not matter, when only PgPool does the writing to the database.

You can add to the mix that both Pgpool and SSH rely on TCP for availability
checks and actions. You better have very low TCP timeout/retry...

When a service lose quorum on a resource, it is supposed to shutdown as fast as
possible... Or even self-fence itself using a watchdog device if the shutdown
action doesn't return fast enough.

Scenario:
S0 - Running Postgresql as primary, and also PgPool.
S1 - Running Postgresql as secondary, and also PgPool.
S2 - Running only PgPool.  Has the VIP.

There's no need for Postgresql or PgPool on server 0 to shut down if it loses contact with S1 and S2, since they'll also notice that that S1 has disappeared.  In that case, they'll vote S1 into degraded state, and promote S1 to be the Postgresql primary.

A good question is what happens when S0 and S1 lose connection to S2 (meaning that S2 loses connection to them, too).  S0 and S1 then "should" vote that S0 take over the VIP.  But, if S2 is still up and can connect to "the world", does it voluntarily decide to give up the VIP since it's all alone?

--
Born in Arizona, moved to Babylonia.
--------------0E6zjzUFLIuyMe1rvRb0i0hx--