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 1rdLZm-00567b-8a for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Feb 2024 02:46:15 +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 1rdLZk-00FOWK-5r for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Feb 2024 02:46:12 +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 1rdLZi-00FOWC-AR for pgsql-hackers@lists.postgresql.org; Fri, 23 Feb 2024 02:46:12 +0000 Received: from fout8-smtp.messagingengine.com ([103.168.172.151]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rdLZd-000Wv3-Bo for pgsql-hackers@lists.postgresql.org; Fri, 23 Feb 2024 02:46:09 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailfout.nyi.internal (Postfix) with ESMTP id 4E66313800E5; Thu, 22 Feb 2024 21:46:05 -0500 (EST) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Thu, 22 Feb 2024 21:46:05 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1708656365; x=1708742765; bh=vkZsFRvJ3v gzvE4lmjgMaVFs1EwgVXi2ZZ/N92wD9E8=; b=A/OTplu63SezGKrKBWoV2I7EzM Cb3YNEXyZz73WPJf9HwG3ckCs6c8CITrAeI89PsSm1Bi4GcnszN8Q+n8h6fBr2mN pXOC6yXXo1aKQjsjLA3wRceGrpn+15hSX1li6eCVkBUb9WwwUKgRAaDK772xE0Dp Mkyh/VFIYCIvM1zlCmN5tJvYrEV8x6DN1qL1PhPiHGtOKoYVGZ8nkloS+8vmK34X ryMVRKZ43SSeKiqsO/+ncqqio3ZQmM5ihArb/rYDiplNPxp1uHVmW9CDVfHdiQO/ Eo1RRVr5gChBRi4sNrpfi3t3oINhnuGhHx1nC0m1yfFcaNQMbzOo0ssYbF6g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; t=1708656365; x=1708742765; bh=vkZsFRvJ3vgzvE4lmjgMaVFs1Ewg VXi2ZZ/N92wD9E8=; b=Yk/TL5GjRqFd7ovegHLNzk/4ebGPK+tJVPxE6VHIcu0F m+VN77b1DSRtZXT1gkmLv44vSWWBffAYUWpzMYYMgGrgZvaNtXKxUisYkKTLpXs7 SklJcX+7Sr3ai2kmcUU+wg+Bv/017bHVC1vMLiFdHVGGmE63Deh8fRgyjd+7fzSW JCBdFrj36TqALxTVmcN+TjH9maTTGesIeHhC0EC3TJmUyHsTejpsJ9Q+I6FAFzy1 lV44diHFK4DSb8sELZaL/h19noZSnxF+4roJRI4LGYTnSQ+M4qbSsnE1nv71Jcyd upRbU0Dx6mm+nBtO5Ncu+YsJFDCOpMAWVTb0heOEDw== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrfeehgdeglecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefofgggkfgjfhffhffvvefutgesrgdtreerreertdenucfhrhhomhepfdfguhhl vghrucfvrghvvghirhgrfdcuoegvuhhlvghrsegvuhhlvghrthhordgtohhmqeenucggtf frrghtthgvrhhnpefgffejvdeivdekvdeivdetudeikeduieevtdfhhfduvdetgeelteei ffekgefhvdenucffohhmrghinhepvghnthgvrhhprhhishgvuggsrdgtohhmnecuvehluh hsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomhepvghulhgvrhesvghu lhgvrhhtohdrtghomh X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id BC09A15A0092; Thu, 22 Feb 2024 21:46:04 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.11.0-alpha0-153-g7e3bb84806-fm-20240215.007-g7e3bb848 MIME-Version: 1.0 Message-Id: In-Reply-To: References: <9a9c7e65-c47b-4400-a63e-ac6169c0860e@app.fastmail.com> <8d8d9960-8130-4216-b0e4-adf36b8b1c43@app.fastmail.com> <370673e8-e2da-4249-8f07-cb67e9d9c20e@app.fastmail.com> <34075bd4-c85d-4654-a782-0cd6713b2c89@app.fastmail.com> <2edde173-5ff9-45fd-b881-cd04eb13049f@app.fastmail.com> <2231a04b-f2d4-4a4e-b5cd-56be8b002427@app.fastmail.com> Date: Thu, 22 Feb 2024 23:45:36 -0300 From: "Euler Taveira" To: "Shlok Kyal" , "kuroda.hayato@fujitsu.com" Cc: "pgsql-hackers@lists.postgresql.org" , "vignesh C" , "Michael Paquier" , "Peter Eisentraut" , "Andres Freund" , "Ashutosh Bapat" , "Amit Kapila" , =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= Subject: Re: speed up a logical replica setup Content-Type: multipart/alternative; boundary=54908fbf44634218a30208833e518eb9 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --54908fbf44634218a30208833e518eb9 Content-Type: text/plain On Wed, Feb 21, 2024, at 5:00 AM, Shlok Kyal wrote: > I found some issues and fixed those issues with top up patches > v23-0012 and v23-0013 > 1. > Suppose there is a cascade physical replication node1->node2->node3. > Now if we run pg_createsubscriber with node1 as primary and node2 as > standby, pg_createsubscriber will be successful but the connection > between node2 and node3 will not be retained and log og node3 will > give error: > 2024-02-20 12:32:12.340 IST [277664] FATAL: database system > identifier differs between the primary and standby > 2024-02-20 12:32:12.340 IST [277664] DETAIL: The primary's identifier > is 7337575856950914038, the standby's identifier is > 7337575783125171076. > 2024-02-20 12:32:12.341 IST [277491] LOG: waiting for WAL to become > available at 0/3000F10 > > To fix this I am avoiding pg_createsubscriber to run if the standby > node is primary to any other server. > Made the change in v23-0012 patch IIRC we already discussed the cascading replication scenario. Of course, breaking a node is not good that's why you proposed v23-0012. However, preventing pg_createsubscriber to run if there are standbys attached to it is also annoying. If you don't access to these hosts you need to (a) kill walsender (very fragile / unstable), (b) start with max_wal_senders = 0 or (3) add a firewall rule to prevent that these hosts do not establish a connection to the target server. I wouldn't like to include the patch as-is. IMO we need at least one message explaining the situation to the user, I mean, add a hint message. I'm resistant to a new option but probably a --force option is an answer. There is no test coverage for it. I adjusted this patch (didn't include the --force option) and add a test case. > 2. > While checking 'max_replication_slots' in 'check_publisher' function, > we are not considering the temporary slot in the check: > + if (max_repslots - cur_repslots < num_dbs) > + { > + pg_log_error("publisher requires %d replication slots, but > only %d remain", > + num_dbs, max_repslots - cur_repslots); > + pg_log_error_hint("Consider increasing max_replication_slots > to at least %d.", > + cur_repslots + num_dbs); > + return false; > + } > Fixed this in v23-0013 Good catch! Both are included in the next patch. -- Euler Taveira EDB https://www.enterprisedb.com/ --54908fbf44634218a30208833e518eb9 Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Wed, Feb 21,= 2024, at 5:00 AM, Shlok Kyal wrote:
I found some issues and fixed those issues wit= h top up patches
v23-0012 and v23-0013
1.
Suppose there is a cascade physical replication node1->no= de2->node3.
Now if we run pg_createsubscriber with node= 1 as primary and node2 as
standby, pg_createsubscriber wil= l be successful but the connection
between node2 and node3= will not be retained and log og node3 will
give error:
2024-02-20 12:32:12.340 IST [277664] FATAL:  database s= ystem
identifier differs between the primary and standby
2024-02-20 12:32:12.340 IST [277664] DETAIL:  The prim= ary's identifier
is 7337575856950914038, the standby's ide= ntifier is
7337575783125171076.
2024-02-20 1= 2:32:12.341 IST [277491] LOG:  waiting for WAL to become
<= div>available at 0/3000F10

To fix this I am= avoiding pg_createsubscriber to run if the standby
node i= s primary to any other server.
Made the change in v23-0012= patch

IIRC we already discuss= ed the cascading replication scenario. Of course,
breaking= a node is not good that's why you proposed v23-0012. However,
=
preventing pg_createsubscriber to run if there are standbys attache= d to it is
also annoying. If you don't access to these hos= ts you need to (a) kill
walsender (very fragile / unstable= ), (b) start with max_wal_senders =3D 0 or (3)
add a firew= all rule to prevent that these hosts do not establish a connection
to the target server. I wouldn't like to include the patch as-i= s. IMO we need
at least one message explaining the situati= on to the user, I mean, add a hint
message.  I'm resi= stant to a new option but probably a --force option is an
= answer. There is no test coverage for it. I adjusted this patch (didn't = include
the --force option) and add a test case.
=

2.
While checking 'max_replication_slots' in 'check_publisher' = function,
we are not considering the temporary slot in the= check:
+   if (max_repslots - cur_repslots <= num_dbs)
+   {
+   = ;    pg_log_error("publisher requires %d replication slot= s, but
only %d remain",
+   &= nbsp;           &= nbsp;    num_dbs, max_repslots - cur_repslots);
=
+       pg_log_error_hint("Consider i= ncreasing max_replication_slots
to at least %d.",
+           = ;            = ;  cur_repslots + num_dbs);
+    =    return false;
+   }
F= ixed this in v23-0013

Good cat= ch!

Both are included in the next patch.


--
Euler Taveira
EDB   https://www.enterprisedb.com/

=
--54908fbf44634218a30208833e518eb9--