Logical replication: duplicate key problem

From: Johann Spies <johann(dot)spies(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Logical replication: duplicate key problem
Date: 2018-03-19 07:30:20
Message-ID: CAGZ55DT=FL4EvFD4Amfc2r=qu1dBER97W1o5-Y8j9QWJArYZFQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have followed the following process:

* pg_dump --schema-only on server 1
* restored that schema-only dump on server 2
* created a publication on server 1 including all the tables on server 1
* created a subscription on server 2

Server 2 does not get updated data for the schema involved from
anywhere else than the logical replication.

For some tables (some of them having many millions of records) the
process of replication seems to go smoothly. But for too many tables I
get this type of error messages:

2018-03-18 08:00:45.915 SAST [13512] ERROR: duplicate key value
violates unique constraint "country_pkey"
2018-03-18 08:00:46.088 SAST [13513] ERROR: duplicate key value
violates unique constraint "abstract_id_key"

In many of those cases it involves a serial field. In at least one
case in involved a citext field with a unique constraint.

Now just wonder how logical replication between the two servers can
produce such errors if the constraints on both sides are the same? Is
this a bug?

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-03-19 13:05:33 Re: Logical replication: duplicate key problem
Previous Message Tom Lane 2018-03-18 23:13:03 Re: pentaho timestamp issue