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

From: Chad Trabant <chad(at)iris(dot)washington(dot)edu>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Upgrade and re-synchronization with logical replication (pglogical and PG 10)
Date: 2018-01-22 06:53:16
Message-ID: 10358935-0D5B-4C94-AF02-D64B1A195442@iris.washington.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Mohammad Ali Raza 2018-01-22 07:55:18 RE: POSTGRESQL 10 Media download with CHECKSUM VALUE
Previous Message Sohel Tamboli 2018-01-22 06:39:04 Migration of postgresql 9.6 from amazon EC2 to Amazon Aurora