Re: Reversing pg_logical replication

On 21/09/2018 17:21, Craig James wrote:
> I dug around on the web for comments and tools about how to reverse the
> master/slave relationship of a pg_logical publisher/subscriber pair
> without having to start fresh and re-copy all the data. All I found were
> several discussions that seemed to say, "You can't do it. You have to
> promote the subscriber to publisher, then blow off the original
> publisher's tables, make it a subscriber, and start from scratch."
>
> But the create-subscription SQL has a "with (copy_data = false)", which
> seems to do exactly what I need. So I tried it, and it works fine.

This works as long as both nodes are in sync and no one is writing to
either of them while you do these changes.

The hard part is doing it while both nodes are active.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services