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

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.

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.

> 2) pglogical has mechanisms to re-synchronize an entire subscription or
> selected tables.  After identifying a few tables, out of many, that are
> out of sync following the re-construction of a replication link, I have
> used this capability to re-synchronize just what was needed. Short of
> completely re-building logical replication links (clearing and copying
> all the data) does postgres 10 have any mechanisms to re-synchronize a
> subscription or a subset thereof?

Not officially, but you could perhaps achieve that by manually fiddling
with the entries in the system catalog pg_subscription_rel.

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