Re: Logical replication from 11.x to 12.x and "unique key violations"

Thomas Kellerer <shammat(at)gmx(dot)net> writes:
>> I have a strange error when using logical replication between a 11.2
>> source database and a 12.3 target.
>>
>> If I create the publication with all needed tables (about 50) at
>> once, I get "duplicate key value violates unique constraint xxx_pkey"
>> errors during the initial replication (when creating the
>> subscription).
>>
>> When create the publication only with a few tables, the initial data
>> sync works without problems. To replicate all tables, I add the
>> tables incrementally to the publication, and refresh the
>> subscription.
>>
>> If I do it like that (step-by-step) everything works fine. Tables
>> that generated the "duplicate key value" error previously will
>> replicate just fine. The tables are quite small, some of them less
>> then 100 rows.

I have not looked at the code, but it wouldn't surprise me if the initial
replication just copies all the specified tables in some random order.
If there are FK references involved, the replication would have to be
done with referenced tables first, and I bet there's no logic for that.
(Even if there was, it could not cope with circular references or
self-references.)

Best bet might be to not install the subscriber's foreign key
constraints till after the initial sync is done.

regards, tom lane