v2-0001-Correction-in-doc-of-failover-ready-steps.patch

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