pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedCorrection in doc of failover ready steps
9+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2024-07-24 07fbecb87b 20aaa634f7 Doc: Fix the mistakes in the subscription's failover option.
* Correction in doc of failover ready steps
@ 2024-07-22 05:16 shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: shveta malik @ 2024-07-22 05:16 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>; Amit Kapila <amit.kapila16@gmail.com>
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
Attachments:
v1-0001-Correction-in-doc-of-failover-ready-steps.patch (application/octet-stream, 1.8K) download
From e9e28662c1b82c6723e660c2802bcfdc0547a28f Mon Sep 17 00:00:00 2001
From: Shveta Malik <shveta.malik@gmail.com>
Date: Mon, 22 Jul 2024 10:10:34 +0530
Subject: [PATCH v1] Correction in doc of failover ready steps.
Unlike pg_subscription, pg_subscription_rel gives results only
when connected to the database having the subscription(s). 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).
---
doc/src/sgml/logical-replication.sgml | 8 +++++---
1 file changed, 5 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 1c37f63e12..542f0e771c 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -726,9 +726,11 @@ ALTER SUBSCRIPTION
<step performance="required">
<para>
On the subscriber node, use the following SQL to identify which slots
- should be synced to the standby that we plan to promote. This query will
- return the relevant replication slots, including the main slots and table
- synchronization slots associated with the failover-enabled subscriptions.
+ should be synced to the standby that we plan to promote. This query
+ needs to be run on the database(s) that includes the failover
+ enabled subscription(s). This query will return the relevant replication
+ slots, including the main slots and table synchronization slots associated
+ with the failover-enabled subscriptions.
Note that the table sync slot should be synced to the standby server only
if the table copy is finished (See <xref linkend="catalog-pg-subscription-rel"/>).
We don't need to ensure that the table sync slots are synced in other scenarios
--
2.34.1
* Re: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
@ 2024-07-22 05:29 ` shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: shveta malik @ 2024-07-22 05:29 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>; Amit Kapila <amit.kapila16@gmail.com>; shveta malik <shveta.malik@gmail.com>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
@ 2024-07-22 11:12 ` Amit Kapila <amit.kapila16@gmail.com>
2024-07-23 02:44 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
0 siblings, 1 reply; 9+ messages in thread
From: Amit Kapila @ 2024-07-22 11:12 UTC (permalink / raw)
To: shveta malik <shveta.malik@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
@ 2024-07-23 02:44 ` Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-23 04:10 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Zhijie Hou (Fujitsu) @ 2024-07-23 02:44 UTC (permalink / raw)
To: Amit Kapila <amit.kapila16@gmail.com>; shveta malik <shveta.malik@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
2024-07-23 02:44 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
@ 2024-07-23 04:10 ` shveta malik <shveta.malik@gmail.com>
2024-07-23 11:40 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: shveta malik @ 2024-07-23 04:10 UTC (permalink / raw)
To: Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>; +Cc: Amit Kapila <amit.kapila16@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>; shveta malik <shveta.malik@gmail.com>
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
Attachments:
v2-0001-Correction-in-doc-of-failover-ready-steps.patch (application/octet-stream, 4.4K) download
From 1d8e466aa41a2e225549eacf9a9c2af7cb377443 Mon Sep 17 00:00:00 2001
From: Shveta Malik <shveta.malik@gmail.com>
Date: Mon, 22 Jul 2024 10:10:34 +0530
Subject: [PATCH v2] Correction in doc of failover ready steps.
Unlike pg_subscription, pg_subscription_rel gives results only
when connected to the database having the subscription(s). 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).
---
doc/src/sgml/logical-replication.sgml | 59 +++++++++++++++++----------
1 file changed, 37 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 1c37f63e12..29cdfe4373 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -725,32 +725,45 @@ ALTER SUBSCRIPTION
<procedure>
<step performance="required">
<para>
- On the subscriber node, use the following SQL to identify which slots
- should be synced to the standby that we plan to promote. This query will
- return the relevant replication slots, including the main slots and table
- synchronization slots associated with the failover-enabled subscriptions.
- Note that the table sync slot should be synced to the standby server only
- 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
+ will return the relevant replication slots associated with the
+ failover-enabled subscriptions.
+<programlisting>
+test_sub=# SELECT
+ array_agg(quote_literal(s.subslotname)) AS slots
+ FROM pg_subscription s
+ WHERE s.subfailover;
+ slots
+-------
+ {'sub1','sub2','sub3'}
+(1 row)
+</programlisting></para>
+ </step>
+ <step performance="required">
+ <para>
+ On the subscriber node, use the following SQL to identify which table
+ synchronization slots should be synced to the standby that we plan to promote.
+ This specific query needs to be run on each database that includes the failover
+ enabled subscription(s). Note that the table sync slot should be synced to the
+ standby server only if the table copy is finished
+ (See <xref linkend="catalog-pg-subscription-rel"/>).
We don't need to ensure that the table sync slots are synced in other scenarios
as they will either be dropped or re-created on the new primary server in those
cases.
<programlisting>
test_sub=# SELECT
- array_agg(slot_name) AS slots
+ array_agg(quote_literal(slot_name)) AS slots
FROM
- ((
- SELECT r.srsubid AS subid, CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name
+ (
+ SELECT CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name
FROM pg_control_system() ctl, pg_subscription_rel r, pg_subscription s
WHERE r.srsubstate = 'f' AND s.oid = r.srsubid AND s.subfailover
- ) UNION (
- SELECT s.oid AS subid, s.subslotname as slot_name
- FROM pg_subscription s
- WHERE s.subfailover
- ))
+ )
WHERE slot_name IS NOT NULL;
slots
-------
- {sub1,sub2,sub3}
+ {'pg_16394_sync_16385_7394666715149055164'}
(1 row)
</programlisting></para>
</step>
@@ -761,13 +774,15 @@ test_sub=# SELECT
<programlisting>
test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready
FROM pg_replication_slots
- WHERE slot_name IN ('sub1','sub2','sub3');
- slot_name | failover_ready
--------------+----------------
- sub1 | t
- sub2 | t
- sub3 | t
-(3 rows)
+ WHERE slot_name IN
+ ('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164');
+ slot_name | failover_ready
+--------------------------------------------+----------------
+ sub1 | t
+ sub2 | t
+ sub3 | t
+ pg_16394_sync_16385_7394666715149055164 | t
+(4 rows)
</programlisting></para>
</step>
</procedure>
--
2.34.1
* Re: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
2024-07-23 02:44 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-23 04:10 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
@ 2024-07-23 11:40 ` Amit Kapila <amit.kapila16@gmail.com>
2024-07-24 02:56 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Amit Kapila @ 2024-07-23 11:40 UTC (permalink / raw)
To: shveta malik <shveta.malik@gmail.com>; +Cc: Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
2024-07-23 02:44 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-23 04:10 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-23 11:40 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
@ 2024-07-24 02:56 ` shveta malik <shveta.malik@gmail.com>
2024-07-24 03:32 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
0 siblings, 1 reply; 9+ messages in thread
From: shveta malik @ 2024-07-24 02:56 UTC (permalink / raw)
To: Amit Kapila <amit.kapila16@gmail.com>; +Cc: Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>; shveta malik <shveta.malik@gmail.com>
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
Attachments:
v3-0001-Correction-in-doc-of-failover-ready-steps.patch (application/octet-stream, 4.4K) download
From 295b3afa8edd50cc7300a649aed622f1e7132410 Mon Sep 17 00:00:00 2001
From: Shveta Malik <shveta.malik@gmail.com>
Date: Mon, 22 Jul 2024 10:10:34 +0530
Subject: [PATCH v3] Correction in doc of failover ready steps.
Unlike pg_subscription, pg_subscription_rel gives results only
when connected to the database having the subscription(s). 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).
---
doc/src/sgml/logical-replication.sgml | 59 +++++++++++++++++----------
1 file changed, 37 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 1c37f63e12..9c1d7d58a8 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -725,32 +725,45 @@ ALTER SUBSCRIPTION
<procedure>
<step performance="required">
<para>
- On the subscriber node, use the following SQL to identify which slots
- should be synced to the standby that we plan to promote. This query will
- return the relevant replication slots, including the main slots and table
- synchronization slots associated with the failover-enabled subscriptions.
- Note that the table sync slot should be synced to the standby server only
- if the table copy is finished (See <xref linkend="catalog-pg-subscription-rel"/>).
+ On the subscriber node, use the following SQL to identify which replication
+ slots should be synced to the standby that we plan to promote. This query
+ will return the relevant replication slots associated with the
+ failover-enabled subscriptions.
+<programlisting>
+test_sub=# SELECT
+ array_agg(quote_literal(s.subslotname)) AS slots
+ FROM pg_subscription s
+ WHERE s.subfailover;
+ slots
+-------
+ {'sub1','sub2','sub3'}
+(1 row)
+</programlisting></para>
+ </step>
+ <step performance="required">
+ <para>
+ On the subscriber node, use the following SQL to identify which table
+ synchronization slots should be synced to the standby that we plan to promote.
+ This specific query needs to be run on each database that includes the failover
+ enabled subscription(s). Note that the table sync slot should be synced to the
+ standby server only if the table copy is finished
+ (See <xref linkend="catalog-pg-subscription-rel"/>).
We don't need to ensure that the table sync slots are synced in other scenarios
as they will either be dropped or re-created on the new primary server in those
cases.
<programlisting>
test_sub=# SELECT
- array_agg(slot_name) AS slots
+ array_agg(quote_literal(slot_name)) AS slots
FROM
- ((
- SELECT r.srsubid AS subid, CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name
+ (
+ SELECT CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name
FROM pg_control_system() ctl, pg_subscription_rel r, pg_subscription s
WHERE r.srsubstate = 'f' AND s.oid = r.srsubid AND s.subfailover
- ) UNION (
- SELECT s.oid AS subid, s.subslotname as slot_name
- FROM pg_subscription s
- WHERE s.subfailover
- ))
+ )
WHERE slot_name IS NOT NULL;
slots
-------
- {sub1,sub2,sub3}
+ {'pg_16394_sync_16385_7394666715149055164'}
(1 row)
</programlisting></para>
</step>
@@ -761,13 +774,15 @@ test_sub=# SELECT
<programlisting>
test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready
FROM pg_replication_slots
- WHERE slot_name IN ('sub1','sub2','sub3');
- slot_name | failover_ready
--------------+----------------
- sub1 | t
- sub2 | t
- sub3 | t
-(3 rows)
+ WHERE slot_name IN
+ ('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164');
+ slot_name | failover_ready
+--------------------------------------------+----------------
+ sub1 | t
+ sub2 | t
+ sub3 | t
+ pg_16394_sync_16385_7394666715149055164 | t
+(4 rows)
</programlisting></para>
</step>
</procedure>
--
2.34.1
* RE: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
2024-07-23 02:44 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-23 04:10 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-23 11:40 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
2024-07-24 02:56 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
@ 2024-07-24 03:32 ` Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-24 04:10 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Zhijie Hou (Fujitsu) @ 2024-07-24 03:32 UTC (permalink / raw)
To: shveta malik <shveta.malik@gmail.com>; Amit Kapila <amit.kapila16@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Correction in doc of failover ready steps
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
2024-07-23 02:44 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-23 04:10 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-23 11:40 ` Re: Correction in doc of failover ready steps Amit Kapila <amit.kapila16@gmail.com>
2024-07-24 02:56 ` Re: Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-24 03:32 ` RE: Correction in doc of failover ready steps Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
@ 2024-07-24 04:10 ` shveta malik <shveta.malik@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: shveta malik @ 2024-07-24 04:10 UTC (permalink / raw)
To: Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>; +Cc: Amit Kapila <amit.kapila16@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>; shveta malik <shveta.malik@gmail.com>
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
Attachments:
v4-0001-Correction-in-doc-of-failover-ready-steps.patch (application/octet-stream, 4.7K) download
From 2e0de27e6eebaf52b6bf7d773e2539312553c54d Mon Sep 17 00:00:00 2001
From: Shveta Malik <shveta.malik@gmail.com>
Date: Mon, 22 Jul 2024 10:10:34 +0530
Subject: [PATCH v4] Correction in doc of failover ready steps.
Unlike pg_subscription, pg_subscription_rel provides results only when
connected to the database that contains the subscription(s). Therefore,
the query for failover-ready steps, which is a UNION of two queries on
pg_subscription and pg_subscription_rel, needs to be split into two
separate queries. The query involving pg_subscription should be
executed once per subscriber node, while the query involving
pg_subscription_rel should be run once per database that includes
the failover-enabled subscription(s). This patch corrects the
documentation accordingly.
---
doc/src/sgml/logical-replication.sgml | 61 +++++++++++++++++----------
1 file changed, 38 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index f078d6364b..fbddddd0e3 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -725,32 +725,45 @@ ALTER SUBSCRIPTION
<procedure>
<step performance="required">
<para>
- On the subscriber node, use the following SQL to identify which slots
- should be synced to the standby that we plan to promote. This query will
- return the relevant replication slots, including the main slots and table
- synchronization slots associated with the failover-enabled subscriptions.
- Note that the table sync slot should be synced to the standby server only
- if the table copy is finished (See <xref linkend="catalog-pg-subscription-rel"/>).
+ On the subscriber node, use the following SQL to identify which replication
+ slots should be synced to the standby that we plan to promote. This query
+ will return the relevant replication slots associated with the
+ failover-enabled subscriptions.
+<programlisting>
+test_sub=# SELECT
+ array_agg(quote_literal(s.subslotname)) AS slots
+ FROM pg_subscription s
+ WHERE s.subfailover AND
+ s.subslotname IS NOT NULL;
+ slots
+-------
+ {'sub1','sub2','sub3'}
+(1 row)
+</programlisting></para>
+ </step>
+ <step performance="required">
+ <para>
+ On the subscriber node, use the following SQL to identify which table
+ synchronization slots should be synced to the standby that we plan to promote.
+ This specific query needs to be run on each database that includes the failover
+ enabled subscription(s). Note that the table sync slot should be synced to the
+ standby server only if the table copy is finished
+ (See <xref linkend="catalog-pg-subscription-rel"/>).
We don't need to ensure that the table sync slots are synced in other scenarios
as they will either be dropped or re-created on the new primary server in those
cases.
<programlisting>
test_sub=# SELECT
- array_agg(slot_name) AS slots
+ array_agg(quote_literal(slot_name)) AS slots
FROM
- ((
- SELECT r.srsubid AS subid, CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name
+ (
+ SELECT CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name
FROM pg_control_system() ctl, pg_subscription_rel r, pg_subscription s
WHERE r.srsubstate = 'f' AND s.oid = r.srsubid AND s.subfailover
- ) UNION (
- SELECT s.oid AS subid, s.subslotname as slot_name
- FROM pg_subscription s
- WHERE s.subfailover
- ))
- WHERE slot_name IS NOT NULL;
+ );
slots
-------
- {sub1,sub2,sub3}
+ {'pg_16394_sync_16385_7394666715149055164'}
(1 row)
</programlisting></para>
</step>
@@ -761,13 +774,15 @@ test_sub=# SELECT
<programlisting>
test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready
FROM pg_replication_slots
- WHERE slot_name IN ('sub1','sub2','sub3');
- slot_name | failover_ready
--------------+----------------
- sub1 | t
- sub2 | t
- sub3 | t
-(3 rows)
+ WHERE slot_name IN
+ ('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164');
+ slot_name | failover_ready
+--------------------------------------------+----------------
+ sub1 | t
+ sub2 | t
+ sub3 | t
+ pg_16394_sync_16385_7394666715149055164 | t
+(4 rows)
</programlisting></para>
</step>
</procedure>
--
2.34.1
end of thread, other threads:[~2024-07-24 04:10 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-22 05:16 Correction in doc of failover ready steps shveta malik <shveta.malik@gmail.com>
2024-07-22 05:29 ` shveta malik <shveta.malik@gmail.com>
2024-07-22 11:12 ` Amit Kapila <amit.kapila16@gmail.com>
2024-07-23 02:44 ` Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-23 04:10 ` shveta malik <shveta.malik@gmail.com>
2024-07-23 11:40 ` Amit Kapila <amit.kapila16@gmail.com>
2024-07-24 02:56 ` shveta malik <shveta.malik@gmail.com>
2024-07-24 03:32 ` Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
2024-07-24 04:10 ` shveta malik <shveta.malik@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox