pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReversing pg_logical replication
2+ messages / 2 participants
[nested] [flat]
* Reversing pg_logical replication
@ 2018-09-21 15:21 Craig James <cjames@emolecules.com>
2018-09-25 17:53 ` Re: Reversing pg_logical replication Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 1 reply; 2+ messages in thread
From: Craig James @ 2018-09-21 15:21 UTC (permalink / raw)
To: 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.
---------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Reversing pg_logical replication
2018-09-21 15:21 Reversing pg_logical replication Craig James <cjames@emolecules.com>
@ 2018-09-25 17:53 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 0 replies; 2+ messages in thread
From: Peter Eisentraut @ 2018-09-25 17:53 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-09-25 17:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-21 15:21 Reversing pg_logical replication Craig James <cjames@emolecules.com>
2018-09-25 17:53 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox