Re: How to shorten a chain of logically replicated servers

From: Mike Lissner <mlissner(at)michaeljaylissner(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: How to shorten a chain of logically replicated servers
Date: 2020-01-08 07:17:43
Message-ID: CAMp9=EyqDRkn8-D3h_yjcQ5HR-HNyphb+FV2jfS8JaV6Gd_Xcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> 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
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message İlyas Derse 2020-01-08 07:23:36 Re: Writing Postgres Extensions in C on Windows
Previous Message Daulat Ram 2020-01-08 07:15:33 Setting up an environment of EDB Advance server