Hello, I came across a surprising behavior when upgrading our PostgreSQL 10 DBs that also serve as a destination for the logical replication of some reference tables.
pg_upgrade turns off all subscriptions on the cluster and doesn't turn them on. Specifically, it creates them with connect = false, as discussed at the thread starting at https://www.postgresql.org/message-id/e4fbfad5-c6ac-fd50-6777-18c84b34e...@2ndquadrant.com Unfortunately, I can't find any mention of this in the docs of pg_upgrade, so I am at leas willing to add those if we can't resolve this in a more automated way (read below). Since we can determine those subscriptions that were active on the old cluster immediately before the upgrade, we could collect those and emit a script at the end of the pg_upgrade to turn them on, similar to the one pg_upgrade produces for the analyze. There are two options when re-enabling the subscriptions: either continue from the current position (possibly discarding all changes that happened while the database was offline), or truncate the destination tables and copy the data again. The first one corresponds to setting copy_data=false when doing refresh publication. The second one is a combination of a prior truncate + refresh publication with copy_data=true and doesn't seem like an action that is performed in a single transaction. Would it make sense to add a copy_truncate flag, false by default, that would instruct the logical replication worker to truncate the destination table immediately before resyncing it from the origin? Regards, Oleksii
