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
