Re: Logical replication issue

On Sat, Jul 31, 2021, 12:14 AM Jayson Hreczuck <
jayson(dot)hreczuck(at)studentuniverse(dot)com> wrote:

> 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 AdministratorC: +1 617.571.9861*130
> Turner St, Ste 530, Waltham, MA 02453
>

Hi Jay,

Might be creating a separate publication/subscription for that table alone
might resolve the risk resyncing the complete subscriber data. Just a
thought.

Regards,
Pavanteja,
9841380956

>
>