Using failover slots for PG-non_PG logical replication

On Wed, Jul 2, 2025 at 10:50 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Hi All,

The failover slots documentation [1] is good for PG - PG logical
replication, but the first two queries require pg_subscription which
may not be present in non-PG downstream. Somebody looking to setup
failover slots for non-PG subscriber may not find the page useful.

Okay. It appears to me that the entire document at [1]https://www.postgresql.org/docs/current/logical-replication.html is
specifically intended for a built-in replication setup, and the
corresponding page was written with that context in mind.

However, the third query, when modified to mention the replication
slots relevant to the downstream is useful to them. How to find the
replication slots to be synchronized is a problem specific to the type
of downstream. Such a setup should add those slots to
sync_replication_slots. I think the chapter should mention that the
3rd query should also include the slots mentioned in
sync_replication_slots for PG-non_PG logical replication setup.

sync_replication_slots is a boolean which enables a physical standby
to synchronize logical failover slots. Did you mean something else?

We could include a general note in the documentation something like:
'For non-native replication, it is the responsibility of the plugin
author to define the required slots'. Then, we can directly use the
third query. Thoughts?

[1]: https://www.postgresql.org/docs/current/logical-replication.html

thanks
Shveta

On Wed, Jul 2, 2025 at 12:36 PM shveta malik <shveta.malik@gmail.com> wrote:

On Wed, Jul 2, 2025 at 10:50 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Hi All,

The failover slots documentation [1] is good for PG - PG logical
replication, but the first two queries require pg_subscription which
may not be present in non-PG downstream. Somebody looking to setup
failover slots for non-PG subscriber may not find the page useful.

Okay. It appears to me that the entire document at [1] is
specifically intended for a built-in replication setup, and the
corresponding page was written with that context in mind.

However, the third query, when modified to mention the replication
slots relevant to the downstream is useful to them. How to find the
replication slots to be synchronized is a problem specific to the type
of downstream. Such a setup should add those slots to
sync_replication_slots. I think the chapter should mention that the
3rd query should also include the slots mentioned in
sync_replication_slots for PG-non_PG logical replication setup.

sync_replication_slots is a boolean which enables a physical standby
to synchronize logical failover slots. Did you mean something else?

I confused this with the actual list of slots to be synchronized.
Sorry for that. The slots to be synchronized can be obtained from the
primary by querying pg_replication_slots with failover = true.

We could include a general note in the documentation something like:
'For non-native replication, it is the responsibility of the plugin
author to define the required slots'. Then, we can directly use the
third query. Thoughts?

Something like that would work. However, a non-PG subscriber may use
the built-in plugin for PG-non_PG, so it's more of a responsibility of
the downstream or user than the plugin. It may help further if we
mention that in such a case where pg_subscriber is not available, the
slots to be synchronized can be obtained from the primary by querying
pg_replication_slots with failover = true.

--
Best Wishes,
Ashutosh Bapat

On Wed, Jul 2, 2025 at 5:50 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Wed, Jul 2, 2025 at 12:36 PM shveta malik <shveta.malik@gmail.com> wrote:

On Wed, Jul 2, 2025 at 10:50 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Hi All,

The failover slots documentation [1] is good for PG - PG logical
replication, but the first two queries require pg_subscription which
may not be present in non-PG downstream. Somebody looking to setup
failover slots for non-PG subscriber may not find the page useful.

Okay. It appears to me that the entire document at [1] is
specifically intended for a built-in replication setup, and the
corresponding page was written with that context in mind.

However, the third query, when modified to mention the replication
slots relevant to the downstream is useful to them. How to find the
replication slots to be synchronized is a problem specific to the type
of downstream. Such a setup should add those slots to
sync_replication_slots. I think the chapter should mention that the
3rd query should also include the slots mentioned in
sync_replication_slots for PG-non_PG logical replication setup.

sync_replication_slots is a boolean which enables a physical standby
to synchronize logical failover slots. Did you mean something else?

I confused this with the actual list of slots to be synchronized.
Sorry for that. The slots to be synchronized can be obtained from the
primary by querying pg_replication_slots with failover = true.

