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

That's a great point, thanks. The DROP SUBSCRIPTION notes say you can:

> Disassociate the subscription from the replication slot by executing ALTER SUBSCRIPTION ... SET (slot_name = NONE). After that, DROP SUBSCRIPTION will no longer attempt any actions on a remote host.

I'll read some more about the replication slots themselves (I did read
about them a while back), but doing the above seems like a good way to
break B from A, before resubscribing C to A instead?

I feel like this is getting warmer.

Thanks for the reply. I really appreciate it.

Mike

On Wed, Jan 8, 2020 at 2:46 PM Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>
> 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