pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpgrade and re-synchronization with logical replication (pglogical and PG 10)
4+ messages / 2 participants
[nested] [flat]
* Upgrade and re-synchronization with logical replication (pglogical and PG 10)
@ 2018-01-22 06:53 Chad Trabant <chad@iris.washington.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Chad Trabant @ 2018-01-22 06:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello DBAs,
I have a couple of systems using logical replication via pglogical. Soon these systems will be upgraded from postgres 9.5 to 10. I have two related questions:
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). The result is that replication links must be "re-constructed" following an upgrade. Short of controlling all the applications to avoid new inserts to the publisher/master, the recommended solutions appear to be re-building the downstream subscribers as if it they were brand new, i.e. copying all the data. For large, multi-terabyte, databases this is wasteful and time consuming, as most of the data is already at the remote location. Perhaps I have missed something. Is there a way to retain or otherwise reconstruct a logical replication state and have the downstream servers catchup to the upgraded publisher/master?
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?
Thanks in advance!
Chad
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrade and re-synchronization with logical replication (pglogical and PG 10)
@ 2018-01-23 17:36 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
parent: Chad Trabant <chad@iris.washington.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Eisentraut @ 2018-01-23 17:36 UTC (permalink / raw)
To: Chad Trabant <chad@iris.washington.edu>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrade and re-synchronization with logical replication (pglogical and PG 10)
@ 2018-01-25 02:06 Chad Trabant <chad@iris.washington.edu>
parent: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chad Trabant @ 2018-01-25 02:06 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Jan 23, 2018, at 9:36 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrade and re-synchronization with logical replication (pglogical and PG 10)
@ 2018-01-25 17:01 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
parent: Chad Trabant <chad@iris.washington.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2018-01-25 17:01 UTC (permalink / raw)
To: Chad Trabant <chad@iris.washington.edu>; +Cc: pgsql-admin@lists.postgresql.org
On 1/24/18 21:06, Chad Trabant wrote:
>> On Jan 23, 2018, at 9:36 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.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)?
pg_upgrade does not preserve WAL information such as LSNs between the
old and new cluster, so it wouldn't know where the slots should start.
Basically, the approach that pg_upgrade takes is incompatible with
replication slots.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-01-25 17:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-22 06:53 Upgrade and re-synchronization with logical replication (pglogical and PG 10) Chad Trabant <chad@iris.washington.edu>
2018-01-23 17:36 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2018-01-25 02:06 ` Chad Trabant <chad@iris.washington.edu>
2018-01-25 17:01 ` 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