Note that primary may have slots corresponding to multiple subscriber
nodes, so querying all slots on primary will give a correct answer may
depend on the use case. For example, say a user wants to do some sort
of load balancing such that some of the subscriber/downstream nodes
are served by a standby, then directly querying all slots from
pg_replication_slots from the primary won't give the correct answer.
In a typical failover case as well, if slots corresponding to a
particular downstream are ready, then that should be sufficient to
continue replication from the standby. Then, also, there is a case
when the primary node is down, then such a query won't work; it can
only work when there is a planned switchover. Considering all these
points, I am not sure if it is a good idea to mention querying the
primary for all slots marked with failover=true. However, I agree that
we should mention something for non-native logical replication
solutions, something on the lines of what Shveta is proposing. OTOH,
if you or Shveta have some clear guidelines for how a downstream can
find the required slots which can work in all or most cases, then it
is okay to mention that as well.

--
With Regards,
Amit Kapila.

On Thu, Jul 3, 2025 at 9:32 AM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Jul 2, 2025 at 5:50 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Wed, Jul 2, 2025 at 12:36 PM shveta malik <shveta.malik@gmail.com> wrote:

On Wed, Jul 2, 2025 at 10:50 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Hi All,

The failover slots documentation [1] is good for PG - PG logical
replication, but the first two queries require pg_subscription which
may not be present in non-PG downstream. Somebody looking to setup
failover slots for non-PG subscriber may not find the page useful.

Okay. It appears to me that the entire document at [1] is
specifically intended for a built-in replication setup, and the
corresponding page was written with that context in mind.

However, the third query, when modified to mention the replication
slots relevant to the downstream is useful to them. How to find the
replication slots to be synchronized is a problem specific to the type
of downstream. Such a setup should add those slots to
sync_replication_slots. I think the chapter should mention that the
3rd query should also include the slots mentioned in
sync_replication_slots for PG-non_PG logical replication setup.

sync_replication_slots is a boolean which enables a physical standby
to synchronize logical failover slots. Did you mean something else?

I confused this with the actual list of slots to be synchronized.
Sorry for that. The slots to be synchronized can be obtained from the
primary by querying pg_replication_slots with failover = true.

Note that primary may have slots corresponding to multiple subscriber
nodes, so querying all slots on primary will give a correct answer may
depend on the use case. For example, say a user wants to do some sort
of load balancing such that some of the subscriber/downstream nodes
are served by a standby, then directly querying all slots from
pg_replication_slots from the primary won't give the correct answer.
In a typical failover case as well, if slots corresponding to a
particular downstream are ready, then that should be sufficient to
continue replication from the standby. Then, also, there is a case
when the primary node is down, then such a query won't work; it can
only work when there is a planned switchover.

Agreed.

Considering all these
points, I am not sure if it is a good idea to mention querying the
primary for all slots marked with failover=true. However, I agree that
we should mention something for non-native logical replication
solutions, something on the lines of what Shveta is proposing. OTOH,
if you or Shveta have some clear guidelines for how a downstream can
find the required slots which can work in all or most cases, then it
is okay to mention that as well.

I do not have any better ideas here other than the one I proposed earlier.

thanks
Shveta

On Thu, Jul 3, 2025 at 9:32 AM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Jul 2, 2025 at 5:50 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Wed, Jul 2, 2025 at 12:36 PM shveta malik <shveta.malik@gmail.com> wrote:

On Wed, Jul 2, 2025 at 10:50 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Hi All,

The failover slots documentation [1] is good for PG - PG logical
replication, but the first two queries require pg_subscription which
may not be present in non-PG downstream. Somebody looking to setup
failover slots for non-PG subscriber may not find the page useful.

Okay. It appears to me that the entire document at [1] is
specifically intended for a built-in replication setup, and the
corresponding page was written with that context in mind.

However, the third query, when modified to mention the replication
slots relevant to the downstream is useful to them. How to find the
replication slots to be synchronized is a problem specific to the type
of downstream. Such a setup should add those slots to
sync_replication_slots. I think the chapter should mention that the
3rd query should also include the slots mentioned in
sync_replication_slots for PG-non_PG logical replication setup.

sync_replication_slots is a boolean which enables a physical standby
to synchronize logical failover slots. Did you mean something else?

I confused this with the actual list of slots to be synchronized.
Sorry for that. The slots to be synchronized can be obtained from the
primary by querying pg_replication_slots with failover = true.

Note that primary may have slots corresponding to multiple subscriber
nodes, so querying all slots on primary will give a correct answer may
depend on the use case. For example, say a user wants to do some sort
of load balancing such that some of the subscriber/downstream nodes
are served by a standby, then directly querying all slots from
pg_replication_slots from the primary won't give the correct answer.
In a typical failover case as well, if slots corresponding to a
particular downstream are ready, then that should be sufficient to
continue replication from the standby. Then, also, there is a case
when the primary node is down, then such a query won't work; it can
only work when there is a planned switchover.

