Re: speed up a logical replica setup - Mailing list pgsql-hackers

From Shlok Kyal
Subject Re: speed up a logical replica setup
Date
Msg-id CANhcyEXJefHDeXk5AUveqmX=ty2BaDYhhSxFF8udZUpwON95LA@mail.gmail.com
Whole thread Raw
In response to RE: speed up a logical replica setup  ("Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>)
Responses Re: speed up a logical replica setup
List pgsql-hackers
> And here are top-up patches. Feel free to check and include.
>
> v22-0001: Same as v21-0001.
> === rebased patches ===
> v22-0002: Update docs per recent changes. Same as v20-0002.
> v22-0003: Add check versions of the target. Extracted from v20-0003.
> v22-0004: Remove -S option. Mostly same as v20-0009, but commit massage was
>           slightly changed.
> === Newbie ===
> V22-0005: Addressed my comments which seems to be trivial[1].
>           Comments #1, 3, 4, 8, 10, 14, 17 were addressed here.
> v22-0006: Consider the scenario when commands are failed after the recovery.
>           drop_subscription() is removed and some messages are added per [2].
> V22-0007: Revise server_is_in_recovery() per [1]. Comments #5, 6, 7, were addressed here.
> V22-0008: Fix a strange report when physical_primary_slot is null. Per comment #9 [1].
> V22-0009: Prohibit reuse publications when it has already existed. Per comments #11 and 12 [1].
> V22-0010: Avoid to call PQclear()/PQfinish()/pg_free() if the process exits soon. Per comment #15 [1].
> V22-0011: Update testcode. Per comments #17- [1].

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

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

v23-0001 to v23-0011 is same as v22-0001 to v22-0011

Thanks and Regards,
Shlok Kyal

Attachment

pgsql-hackers by date:

Previous
From: Andrei Lepikhov
Date:
Subject: Re: Removing unneeded self joins
Next
From: jian he
Date:
Subject: Re: Catalog domain not-null constraints