| From: | Mike Lissner <mlissner(at)michaeljaylissner(dot)com> |
|---|---|
| To: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | How to shorten a chain of logically replicated servers |
| Date: | 2019-12-31 23:51:25 |
| Message-ID: | CAMp9=EzU5cBXSwaH=Somx6QTuH_pkN=pgYjSzZdVMs_+RnAveA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
Thanks everybody and happy new year,
Mike
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Matthias Apitz | 2020-01-01 17:39:36 | Re: How to reset a server error '25P02 in_failed_sql_transaction' |
| Previous Message | Ron | 2019-12-31 18:08:32 | Re: How to reset a server error '25P02 in_failed_sql_transaction' |