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

On 7/20/20 7:22 AM, Thomas Kellerer wrote:
>> 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.
>>
>
> Any pointers where I should start looking to investigate this?

What are the PUBLICATION and SUBSCRIPTION commands being used?

Where is "xxx_pkey" coming from, e.g. sequence?

Where are source and target relative to each other in network/world?

Are there any other errors in log at around the same time that might apply?

Have you looked at
https://www.postgresql.org/docs/12/logical-replication-architecture.html#LOGICAL-REPLICATION-SNAPSHOT?:

30.5.1. Initial Snapshot

Are the tables heavily used when the subscription is invoked?

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