From: shveta malik <shveta.malik@gmail.com>
To: Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Cc: shveta malik <shveta.malik@gmail.com>
Subject: Re: Correction in doc of failover ready steps
Date: Wed, 24 Jul 2024 09:40:49 +0530
Message-ID: <CAJpy0uDiaZWp_p5WEavgu0QgKxdMBT4nbCEYMYcO2sYdcDqxEg@mail.gmail.com> (raw)
In-Reply-To: <OS0PR01MB57165C9AEDDCEFD44B0BB1CB94AA2@OS0PR01MB5716.jpnprd01.prod.outlook.com>
References: <CAJpy0uBBk+OZXXqQ00Gai09XR+mDi2=9sMBYY0F+BedoFivaMA@mail.gmail.com>
<CAJpy0uAKG3moYA0pwQoQ2sv1d-S0CmfXsZf_9C_83UqtREaj+w@mail.gmail.com>
<CAA4eK1+voWz3b1zL=RP4AcsDuLfRv7N=Rom2od5uD-geKEQDGA@mail.gmail.com>
<OS0PR01MB5716A706053B6EEA2B4EC20B94A92@OS0PR01MB5716.jpnprd01.prod.outlook.com>
<CAJpy0uArGF6S4if6Aet82s5NEnfcPSKVBHGcxGx-m5Q+XHso6w@mail.gmail.com>
<CAA4eK1JahE4YRb1ixyKdi7uc-v1cbO3iJVWNbAOkzBSdbDXVwA@mail.gmail.com>
<CAJpy0uAeROTfqdoGWvZ9zqH4kRX5LECEGP1q-cTdmV9sYmWG6w@mail.gmail.com>
<OS0PR01MB57165C9AEDDCEFD44B0BB1CB94AA2@OS0PR01MB5716.jpnprd01.prod.outlook.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:
[application/octet-stream] v4-0001-Correction-in-doc-of-failover-ready-steps.patch (4.7K, ../CAJpy0uDiaZWp_p5WEavgu0QgKxdMBT4nbCEYMYcO2sYdcDqxEg@mail.gmail.com/2-v4-0001-Correction-in-doc-of-failover-ready-steps.patch)
download | inline diff:
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.sgmlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: shveta.malik@gmail.com, houzj.fnst@fujitsu.com, amit.kapila16@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Correction in doc of failover ready steps
In-Reply-To: <CAJpy0uDiaZWp_p5WEavgu0QgKxdMBT4nbCEYMYcO2sYdcDqxEg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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