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? 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@xxxxxxxxxxxxxxx> 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
