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 1rSClO-00C3rf-9x for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Jan 2024 09:08:10 +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 1rSClM-00BGPy-RL for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Jan 2024 09:08:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSClM-00BGPq-GC for pgsql-hackers@lists.postgresql.org; Tue, 23 Jan 2024 09:08:08 +0000 Received: from mail-yb1-xb30.google.com ([2607:f8b0:4864:20::b30]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rSClJ-003Gzl-NX for pgsql-hackers@postgresql.org; Tue, 23 Jan 2024 09:08:07 +0000 Received: by mail-yb1-xb30.google.com with SMTP id 3f1490d57ef6-dc21ad8266fso3139904276.1 for ; Tue, 23 Jan 2024 01:08:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1706000883; x=1706605683; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=EhrP21KPKmAyKgkBqRuxHxKHss8yWuhIYBVKAZ4rK0s=; b=S6F71Ust6Xcw0wmhSFJQ2F0j39Pp82rl9hUATsY++wP3BQofOg5fsoegKHh6tma25Q z80OPeHugNVroOEAKHZqwjmtE/pqhm2jLKkI+1SSiJ3oG1fob4tRtBkKKGLYQfTg3Mru L/Bdk49Y5OR8VD840lE13GA3DTX/TaOpFa++j4VyhYeEskHCTRv8PamZCpi/GorWD9xd WlMsnWwNikH6xnAbH6g52WoGsREpcKm8opg4hsomO+DXRdSE8rKk8PWI9Sgmw7BRc+KE iZjqF1CwZuKRhQ1aJibiiyVbTNUpadPG2MIjhL+myu+CpP8UYGngJN2fNHGAkWzFW/EF d9ZQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1706000883; x=1706605683; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=EhrP21KPKmAyKgkBqRuxHxKHss8yWuhIYBVKAZ4rK0s=; b=iT7tnTDkPdmOzfAhFQd5Kt/3ax/SjJ+uh2g825efj58GB74QOZZsPIKLC3cv5L31vN UcVla2tvzXGJnVKyv+yc+Gq/lxj/OBjU+rPl2tsK7wXjJWzkjaDsS7QbpzWPuqOFkGz7 Ir2Df1ou6lLUmOMVDhLT+hYAsWEskI1fmbqR+1SHmNHKDUtgVv5xDyZiF+w1fxQirRDd JyocXotPNSyHK/nD04sHJ1a+GhzOG5GAvFUygoG4gRgqYnTxhFTMqHHiv+KLNgE0vej+ h0nGwqlnxRYMt1ago+RYDCh5RPCbY1x31+fAZB4U1fTWpe42McEfzK5+AyXO4WVrwx6L ooig== X-Gm-Message-State: AOJu0Yy3nmZxhjUl749JHIzWW1K7eRhVHUZVCyIU/0zd7Q8nzgVysAus ljOe1OPY41cZnYwcs0qXe7veyPGaYVS25m0x1tsL7lcLa0pzev68nAQ8yG5Jy/uuZo2xxbwzjkC RnkEUuDNXyNYIHI9SkA1k8KCOkLY= X-Google-Smtp-Source: AGHT+IEy7huew55Bg4pab57zp/1Fdz8ThLRzrqzS0/s00F8F3Pp/Avsgriw5NevXBQDDIyeMexksliSbjFk0MZKdXA8= X-Received: by 2002:a25:dc51:0:b0:dbd:a9b6:b01e with SMTP id y78-20020a25dc51000000b00dbda9b6b01emr3288278ybe.36.1706000883442; Tue, 23 Jan 2024 01:08:03 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ajin Cherian Date: Tue, 23 Jan 2024 20:07:50 +1100 Message-ID: Subject: Re: Synchronizing slots from primary to standby To: shveta malik Cc: Amit Kapila , Bertrand Drouvot , Masahiko Sawada , "Zhijie Hou (Fujitsu)" , Dilip Kumar , Peter Smith , Nisha Moond , "Hayato Kuroda (Fujitsu)" , Bharath Rupireddy , Peter Eisentraut , Bruce Momjian , Ashutosh Sharma , Andres Freund , pgsql-hackers , Alvaro Herrera Content-Type: multipart/alternative; boundary="000000000000a4e921060f994810" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a4e921060f994810 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jan 22, 2024 at 10:30=E2=80=AFPM shveta malik wrote: > > On Mon, Jan 22, 2024 at 3:10=E2=80=AFPM Amit Kapila wrote: > > > > minor comments on the patch: > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > PFA v65 addressing the comments. > > Addressed comments by Peter in [1], comments by Hou-San in [2], > comments by Amit in [3] and [4] > > TODO: > Analyze the issue reported by Swada-san in [5] (pt 2) > Disallow subscription creation on standby with failover=3Dtrue (as we do > not support sync on cascading standbys) > > [1]: https://www.postgresql.org/message-id/CAHut%2BPt5Pk_xJkb54oahR%2Bf9oawgfnmb= pewvkZPgnRhoJ3gkYg%40mail.gmail.com > [2]: https://www.postgresql.org/message-id/OS0PR01MB57160C7184E17C6765AAE3829475= 2%40OS0PR01MB5716.jpnprd01.prod.outlook.com > [3]: https://www.postgresql.org/message-id/CAA4eK1JPB-zpGYTbVOP5Qp26tNQPMjDuYzNZ= %2Ba9RFiN5nE1tEA%40mail.gmail.com > [4]: https://www.postgresql.org/message-id/CAA4eK1Jhy1-bsu6vc0%3DNja7aw5-EK_%3D1= 01pnnuM3ATqTA8%2B%3DSg%40mail.gmail.com > [5]: https://www.postgresql.org/message-id/CAD21AoBgzONdt3o5mzbQ4MtqAE%3DWseiXUO= q0LMqne-nWGjZBsA%40mail.gmail.com > > I was doing some testing on this. What I noticed is that creating subscriptions with failover enabled is taking a lot longer compared with a subscription with failover disabled. The setup has primary configured with standby_slot_names and that standby is enabled with enable_synclot turned on. Publisher has one publication, no tables. subscriber: postgres=3D# \timing Timing is on. postgres=3D# CREATE SUBSCRIPTION sub CONNECTION 'dbname=3Dpostgres host=3Dlocalhost port=3D6972' PUBLICATION pub with (failover =3D true); NOTICE: created replication slot "sub" on publisher CREATE SUBSCRIPTION Time: 10011.829 ms (00:10.012) =3D=3D drop the sub postgres=3D# CREATE SUBSCRIPTION sub CONNECTION 'dbname=3Dpostgres host=3Dlocalhost port=3D6972' PUBLICATION pub with (failover =3D false); NOTICE: created replication slot "sub" on publisher CREATE SUBSCRIPTION Time: 46.317 ms With failover=3Dtrue, it takes 10011 ms while failover=3Dfalse takes 46 ms. I don't see a similar delay when creating slot on the primary with pg_create_logical_replication_slot() with failover flag enabled. Then on primary: postgres=3D# SELECT 'init' FROM pg_create_logical_replication_slot('lsub2_slot', 'pgoutput', false, false, true); ?column? ---------- init (1 row) Time: 36.125 ms postgres=3D# SELECT 'init' FROM pg_create_logical_replication_slot('lsub1_slot', 'pgoutput', false, false, false); ?column? ---------- init (1 row) Time: 53.981 ms regards, Ajin Cherian Fujitsu Australia --000000000000a4e921060f994810 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Mon, Jan 22, 2024 at 10:30=E2=80=AFPM sh= veta malik <shveta.malik@gmail= .com> wrote:
>
> On Mon, Jan 22, 2024 at 3:10=E2=80=AFPM= Amit Kapila <amit.kapila16@g= mail.com> wrote:
> >
> > minor comments on the pat= ch:
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
>
> PFA v65 addressing the comments.
>
&g= t; Addressed comments by Peter in [1], comments by Hou-San in [2],
> = comments by Amit in [3] and [4]
>
> TODO:
> Analyze the i= ssue reported by Swada-san in [5] (pt 2)
> Disallow subscription crea= tion on standby with failover=3Dtrue (as we do
> not support sync on = cascading standbys)
>
> [1]: https://www.postgresql.org/message-id/CAHut%2BPt5Pk_xJkb54oah= R%2Bf9oawgfnmbpewvkZPgnRhoJ3gkYg%40mail.gmail.com
> [2]: https://www.postgresql.org= /message-id/OS0PR01MB57160C7184E17C6765AAE38294752%40OS0PR01MB5716.jpnprd01= .prod.outlook.com
> [3]: https://www.postgresql.org/message-id/CAA4eK1JPB-zpGYTbVOP5Qp26tNQPMj= DuYzNZ%2Ba9RFiN5nE1tEA%40mail.gmail.com
> [4]: https://www.postgresql.org/message-id/CAA= 4eK1Jhy1-bsu6vc0%3DNja7aw5-EK_%3D101pnnuM3ATqTA8%2B%3DSg%40mail.gmail.com
> [5]: https://www.p= ostgresql.org/message-id/CAD21AoBgzONdt3o5mzbQ4MtqAE%3DWseiXUOq0LMqne-nWGjZ= BsA%40mail.gmail.com
>
>
I was doing some testing on this. What I noticed is that creating subscript= ions with failover enabled is taking a lot longer compared with a subscript= ion with failover disabled. The setup has primary configured with standby_s= lot_names and that standby is enabled with enable_synclot turned on.
Publisher has one publication, no tables.
subscriber:
postgres=3D# = \timing
Timing is on.
postgres=3D# CREATE SUBSCRIPTION sub CON= NECTION 'dbname=3Dpostgres host=3Dlocalhost port=3D6972' PUBLICATIO= N pub with (failover =3D true);
NOTICE: =C2=A0created replication slot &= quot;sub" on publisher
CREATE SUBSCRIPTION
Time: 10011.829 ms (0= 0:10.012)

=3D=3D drop the sub

postgres=3D# CREATE SUBSCRIPTIO= N sub CONNECTION 'dbname=3Dpostgres host=3Dlocalhost port=3D6972' P= UBLICATION pub with (failover =3D false);
NOTICE: =C2=A0created replicat= ion slot "sub" on publisher
CREATE SUBSCRIPTION
Time: 46.31= 7 ms

With failover=3Dtrue, it takes 10011 ms while failover=3Dfalse = takes 46 ms.

I don't see a similar delay when creating slot on t= he primary with pg_create_logical_replication_slot() with failover flag ena= bled.

Then on primary:
postgres=3D# SELECT 'init' FROM pg= _create_logical_replication_slot('lsub2_slot', 'pgoutput', = false, false, true);
?column?
----------
init
(1 row)
=C2= =A0
Time: 36.125 ms
postgres=3D# SELECT 'init' FROM pg_create= _logical_replication_slot('lsub1_slot', 'pgoutput', false, = false, false);
?column?
----------
init
(1 row)
=C2=A0
T= ime: 53.981 ms

regards,
Ajin Cherian
=
Fujitsu Australia
--000000000000a4e921060f994810--