Re: Logical replication issue - Mailing list pgsql-admin

Good afternoon,

We have several different servers and databases all publishing data to one large centralized subscriber.  However in one of our largest databases, we've had issues with the sub after doing bulk inserts on the pub.

We are getting this attempt in logs:

rep1: 2021-07-30 14:19:15 EDT: :::[111719]:LOG:  logical replication apply worker for subscription "subrep1_pdx" has started
rep1: 2021-07-30 14:19:18 EDT: :::[111719]:ERROR:  duplicate key value violates unique constraint "pk_users"
rep1: 2021-07-30 14:19:18 EDT: :::[111719]:DETAIL:  Key (user_id)=(5826051) already exists.
rep1: 2021-07-30 14:19:18 EDT: :::[65376]:LOG:  worker process: logical replication worker for subscription 650911835 (PID 111719) exited with exit code 1

This issue is that 5826051 does not actually exist on the sub table users.  I even checked the sequence to see if it was out of sync, and that wasn't the case.  There seems to be something wrong with the way the data is being applied, but I have no idea what would cause this error.  This table "users", only has inserts and updates, no deletes.

So far the only solution would be a laborious task of wiping the tables and doing a complete resync of the subscriber.  Given the amount of data here, and the slow uncompressed copy command, this takes 16 hours.

Any thoughts and/or suggestions?   Is there any quick work around to refresh only that one table?

--

Jayson Hreczuck | Principal Database Administrator

C: +1 617.571.9861130 Turner St, Ste 530, Waltham, MA 02453