Re: Notes on physical replica failover with logical publisher or subscriber

On Mon, Nov 30, 2020 at 9:30 AM Craig Ringer
<craig(dot)ringer(at)enterprisedb(dot)com> wrote:
>
> Hi all
>
> I recently wrote some notes on interaction between physical
> replication failover/promotion and logical replication publisher
> and/or standby.
>
> As you probably all know, right now we don't support physical failover
> for logical replication publishers at all, either for in-core logical
> replication or for 3rd party solutions. And while we support physical
> failover and promotion of subscribers, there turn out to be some
> issues with that too.
>
> I'm not trying to solve those issues right now. But since there are
> various out-of-tree replication tools trying to work around these
> limitations, I wanted to share my knowledge of the various hazards and
> challenges involved in doing so, so I've written a wiki article on it.
>
> https://wiki.postgresql.org/wiki/Logical_replication_and_physical_standby_failover
>
> I tried to address many of these issues with failover slots, but I am
> not trying to beat that dead horse now. I know that at least some
> people here are of the opinion that effort shouldn't go into
> logical/physical replication interoperation anyway - that we should
> instead address the remaining limitations in logical replication so
> that it can provide complete HA capabilities without use of physical
> replication.
>

What is the advantage of using logical replication for HA over
physical replication? It sends more WAL in most cases and probably
requires more set up in terms of the publisher-subscriber. I see the
major use as upgrades can be easier because it can provide
cross-version replication and others as mentioned in docs [1]. I can
also imagine that one might want to use it for scale-out by carefully
having transactions specific to different master nodes or probably by
having a third-party transaction coordinator.

> So for now I'm just trying to save others who go looking
> into these issues some time and warn them about some of the less
> obvious booby-traps.
>
> I do want to add some info to the logical decoding docs around slot
> fast-forward behaviour and how to write clients to avoid missing or
> double-processing transactions.
>

+1.

> I'd welcome advice on the best way to
> do that in a manner that would be accepted by this community.
>

Why not enhance test_decoding apart from mentioning in docs to explain
such concepts? It might help in some code-coverage as well depending
on what we want to cover.

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

--
With Regards,
Amit Kapila.