v5-0001-Document-the-steps-to-check-if-the-standby-is-rea.patch

From 2c5f6083415ec87a1f17a61c4481c1a32364bba4 Mon Sep 17 00:00:00 2001
From: Shveta Malik <shveta.malik@gmail.com>
Date: Fri, 19 Jan 2024 11:04:16 +0530
Subject: [PATCH v5] Document the steps to check if the standby is ready for
 failover

This patch adds detailed documentation for the slot sync feature.
It includes examples to guide the user:

* How to verify that all slots have been successfully synchronized to
the standby server

* How to confirm whether the subscription can continue subscribing to
publications on the promoted standby server
---
 doc/src/sgml/high-availability.sgml   |  9 +++
 doc/src/sgml/logical-replication.sgml | 86 +++++++++++++++++++++++++++
 2 files changed, 95 insertions(+)

diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index b48209fc2f..acf3ac0601 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1487,6 +1487,15 @@ synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
     Written administration procedures are advised.
    </para>
 
+   <para>
+    If you have opted for logical replication slot synchronization (see
+    <xref linkend="logicaldecoding-replication-slots-synchronization"/>),
+    then before switching to the standby server, it is recommended to check
+    if the logical slots synchronized on the standby server are ready
+    for failover. This can be done by following the steps described in
+    <xref linkend="logical-replication-failover"/>.
+   </para>
+
    <para>
     To trigger failover of a log-shipping standby server, run
     <command>pg_ctl promote</command> or call <function>pg_promote()</function>.
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index ec2130669e..1676f00f10 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -687,6 +687,92 @@ ALTER SUBSCRIPTION
 
  </sect1>
 
+ <sect1 id="logical-replication-failover">
+  <title>Logical Replication Failover</title>
+
+  <para>
+   When publications are defined on the primary server of a streaming
+   replication, the logical slots on that primary server can be synchronized to
+   the standby server by specifying <literal>failover = true</literal> when
+   creating subscriptions for those publications. Enabling the
+   <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
+   parameter ensures a seamless transition of those subscriptions after the
+   standby is promoted. They can continue subscribing to publications now on the
+   new primary server without any loss of data. Note that in the case of
+   asynchronous replication, there remains a risk of data loss for transactions
+   that have been committed on the former primary server but have yet to be
+   replicated to the new primary server.
+  </para>
+
+  <para>
+   Because the slot synchronization logic copies asynchronously, it is
+   necessary to confirm that replication slots have been synced to the standby
+   server before the failover happens. Furthermore, to ensure a successful
+   failover, the standby server must not be lagging behind the subscriber. It
+   is highly recommended to use
+   <link linkend="guc-standby-slot-names"><varname>standby_slot_names</varname></link>
+   to prevent the subscriber from consuming changes faster than the hot standby.
+  </para>
+
+  <para>
+   To confirm that the standby server is indeed ready for failover, follow these
+   steps to verify that all necessary logical replication slots have been
+   synchronized to the standby server:
+  </para>
+
+  <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.
+<programlisting>
+test_sub=# SELECT
+               array_agg(slot_name) AS slots
+           FROM
+           ((
+               SELECT r.srsubid AS subid, 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
+           ));
+ slots
+-------
+ {sub1,sub2,sub3}
+(1 row)
+</programlisting></para>
+   </step>
+   <step performance="required">
+    <para>
+     Check that the logical replication slots identified above exist on
+     the standby server and are ready for failover.
+<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)
+</programlisting></para>
+    </step>
+  </procedure>
+
+  <para>
+   If all the slots are present on the standby server and result
+   (<literal>failover_ready</literal>) of is true, then existing subscriptions
+   can continue subscribing to publications now on the new primary server
+   without any loss of data.
+  </para>
+
+ </sect1>
+
  <sect1 id="logical-replication-row-filter">
   <title>Row Filters</title>
 
-- 
2.30.0.windows.2