Doc: Fix the mistakes in the subscription's failover option.

The documentation incorrectly stated that users could not alter the
subscription's failover option when the two-phase commit is enabled.

The steps to confirm that the standby server is ready for failover were
incorrect.

Author: Shveta Malik, Hou Zhijie
Reviewed-by: Amit Kapila
Discussion: https://postgr.es/m/OS0PR01MB571657B72F8D75BD858DCCE394AD2@OS0PR01MB5716.jpnprd01.prod.outlook.com
Discussion: https://postgr.es/m/CAJpy0uBBk+OZXXqQ00Gai09XR+mDi2=9sMBYY0F+BedoFivaMA@mail.gmail.com

2 files changed, 44 insertions, 24 deletions

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index ccdd24312bd..a23a3d57e2b 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 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>
diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index 6af6d0d2c8d..fdc648d007f 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -72,8 +72,13 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
    <command>ALTER SUBSCRIPTION ... SET (failover = true|false)</command> and
    <command>ALTER SUBSCRIPTION ... SET (two_phase = false)</command>
    cannot be executed inside a transaction block.
+  </para>
 
-   These commands also cannot be executed when the subscription has
+  <para>
+   Commands <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command> and
+   <command>ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</command>
+   with <literal>refresh</literal> option as <literal>true</literal> also cannot
+   be executed when the subscription has
    <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
    commit enabled, unless
    <link linkend="sql-createsubscription-params-with-copy-data"><literal>copy_data</literal></link>