Обсуждение: Upgrade and re-synchronization with logical replication (pglogicaland PG 10)
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
Re: Upgrade and re-synchronization with logical replication(pglogical and PG 10)
От
Peter Eisentraut
Дата:
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
Re: Upgrade and re-synchronization with logical replication(pglogical and PG 10)
От
Chad Trabant
Дата:
> 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 pairwith 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 dataand 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 shortterm, planned maintenance and only one of each pair needs to be up for select access. I'll be experimenting with howto re-attach a publisher-subscriber pair post-pg_upgrade. Any hints or gotchas that could guide me would be appreciated.
Re: Upgrade and re-synchronization with logical replication(pglogical and PG 10)
От
Peter Eisentraut
Дата:
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