Re: How to shorten a chain of logically replicated servers

> You'd have to suspend all data modification on A in that interval.

I know how to stop the DB completely, but I can't think of any obvious
ways to make sure that it doesn't get any data modification for a
period of time. Is there a trick here? This is feeling a bit hopeless.

Thanks for the response, Laurenz.

On Tue, Jan 7, 2020 at 3:11 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Tue, 2019-12-31 at 15:51 -0800, Mike Lissner wrote:
> > Hi, I'm trying to figure out how to shorten a chain of logically
> > replicating servers. Right now we have three servers replicating like
> > so:
> >
> > A --> B --> C
> >
> > And I'd like to remove B from the chain of replication so that I only have:
> >
> > A --> C
> >
> > Of course, doing this without losing data is the goal. If the
> > replication to C breaks temporarily, that's fine, so long as all the
> > changes on A make it to C eventually.
> >
> > I'm not sure how to proceed with this. My best theory is:
> >
> > 1. In a transaction, DISABLE the replication from A to B and start a
> > new PUBLICATION on A that C will subscribe to in step ③ below. The
> > hope is that this will simultaneously stop sending changes to B while
> > starting a log of new changes that can later be sent to C.
> >
> > 2. Let any changes queued on B flush to C. (How to know when they're
> > all flushed?)
> >
> > 3. Subscribe C to the new PUBLICATION created in step ①. Create the
> > subscription with copy_data=False. This should send all changes to C
> > that hadn't been sent to B, without sending the complete tables.
> >
> > 4. DROP all replication to/from B (this is just cleanup; the incoming
> > changes to B were disabled in step ①, and outgoing changes from B were
> > flushed in step ②).
> >
> > Does this sound even close to the right approach? Logical replication
> > can be a bit finicky, so I'd love to have some validation of the
> > general approach before I go down this road.
>
> I don't think that will work.
>
> Any changes on A that take place between step 1 and step 3 wouldn't be
> replicated to C.
>
> You'd have to suspend all data modification on A in that interval.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>