Re: Is it safe to transfer logical replication publication/subscription? - Mailing list pgsql-general

From Mike Lissner
Subject Re: Is it safe to transfer logical replication publication/subscription?
Date
Msg-id CAMp9=EyG09uSn-D268F3Dusz8AkAxYaVuM66_kUZudXU8Wj2GA@mail.gmail.com
Whole thread Raw
In response to Re: Is it safe to transfer logical replicationpublication/subscription?  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
Responses Re: Is it safe to transfer logical replicationpublication/subscription?
List pgsql-general
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).
Afterthat, 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.eisentraut@2ndquadrant.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



pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Is it safe to transfer logical replicationpublication/subscription?
Next
From: github kran
Date:
Subject: Re: Upgrade PostgreSQL 9.6 to 10.6