On 7/20/20 10:47 AM, Tom Lane wrote:
Thomas Kellerer <shammat@xxxxxxx> 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.klaver@xxxxxxxxxxx
