Reversing pg_logical replication

From: Craig James <cjames(at)emolecules(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Reversing pg_logical replication
Date: 2018-09-21 15:21:45
Message-ID: CAFwQ8rdGB-jUPtrDXpp-gMgS8guWg8SWSkBjkXy0San3rmvj8w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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:

1. Shut off activity
2. delete subscription from B
3. delete publication from A
4. create publication on B
5. 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.
---------------------------------

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Andres Freund 2018-09-21 16:26:24 Re: How to avoid the XLog Write to be the performance bottleneck
Previous Message Pierre Ochsenbein 2018-09-21 13:54:28 Re: PostGIS extension