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. For a publish/subscript from servers A --> B, the procedure is:
- Shut off activity
- delete subscription from B
- delete publication from A
- create publication on B
- create subscription on A with copy_data = false.
It seems simple. It works. Have I overlooked something? For all the hand-wringing I found when others asked about this, I'm worried that I overlooked something.
Thanks,
Craig
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------