0001-Logical-replication-failover-document-20250704.patch

From 165eb64e3b3ee3e0bbe4f769baa9de44aac15506 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Fri, 4 Jul 2025 19:35:47 +0530
Subject: [PATCH] Logical replication failover document

Clarify that the existing steps in logical replication failover section are
meant for a given subscriber. Also clarify that the first two steps are for a
PostgreSQL subscriber. Add a separate paragraph to specify the query to be used
to make sure that all the replication slots have been synchronized to the
required standby in case of a planner failover.

Ashutosh Bapat
---
 doc/src/sgml/logical-replication.sgml | 33 +++++++++++++++++++++++++--
 1 file changed, 31 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index c32e6bc000d..bdfa9d7afef 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -709,8 +709,8 @@ HINT:  To initiate replication, you must manually create the replication slot, e
   </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
+   To confirm that the standby server is indeed ready for failover for a given subscriber, follow these
+   steps to verify that all the logical replication slots required by that subscriber have been
    synchronized to the standby server:
   </para>
 
@@ -786,6 +786,35 @@ HINT:  To initiate replication, you must manually create the replication slot, e
    new primary server.
   </para>
 
+  <para>
+   The first two steps in the above procedure are meant for a
+   <productname>PostgreSQL</productname> subscriber. A
+   non-<productname>PostgreSQL</productname> subscriber may have its own method
+   of identifying the slots used by its subscriptions.
+  </para>
+
+  <para>
+   Sometimes it is required to confirm that all the subscribers,
+   <productname>PostgreSQL</productname> or
+   non-<productname>PostgreSQL</productname>, will be able to continue
+   replication after failing over to a given standby server, for example, in
+   case of a planned failover. In such a case use the following SQL, instead of
+   the first two steps above, to identify which replication slots on the primary
+   should be synced to the standby that we plan to promote. This query will
+   return the relevant replication slots associated with all the
+   failover-enabled subscriptions.
+  </para>
+
+   <para>
+<programlisting>
+/* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots
+               FROM pg_replication_slots r
+               WHERE r.failover AND NOT r.temporary;
+ slots
+-------
+ {'sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164'}
+(1 row)
+</programlisting></para>
  </sect1>
 
  <sect1 id="logical-replication-row-filter">

base-commit: 5a6c39b6df3313e5c2d3aed714a56f5a5c6be3f2
-- 
2.34.1