AFAIU the main problem in your case is that you didn't block the write traffic on the publisher side. Let me try to understand the situation. After the upgrade is finished, there are some new tables with data on the publisher, and did old tables have any additional data?
Correct.
Are the contents in pg_replication_origin intact after the upgrade?
Yes
So, in short, I think what we need to solve is to get the data from new tables and newly performed writes on old tables. I could think of the following two approaches:
Approach-1: 1. Drop subscription and Truncate all tables corresponding to subscription.
2. Create a new subscription for the publication.
If I drop subscription it will drop WAL ou publication side and I lost all changed data between the starting of pg_upgrade process and now.
My problem is not related with new tables, they will be copied fine because doesn´t exists any record on subscriber.
But old tables had records modified since that pg_upgrade process, that is my problem, only that.
My question remains the same, why pg_subscription_rel was not copied from previous version?
If pg_upgrade would copy pg_replication_origin (it did) and these pg_subscription_rel (it didn´t) records from version 13 to 14, when I enable subscription it would start copying data from that point on, correct ?
