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

On 7/20/20 10:47 AM, Tom Lane wrote:
> 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.

I'm probably missing something, but would that not result in a 'key not
found' type of error. The OP is seeing "duplicate key value violates
unique constraint xxx_pkey". To me that indicates a doubling up of at
least some of the data replication.

>
> regards, tom lane
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com