Re: Is it safe to transfer logical replication publication/subscription?

On 2020-01-08 22:22, Mike Lissner wrote:
> Hi all, this is a follow up from an earlier question I asked about
> shortening a chain of logically replicating servers. Right now we have
> three servers replicating like this:
>
> A --> B --> C
>
> And we want to remove B so that we have:
>
> A --> C
>
> Is it possible to DROP the subscription on B to A and then to
> SUBSCRIBE C to the previously used publication on A without losing
> data?

What you are not taking into account here are replication slots, which
are the low-level mechanism to keep track of what a replication client
has consumed. When you drop the subscription on B, that (by default)
also drops the associated replication slot on A, and therefore you lose
the information of how much B has consumed from A. (This assumes that
there is concurrent write activity on A, otherwise this is uninteresting.)

What you need to do instead is disassociate the B-from-A subscription
from the replication slot on A, then let all changes from B trickle to
C, then change the C-from-B subscription to replicate from A and use the
existing replication slot on A.

See
https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT
for details.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services