I think there are two different points of views. Section 29.3 is
written from a single subscriber's point of view i.e. whether a given
subscriber can continue logical replication from the new primary after
failover? The other view is from primary's point of view i.e. if
primary fails over will all the subscribers be able to continue
replication? For example, in case of a planned failover, the failover
orchestrator can check whether all the replication slots have been
synchronized or not. If so then it goes ahead with the failover. I
think the section is the right place to guide in this case as well.

Considering all these
points, I am not sure if it is a good idea to mention querying the
primary for all slots marked with failover=true. However, I agree that
we should mention something for non-native logical replication
solutions, something on the lines of what Shveta is proposing. OTOH,
if you or Shveta have some clear guidelines for how a downstream can
find the required slots which can work in all or most cases, then it
is okay to mention that as well.

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?

--
Best Wishes,
Ashutosh Bapat

On Thu, Jul 3, 2025 at 7:07 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Thu, Jul 3, 2025 at 9:32 AM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Jul 2, 2025 at 5:50 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Wed, Jul 2, 2025 at 12:36 PM shveta malik <shveta.malik@gmail.com> wrote:

On Wed, Jul 2, 2025 at 10:50 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Hi All,

The failover slots documentation [1] is good for PG - PG logical
replication, but the first two queries require pg_subscription which
may not be present in non-PG downstream. Somebody looking to setup
failover slots for non-PG subscriber may not find the page useful.

Okay. It appears to me that the entire document at [1] is
specifically intended for a built-in replication setup, and the
corresponding page was written with that context in mind.

However, the third query, when modified to mention the replication
slots relevant to the downstream is useful to them. How to find the
replication slots to be synchronized is a problem specific to the type
of downstream. Such a setup should add those slots to
sync_replication_slots. I think the chapter should mention that the
3rd query should also include the slots mentioned in
sync_replication_slots for PG-non_PG logical replication setup.

sync_replication_slots is a boolean which enables a physical standby
to synchronize logical failover slots. Did you mean something else?

I confused this with the actual list of slots to be synchronized.
Sorry for that. The slots to be synchronized can be obtained from the
primary by querying pg_replication_slots with failover = true.

Note that primary may have slots corresponding to multiple subscriber
nodes, so querying all slots on primary will give a correct answer may
depend on the use case. For example, say a user wants to do some sort
of load balancing such that some of the subscriber/downstream nodes
are served by a standby, then directly querying all slots from
pg_replication_slots from the primary won't give the correct answer.
In a typical failover case as well, if slots corresponding to a
particular downstream are ready, then that should be sufficient to
continue replication from the standby. Then, also, there is a case
when the primary node is down, then such a query won't work; it can
only work when there is a planned switchover.

I think there are two different points of views. Section 29.3 is
written from a single subscriber's point of view i.e. whether a given
subscriber can continue logical replication from the new primary after
failover? The other view is from primary's point of view i.e. if
primary fails over will all the subscribers be able to continue
replication? For example, in case of a planned failover, the failover
orchestrator can check whether all the replication slots have been
synchronized or not. If so then it goes ahead with the failover. I
think the section is the right place to guide in this case as well.

Considering all these
points, I am not sure if it is a good idea to mention querying the
primary for all slots marked with failover=true. However, I agree that
we should mention something for non-native logical replication
solutions, something on the lines of what Shveta is proposing. OTOH,
if you or Shveta have some clear guidelines for how a downstream can
find the required slots which can work in all or most cases, then it
is okay to mention that as well.

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?

--
With Regards,
Amit Kapila.

On Fri, Jul 4, 2025 at 9:23 AM Amit Kapila <amit.kapila16@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

On Fri, Jul 4, 2025 at 7:42 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Fri, Jul 4, 2025 at 9:23 AM Amit Kapila <amit.kapila16@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.

Thanks for the patch.

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?

Yes, that’s correct. We sync the invalidation status of replication
slots from the primary to the standby and then stop synchronizing any
slots that have been marked as invalidated, retaining synced flag as
true. IMO, there's no need to filter out conflicting slots on the
primary, because instead of excluding them there and showing
everything as failover-ready on the standby, the correct approach is
to reflect the actual state on standby.This means conflicting slots
will appear as non-failover-ready on the standby. That’s why Step 3
also considers conflicting flag in its evaluation.

