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