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(dot)klaver(at)aklaver(dot)com