On Fri, Jul 4, 2025 at 9:23 AM Amit Kapila <amit.kapil...@gmail.com> wrote:
> > >
> >
> > How about this:
> > We change the following sentence in the third paragraph
> > To confirm that the standby server is indeed ready for failover <new
> > addition> so that a given PostgreSQL subscriber can continue logical
> > replication </new addition>, follow ... . <new addition> A
> > non-PostgreSQL downstream may need to device a different way to find
> > the slots corresponding to its subscriptions or use the next section.
> >
> > Then add a separate paragraph at the end or a separate section like below.
> >
> > In order to check whether a standby server is ready for failover so
> > that all the subscribers, PostgreSQL as well as non-PostgreSQL, can
> > continue logical replication, follow these steps make sure that all
> > the replication slots, on the primary server, that have property
> > failover = true are synchronized to the standby server.
> > 1. On the primary server run following query
> > select slot_name from pg_replication_slots where failover and NOT temporary
> >
> > 2. Check that the logical replication slots identified above exist on
> > the standby server and are ready for failover.
> > SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS
> > failover_ready
> > FROM pg_replication_slots
> > WHERE slot_name IN
> >
> > Does that look good?
> >
>
> Yes, something on these lines sounds like an improvement. Would you
> like to propose a patch or want Shveta or me to do the same?
How about something like attached.
I couldn't figure out whether the query on primary to fetch all the
slots to be synchronized should filter based on invalidation_reason
and conflicting or not. According to synchronize_slots(), it seems
that we retain invalidated slots on standby when failover = true and
they would remain with synced = true on standby. Is that right?
--
Best Wishes,
Ashutosh Bapat
From 165eb64e3b3ee3e0bbe4f769baa9de44aac15506 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat....@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
