Subject: Re: Upgrade and re-synchronization with logical replication
 (pglogical and PG 10)
To: Chad Trabant <chad@iris.washington.edu>, pgsql-admin@lists.postgresql.org
References: <10358935-0D5B-4C94-AF02-D64B1A195442@iris.washington.edu>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <e508c847-bf4e-5121-27d4-30e6d0a4bd48@2ndquadrant.com>
Date: Tue, 23 Jan 2018 12:36:48 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: <10358935-0D5B-4C94-AF02-D64B1A195442@iris.washington.edu>
Content-Type: text/plain; charset=windows-1252
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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