Re: Upgrade and re-synchronization with logical replication (pglogical and PG 10)

> On Jan 23, 2018, at 9:36 AM, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>
> On 1/22/18 01:53, Chad Trabant wrote:
>> 1) In my initial testing it seems that an upgrade via pg_upgrade does
>> not migrate logical replication slots or origins
>> (pg_replication_slots and pg_replication_origin).
>
> Correct.

Thank you for confirming. I'm sure there is a good reason for this, can someone explain why (even if briefly)?

> A better approach might be to use pglogical itself to do the upgrade.
> That is, set up a new replica somewhere that is PG10, replicate into
> that, and switch over.

This requires duplicating the data and host. What I'm trying to do is upgrade an existing publisher and subscriber pair with limited downtime. I suppose this could be done by replicating the publisher to a new publisher, then to a new subscriber, then swap out the old systems for the new. Unfortunately, I have have some systems with a large volume of data and specialized hardware (relative to our data center), which makes creating a duplicate not possible.

Luckily in my scenario it is acceptable to turn off all inserts/updates/deletes/truncates to the paired systems for short term, planned maintenance and only one of each pair needs to be up for select access. I'll be experimenting with how to re-attach a publisher-subscriber pair post-pg_upgrade. Any hints or gotchas that could guide me would be appreciated.