Re: Logical replication: duplicate key problem

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