On Mon, Jan 22, 2024 at 10:30 PM shveta malik <
shveta.malik@gmail.com> wrote:
>
> On Mon, Jan 22, 2024 at 3:10 PM Amit Kapila <
amit.kapila16@gmail.com> wrote:
> >
> > minor comments on the patch:
> > =======================
>
> 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=true (as we do
> not support sync on cascading standbys)
>
> [1]:
https://www.postgresql.org/message-id/CAHut%2BPt5Pk_xJkb54oahR%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-zpGYTbVOP5Qp26tNQPMjDuYzNZ%2Ba9RFiN5nE1tEA%40mail.gmail.com> [4]:
https://www.postgresql.org/message-id/CAA4eK1Jhy1-bsu6vc0%3DNja7aw5-EK_%3D101pnnuM3ATqTA8%2B%3DSg%40mail.gmail.com> [5]:
https://www.postgresql.org/message-id/CAD21AoBgzONdt3o5mzbQ4MtqAE%3DWseiXUOq0LMqne-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=# \timing
Timing is on.
postgres=# CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres host=localhost port=6972' PUBLICATION pub with (failover = true);
NOTICE: created replication slot "sub" on publisher
CREATE SUBSCRIPTION
Time: 10011.829 ms (00:10.012)
== drop the sub
postgres=# CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres host=localhost port=6972' PUBLICATION pub with (failover = false);
NOTICE: created replication slot "sub" on publisher
CREATE SUBSCRIPTION
Time: 46.317 ms
With failover=true, it takes 10011 ms while failover=false 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=# SELECT 'init' FROM pg_create_logical_replication_slot('lsub2_slot', 'pgoutput', false, false, true);
?column?
----------
init
(1 row)
Time: 36.125 ms
postgres=# 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