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.klaver@xxxxxxxxxxx
