Обсуждение: Is it safe to transfer logical replication publication/subscription?
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? E.g., assuming the following: - "A" has a PUBLICATION named "A-to-B-Pub" that "B" subscribes to. - "C" subscribes to "B" with a subscription named "B-to-C-Sub". Would this work? 1. On B, DROP the subscription to "A-to-B-Pub". 2. Let any cached changes on B flush to C. Give it an hour to be sure. 3. On C ALTER "B-to-C-Sub" to subscribe to the now-used "A-to-B-Pub" on A. Seems like this would either work perfectly or totally fail. Any ideas? Thanks for any help, Mike
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
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
On 2020-01-08 23:55, Mike Lissner wrote: > 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? Yes, that's the one you want. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Thank you Peter, this is wildly helpful. On Thu, Jan 9, 2020 at 7:52 AM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > > On 2020-01-08 23:55, Mike Lissner wrote: > > 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? > > Yes, that's the one you want. > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services