Thread: Logical replication: duplicate key problem

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)

On 03/19/2018 12:30 AM, Johann Spies wrote:
> 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"

Are you getting the same errors on server 1?

Are you sure nothing else is touching server 2?

> 
> 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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com