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). 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@2ndquadrant.com> wrote:
Show quoted text
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). 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
Thank you Peter, this is wildly helpful.
On Thu, Jan 9, 2020 at 7:52 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
Show quoted text
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