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

Adrian Klaver schrieb am 20.07.2020 um 16:45:
> 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?

Pretty basic:

create publication some_publication
for table table1, table2, table3, ...., table50;

create subscription foo
connection '...'
publication some_publication;

The approach that worked:

* create the publication with the first 10 tables
* create the subscription, wait until the initial sync is finished
* alter the publication add the next 10 tables
* refresh the subscription, wait until the sync is finished - repeat until all tables are replicated.

Note that I did not pay attention to foreign key relationships when adding the tables
(I essentially added them in alphabetical order). I first thought that was going to
be a problem, but it seems Postgres can cope with that.

Once the initial sync was through (about a week now), no further problems came up.
It's been running smoothly since then

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

No, as mentioned, those are varchar(20) columns.
The values are generated by the application (no default value defined for the column)

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

Same datacenter.

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

No.

> Are the tables heavily used when the subscription is invoked?

Used, but not "heavily" (a few inserts per minute).

And I think the tables where the errors happened, weren't written to at all.
Those were rather small lookup tables (a few hundred rows at most)

There are no triggers on the tables where the errors happened in case that's important.