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 1qnKOl-00Acxh-0X for pgsql-general@arkaria.postgresql.org; Mon, 02 Oct 2023 14:59:51 +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 1qnKOj-00Fn1N-9i for pgsql-general@arkaria.postgresql.org; Mon, 02 Oct 2023 14:59:49 +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 1qnKOi-00Fn1E-TP for pgsql-general@lists.postgresql.org; Mon, 02 Oct 2023 14:59:48 +0000 Received: from mail-yw1-x1132.google.com ([2607:f8b0:4864:20::1132]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qnKOd-007nrE-GE for pgsql-general@lists.postgresql.org; Mon, 02 Oct 2023 14:59:47 +0000 Received: by mail-yw1-x1132.google.com with SMTP id 00721157ae682-5a229ac185aso44195347b3.1 for ; Mon, 02 Oct 2023 07:59:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696258782; x=1696863582; darn=lists.postgresql.org; 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=BDAmWZlrnVNc5RmIJ6JyH3CgaBBp1U/A2PRqTs2mx/4=; b=X0HJwQ6CxBS9U1zp1Jis52On+zZB9tt0yN6rxaYKiKkZTU46V6zhqGfIYsyysirJoh xlngO0QBC5tMXApZgRFtO8Nq+HAKPc1uNTnZWoayBtBF4XHSCX+F54VB5YLmHZnl/brL 2yResk/o3jXjy4u4tk9iYrQyzin3zOK0vURzPqCWuWbB9IF//saF71H5hPuhrhFhoYF6 COENcNGEn/sJY/LU4RE4CasrNWZLSNevM6GdT8IXOmFbf3xI7dy1hXr0lyNBRAcqDV6j RYD+U4EdH56ascxPT7sJhT9yFopR8nKeuEu5CQyrBoXIQKDX+PNCd+tL6ZdbyF9GVcCR d8ag== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696258782; x=1696863582; 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=BDAmWZlrnVNc5RmIJ6JyH3CgaBBp1U/A2PRqTs2mx/4=; b=jsmCAnStbId8rc0K5DUIEs5c34hVPAhqIZu26/1PVvwl98p3lvvA5EBnyqJ44q/bsq 8bAIv6tvGgAIbTsynALcPpHZQuWQyGubJ9MTpttR80Wy6bqlKZy2swMbR5qo5wwaEAsw p2v9aJ5sfo7yjI7aBxSm+eTzUycKLbQJjMqe9uaqQByWhn5/EvLyzvFNZwWtJ8bFR/jS REW/yM5njYUTRx/mBlzyXemNn2MnreEn51Ww4dwsC9g5fb22gqK5Y4YXG1GiQ0r6zkKq 8xwa2cNTe53/1319KQlQ9HvnQu21BnS2Ryws31T6125B6tVWFNvoU9uJKF0p/rQnXjWD KAlQ== X-Gm-Message-State: AOJu0Yzj2D4PU/Ytlht0uRCNwtcj3KZMsH61VxoSO09t5socAWbFR1d0 XFf+0MSD3l/sDIFC7uwlm0nvnNfsgOg= X-Google-Smtp-Source: AGHT+IGPpXs9Rio0608ZBQIfux1osFPKGms89893dH3PSLCoV3YBen0c6LIp0JplhU5nY8oEBPnzVw== X-Received: by 2002:a81:8907:0:b0:59b:518a:639c with SMTP id z7-20020a818907000000b0059b518a639cmr12226050ywf.36.1696258782586; Mon, 02 Oct 2023 07:59:42 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:3036:dcd3:c56e:c82f? ([2600:1700:bd2c:b810:3036:dcd3:c56e:c82f]) by smtp.googlemail.com with ESMTPSA id i13-20020a816d0d000000b005a4d922cf77sm158943ywc.119.2023.10.02.07.59.42 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 02 Oct 2023 07:59:42 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------hgQThbstVUrmK2P5C4LcojHT" Message-ID: <2b33c1e3-3dc8-b312-3a0d-9a205cf872ce@gmail.com> Date: Mon, 2 Oct 2023 09:59:41 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Operating of synchronous master when no standby is available Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: 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. --------------hgQThbstVUrmK2P5C4LcojHT Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/2/23 03:54, Sergey Cherukhin wrote: > Hello! > > I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in > synchronous replication mode. When one of the nodes is down, clients hang > on INSERT operations because the primary server waits until standby > confirms that it got the data, but the standby node is down. > > I need clients to feel no difference when they operate with a fully > operational cluster or with a degraded cluster containing primary node alone. This is why we use async physical replication even with local HA clusters. > Is it possible to configure synchronous replication to operate normally > when primary and replica are online, but primary no waits confirmation > from replica if replica is not connected? Locally, async replication over Gbit (or faster) NICs will /probably/ be Good Enough.  (I ran pgbench at loads that saturated all the CPUs and inserted 100GB of data against a pgpool cluster -- pgpool VIP on one node, primary database on the second node and replica on the third node -- and saw no lag.) -- Born in Arizona, moved to Babylonia. --------------hgQThbstVUrmK2P5C4LcojHT Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/2/23 03:54, Sergey Cherukhin wrote:
Hello!

I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances in synchronous replication mode. When one of the nodes is down, clients hang on INSERT operations because the primary server waits until standby confirms that it got the data, but the standby node is down.

I need clients to feel no difference when they operate with a fully operational cluster or with a degraded cluster containing primary node alone.

This is why we use async physical replication even with local HA clusters.

Is it possible to configure synchronous replication to operate normally when primary and replica are online, but primary no waits confirmation from replica if replica is not connected?

Locally, async replication over Gbit (or faster) NICs will probably be Good Enough.  (I ran pgbench at loads that saturated all the CPUs and inserted 100GB of data against a pgpool cluster -- pgpool VIP on one node, primary database on the second node and replica on the third node -- and saw no lag.)

--
Born in Arizona, moved to Babylonia.
--------------hgQThbstVUrmK2P5C4LcojHT--