Thread: Correction in doc of failover ready steps
Hi, We have a query in failover-ready doc referring to pg_subscription_rel. Unlike pg_subscription, pg_subscription_rel gives results only when connected to the database having the subscription(s). If we run the concerned query on any other database, it will give incomplete results i.e. it will give info on main slots leaving table sync slots (if any). Thus the failover-ready steps which queries pg_subscription_rel need to mention that the concerned query needs to be run on the database(s) that includes the failover enabled subscription(s). Corrected the doc for the same. thanks Shveta
Attachment
On Mon, Jul 22, 2024 at 10:46 AM shveta malik <shveta.malik@gmail.com> wrote: > > Hi, > > We have a query in failover-ready doc referring to > pg_subscription_rel. Unlike pg_subscription, pg_subscription_rel gives > results only when connected to the database having the > subscription(s). If we run the concerned query on any other database, > it will give incomplete results i.e. it will give info on main slots > leaving table sync slots (if any). > Thus the failover-ready steps which queries pg_subscription_rel need > to mention that the concerned query needs to be run on the database(s) > that includes the failover enabled subscription(s). Corrected the doc > for the same. On rethinking, since pg_subscription query needs to be run only once on *any* database to get combined results of all main slots while pg_subscription_rel query needs to be run on each database having concerned subscription (and table), does it makes sense to separate the 2 queries instead of having UNION ? Thoughts? thanks Shveta
On Mon, Jul 22, 2024 at 10:59 AM shveta malik <shveta.malik@gmail.com> wrote: > > On Mon, Jul 22, 2024 at 10:46 AM shveta malik <shveta.malik@gmail.com> wrote: > > > > Hi, > > > > We have a query in failover-ready doc referring to > > pg_subscription_rel. Unlike pg_subscription, pg_subscription_rel gives > > results only when connected to the database having the > > subscription(s). If we run the concerned query on any other database, > > it will give incomplete results i.e. it will give info on main slots > > leaving table sync slots (if any). > > Thus the failover-ready steps which queries pg_subscription_rel need > > to mention that the concerned query needs to be run on the database(s) > > that includes the failover enabled subscription(s). Corrected the doc > > for the same. > > On rethinking, since pg_subscription query needs to be run only once > on *any* database to get combined results of all main slots while > pg_subscription_rel query needs to be run on each database having > concerned subscription (and table), does it makes sense to separate > the 2 queries instead of having UNION ? Thoughts? > I think so. Let's see if Hou-San or anyone else has better ideas to fetch this information. -- With Regards, Amit Kapila.
On Monday, July 22, 2024 7:13 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > On Mon, Jul 22, 2024 at 10:59 AM shveta malik <shveta.malik@gmail.com> > wrote: > > > > On Mon, Jul 22, 2024 at 10:46 AM shveta malik <shveta.malik@gmail.com> > wrote: > > > > > > Hi, > > > > > > We have a query in failover-ready doc referring to > > > pg_subscription_rel. Unlike pg_subscription, pg_subscription_rel > > > gives results only when connected to the database having the > > > subscription(s). If we run the concerned query on any other > > > database, it will give incomplete results i.e. it will give info on > > > main slots leaving table sync slots (if any). > > > Thus the failover-ready steps which queries pg_subscription_rel need > > > to mention that the concerned query needs to be run on the > > > database(s) that includes the failover enabled subscription(s). > > > Corrected the doc for the same. > > > > On rethinking, since pg_subscription query needs to be run only once > > on *any* database to get combined results of all main slots while > > pg_subscription_rel query needs to be run on each database having > > concerned subscription (and table), does it makes sense to separate > > the 2 queries instead of having UNION ? Thoughts? > > > > I think so. Let's see if Hou-San or anyone else has better ideas to fetch this > information. I also agree that separating the 2 queries makes sense. Best Regards, Hou zj
On Tue, Jul 23, 2024 at 8:14 AM Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com> wrote: > > I also agree that separating the 2 queries makes sense. Please find the patch with the suggested change. thanks Shveta
Attachment
On Tue, Jul 23, 2024 at 9:40 AM shveta malik <shveta.malik@gmail.com> wrote: > > On Tue, Jul 23, 2024 at 8:14 AM Zhijie Hou (Fujitsu) > <houzj.fnst@fujitsu.com> wrote: > > > > I also agree that separating the 2 queries makes sense. > > Please find the patch with the suggested change. > One minor comment: - if the table copy is finished (See <xref linkend="catalog-pg-subscription-rel"/>). + On the subscriber node, use the following SQL to identify which main + slots should be synced to the standby that we plan to promote. This query Shall we refer to these slots as replication slots instead of main slots in the above sentence? We don't have a main slot terminology at other places, so it would be better not to introduce a new one. I know that it was introduced in the original commit but it seems better to change if we agree. -- With Regards, Amit Kapila.
On Tue, Jul 23, 2024 at 5:10 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > One minor comment: > - if the table copy is finished (See <xref > linkend="catalog-pg-subscription-rel"/>). > + On the subscriber node, use the following SQL to identify which main > + slots should be synced to the standby that we plan to promote. This query > > Shall we refer to these slots as replication slots instead of main > slots in the above sentence? We don't have a main slot terminology at > other places, so it would be better not to introduce a new one. I know > that it was introduced in the original commit but it seems better to > change if we agree. Yes, it makes sense. Please find the patch with this change. thanks SHveta
Attachment
On Wednesday, July 24, 2024 10:56 AM shveta malik <shveta.malik@gmail.com> wrote: > > On Tue, Jul 23, 2024 at 5:10 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > > > One minor comment: > > - if the table copy is finished (See <xref > > linkend="catalog-pg-subscription-rel"/>). > > + On the subscriber node, use the following SQL to identify which main > > + slots should be synced to the standby that we plan to promote. > > + This query > > > > Shall we refer to these slots as replication slots instead of main > > slots in the above sentence? We don't have a main slot terminology at > > other places, so it would be better not to introduce a new one. I know > > that it was introduced in the original commit but it seems better to > > change if we agree. > > Yes, it makes sense. Please find the patch with this change. Thanks for the patch. Here is one comment: The second query has a condition 'WHERE slot_name IS NOT NULL', but I think it belongs to the first query. Because the slot_name of second query is built by CONCAT() which means it should be valid, while the first query's subslotname could be NULL if user executed ALTER SUB SET (slot_name = NONE). Apart from above, it looks good to me. Best Regards, Hou zj
On Wed, Jul 24, 2024 at 9:02 AM Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com> wrote: > > Thanks for the patch. > > Here is one comment: > > The second query has a condition 'WHERE slot_name IS NOT NULL', but I > think it belongs to the first query. Because the slot_name of second query > is built by CONCAT() which means it should be valid, while the first query's > subslotname could be NULL if user executed ALTER SUB SET (slot_name = NONE). > Thanks for pointing it out. Please find a new patch with this change. Also corrected the commit message in this patch. thanks Shveta