1)
+/* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots
+               FROM pg_replication_slots r
+               WHERE r.failover AND NOT r.temporary;

On primary, there is no need to check temporary-status. We do not
allow setting failover as true for temporary slots.

2)
Although not directly related to the concerns addressed in the given
patch, I think it would be helpful to add a note in the original doc
stating that Steps 1 and 2 should be executed on each subscriber node
that will be served by the standby after failover.

I have attached a top-up patch with the above changes and a few more
trivial changes. Please include it if you find it okay.

thanks
Shveta

On Mon, Jul 7, 2025 at 9:53 AM shveta malik <shveta.malik@gmail.com> wrote:

Thanks for the patch.

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?

Yes, that’s correct. We sync the invalidation status of replication
slots from the primary to the standby and then stop synchronizing any
slots that have been marked as invalidated, retaining synced flag as
true. IMO, there's no need to filter out conflicting slots on the
primary, because instead of excluding them there and showing
everything as failover-ready on the standby, the correct approach is
to reflect the actual state on standby.This means conflicting slots
will appear as non-failover-ready on the standby. That’s why Step 3
also considers conflicting flag in its evaluation.

Thanks for the explanation. WFM.

If a slot is invalidated because RS_INVAL_WAL_REMOVED, conflicting =
false, but the slot is not useful standby. But then it's not useful on
primary as well. Is that why we are not including (invalidation_reason
IS NOT NULL) condition along in (synced AND NOT temporary AND NOT
conflicting)?

1)
+/* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots
+               FROM pg_replication_slots r
+               WHERE r.failover AND NOT r.temporary;

On primary, there is no need to check temporary-status. We do not
allow setting failover as true for temporary slots.

Why does synchronize_slots() has it in its query?

2)
Although not directly related to the concerns addressed in the given
patch, I think it would be helpful to add a note in the original doc
stating that Steps 1 and 2 should be executed on each subscriber node
that will be served by the standby after failover.

There's a bit of semantic repeatition here since an earlier paragraph
mentions a given subscriber. But I think overall it's better to be
more clear than being less clear.

I have attached a top-up patch with the above changes and a few more
trivial changes. Please include it if you find it okay.

Thanks. Included. I have made a few edits and included them in the
attached patch.

--
Best Wishes,
Ashutosh Bapat

On Mon, Jul 7, 2025 at 7:00 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Mon, Jul 7, 2025 at 9:53 AM shveta malik <shveta.malik@gmail.com> wrote:

Thanks for the patch.

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?

Yes, that’s correct. We sync the invalidation status of replication
slots from the primary to the standby and then stop synchronizing any
slots that have been marked as invalidated, retaining synced flag as
true. IMO, there's no need to filter out conflicting slots on the
primary, because instead of excluding them there and showing
everything as failover-ready on the standby, the correct approach is
to reflect the actual state on standby.This means conflicting slots
will appear as non-failover-ready on the standby. That’s why Step 3
also considers conflicting flag in its evaluation.

Thanks for the explanation. WFM.

If a slot is invalidated because RS_INVAL_WAL_REMOVED, conflicting =
false, but the slot is not useful standby. But then it's not useful on
primary as well. Is that why we are not including (invalidation_reason
IS NOT NULL) condition along in (synced AND NOT temporary AND NOT
conflicting)?

Thanks for bringing it up. Even if the slot is not useful on the
primary node as well, we shall still show failover-ready as false on
standby. We should modify the query of step3 to check
'invalidation_reason IS NULL' instead of 'NOT conflicting'. That will
cover all the cases where the slot is invalidated and thus not
failover ready.

1)
+/* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots
+               FROM pg_replication_slots r
+               WHERE r.failover AND NOT r.temporary;

On primary, there is no need to check temporary-status. We do not
allow setting failover as true for temporary slots.

Why does synchronize_slots() has it in its query?

It is not needed but no harm in maintaining it.
If we want documents to be in sync with code, we can have 'not
temporary' check in doc as well.

2)
Although not directly related to the concerns addressed in the given
patch, I think it would be helpful to add a note in the original doc
stating that Steps 1 and 2 should be executed on each subscriber node
that will be served by the standby after failover.

There's a bit of semantic repeatition here since an earlier paragraph
mentions a given subscriber. But I think overall it's better to be
more clear than being less clear.

I have attached a top-up patch with the above changes and a few more
trivial changes. Please include it if you find it okay.

Thanks. Included. I have made a few edits and included them in the
attached patch.

Thanks. The existing changes (originally targeted in this patch) look
good to me.

I have attached a top-up patch for step-3 correction. Please include
if you find it okay to be fixed in the same patch, otherwise we can
handle it separately.

thanks
Shveta

On Tue, Jul 8, 2025 at 4:03 PM shveta malik <shveta.malik@gmail.com> wrote:

On Mon, Jul 7, 2025 at 7:00 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Mon, Jul 7, 2025 at 9:53 AM shveta malik <shveta.malik@gmail.com> wrote:

Thanks for the patch.

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?

Yes, that’s correct. We sync the invalidation status of replication
slots from the primary to the standby and then stop synchronizing any
slots that have been marked as invalidated, retaining synced flag as
true. IMO, there's no need to filter out conflicting slots on the
primary, because instead of excluding them there and showing
everything as failover-ready on the standby, the correct approach is
to reflect the actual state on standby.This means conflicting slots
will appear as non-failover-ready on the standby. That’s why Step 3
also considers conflicting flag in its evaluation.

Thanks for the explanation. WFM.

If a slot is invalidated because RS_INVAL_WAL_REMOVED, conflicting =
false, but the slot is not useful standby. But then it's not useful on
primary as well. Is that why we are not including (invalidation_reason
IS NOT NULL) condition along in (synced AND NOT temporary AND NOT
conflicting)?

Thanks for bringing it up. Even if the slot is not useful on the
primary node as well, we shall still show failover-ready as false on
standby. We should modify the query of step3 to check
'invalidation_reason IS NULL' instead of 'NOT conflicting'. That will
cover all the cases where the slot is invalidated and thus not
failover ready.

Thanks for the confirmation.

1)
+/* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots
+               FROM pg_replication_slots r
+               WHERE r.failover AND NOT r.temporary;

On primary, there is no need to check temporary-status. We do not
allow setting failover as true for temporary slots.

Why does synchronize_slots() has it in its query?

It is not needed but no harm in maintaining it.
If we want documents to be in sync with code, we can have 'not
temporary' check in doc as well.

I think it's better to keep the code and the doc in sync otherwise we
developers would get confused.

2)
Although not directly related to the concerns addressed in the given
patch, I think it would be helpful to add a note in the original doc
stating that Steps 1 and 2 should be executed on each subscriber node
that will be served by the standby after failover.

There's a bit of semantic repeatition here since an earlier paragraph
mentions a given subscriber. But I think overall it's better to be
more clear than being less clear.

I have attached a top-up patch with the above changes and a few more
trivial changes. Please include it if you find it okay.

Thanks. Included. I have made a few edits and included them in the
attached patch.

Thanks. The existing changes (originally targeted in this patch) look
good to me.

I have attached a top-up patch for step-3 correction. Please include
if you find it okay to be fixed in the same patch, otherwise we can
handle it separately.

I have split your top up patch into 2 - one related to the document
change being the subject of this thread and the other for fixing the
query. Committer may squash the patch, if they think so.

--
Best Wishes,
Ashutosh Bapat

On Tue, Jul 8, 2025 at 7:29 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Tue, Jul 8, 2025 at 4:03 PM shveta malik <shveta.malik@gmail.com> wrote:

On Mon, Jul 7, 2025 at 7:00 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Mon, Jul 7, 2025 at 9:53 AM shveta malik <shveta.malik@gmail.com> wrote:

Thanks for the patch.

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?

Yes, that’s correct. We sync the invalidation status of replication
slots from the primary to the standby and then stop synchronizing any
slots that have been marked as invalidated, retaining synced flag as
true. IMO, there's no need to filter out conflicting slots on the
primary, because instead of excluding them there and showing
everything as failover-ready on the standby, the correct approach is
to reflect the actual state on standby.This means conflicting slots
will appear as non-failover-ready on the standby. That’s why Step 3
also considers conflicting flag in its evaluation.

Thanks for the explanation. WFM.

If a slot is invalidated because RS_INVAL_WAL_REMOVED, conflicting =
false, but the slot is not useful standby. But then it's not useful on
primary as well. Is that why we are not including (invalidation_reason
IS NOT NULL) condition along in (synced AND NOT temporary AND NOT
conflicting)?

Thanks for bringing it up. Even if the slot is not useful on the
primary node as well, we shall still show failover-ready as false on
standby. We should modify the query of step3 to check
'invalidation_reason IS NULL' instead of 'NOT conflicting'. That will
cover all the cases where the slot is invalidated and thus not
failover ready.

Thanks for the confirmation.

1)
+/* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots
+               FROM pg_replication_slots r
+               WHERE r.failover AND NOT r.temporary;

On primary, there is no need to check temporary-status. We do not
allow setting failover as true for temporary slots.

Why does synchronize_slots() has it in its query?

It is not needed but no harm in maintaining it.
If we want documents to be in sync with code, we can have 'not
temporary' check in doc as well.

I think it's better to keep the code and the doc in sync otherwise we
developers would get confused.

2)
Although not directly related to the concerns addressed in the given
patch, I think it would be helpful to add a note in the original doc
stating that Steps 1 and 2 should be executed on each subscriber node
that will be served by the standby after failover.

There's a bit of semantic repeatition here since an earlier paragraph
mentions a given subscriber. But I think overall it's better to be
more clear than being less clear.

I have attached a top-up patch with the above changes and a few more
trivial changes. Please include it if you find it okay.

Thanks. Included. I have made a few edits and included them in the
attached patch.

Thanks. The existing changes (originally targeted in this patch) look
good to me.

I have attached a top-up patch for step-3 correction. Please include
if you find it okay to be fixed in the same patch, otherwise we can
handle it separately.

I have split your top up patch into 2 - one related to the document
change being the subject of this thread and the other for fixing the
query. Committer may squash the patch, if they think so.

The changes look good to me.

thanks
Shveta

On Wed, Jul 9, 2025 at 8:30 AM shveta malik <shveta.malik@gmail.com> wrote:

I have split your top up patch into 2 - one related to the document
change being the subject of this thread and the other for fixing the
query. Committer may squash the patch, if they think so.

The changes look good to me.

Thanks.

Looks like Amit has already committed it. I had created a CF entry
https://commitfest.postgresql.org/patch/5904/ to track this. I will
mark it as committed now.

Amit,
While reviewing the patches again, I felt that the second sentence in
that section also needs a bit of clarification. Here's patch with that
change. Please feel free to reject it or apply it.

--
Best Wishes,
Ashutosh Bapat

On Wed, Jul 9, 2025 at 6:50 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Wed, Jul 9, 2025 at 8:30 AM shveta malik <shveta.malik@gmail.com> wrote:

I have split your top up patch into 2 - one related to the document
change being the subject of this thread and the other for fixing the
query. Committer may squash the patch, if they think so.

The changes look good to me.

Thanks.

Looks like Amit has already committed it. I had created a CF entry
https://commitfest.postgresql.org/patch/5904/ to track this. I will
mark it as committed now.

Thanks.

Amit,
While reviewing the patches again, I felt that the second sentence in
that section also needs a bit of clarification. Here's patch with that
change. Please feel free to reject it or apply it.

The additional part: ""+ or when creating replication slots
directly" you mentioned could be considered to be added. But I see
that is already explained in the link mentioned in the doc, see [1]https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS-SYNCHRONIZATION.
So, I suggest we leave this part of docs as it is.

[1]: https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS-SYNCHRONIZATION

--
With Regards,
Amit Kapila.

On Thu, Jul 10, 2025 at 8:49 AM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Jul 9, 2025 at 6:50 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

On Wed, Jul 9, 2025 at 8:30 AM shveta malik <shveta.malik@gmail.com> wrote:

I have split your top up patch into 2 - one related to the document
change being the subject of this thread and the other for fixing the
query. Committer may squash the patch, if they think so.

The changes look good to me.

Thanks.

Looks like Amit has already committed it. I had created a CF entry
https://commitfest.postgresql.org/patch/5904/ to track this. I will
mark it as committed now.

Thanks.

Amit,
While reviewing the patches again, I felt that the second sentence in
that section also needs a bit of clarification. Here's patch with that
change. Please feel free to reject it or apply it.

The additional part: ""+ or when creating replication slots
directly" you mentioned could be considered to be added. But I see
that is already explained in the link mentioned in the doc, see [1].
So, I suggest we leave this part of docs as it is.

[1]: https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS-SYNCHRONIZATION

WFM.

--
Best Wishes,
Ashutosh Bapat