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-18c84b34eb2f@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