Re: pg_upgrade and publication/subscription problem

On Fri, Nov 26, 2021 at 5:47 PM Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:
>>
>> 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.
>

I think you can disable the subscription as well or before dropping
disassociate the slot from subscription.

> 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.
>

Yeah, I understand that point. Here, the problem is that both old and
new tables belong to the same publication, and you can't refresh some
tables from the publication.

> 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 ?
>

I think we don't want to make assumptions about the remote end being
the same after the upgrade and we let users reactivate the
subscriptions in a suitable way. See [1] (Start reading from "..When
dumping logical replication subscriptions..") In your case, if you
wouldn't have allowed new tables in the publication then a simple
Alter Subscription <sub_name> Refresh Publication with (copy_data =
false) would have served the purpose.

BTW, just for records, this problem has nothing to do with any changes
in PG-14, the same behavior exists in the previous versions as well.

[1] - https://www.postgresql.org/docs/devel/app-pgdump.html
--
With Regards,
Amit Kapila.