I accidentally loaded data into a table before setting up a logical replication subscription for it and now I'm getting errors when I try to sync it (duplicate key already exists).

I can't delete the rows because "cannot truncate a table referenced in a foreign key constraint".

Is there a way to tell the subscription that the table was pre-synced or pre-populated?

We're getting lots of logs lines like:

ERROR: duplicate key value violates unique constraint

Thanks.

I was able to get this fixed using a bit of a hack. Basically, drop the table from the publication, refresh the publication from the subscriber, add the table to the publication again, then refresh the publication again, but with copy_data=false:

  1. drop the table from the publication.

    alter publication my-publication drop table only my-table ;
    
  2. Refresh the subscription.

    ALTER SUBSCRIPTION my-subscription REFRESH PUBLICATION;
    

    The log lines seem to stop at this point. Good!

  3. Add the table to the publication.

    alter publication my-publication add table only my-table ;
    
  4. Refresh the subscription with copy_data (false)

     ALTER SUBSCRIPTION my-subscription REFRESH PUBLICATION with (copy_data = false);
    

This seems to work, though it seems like it'd be problematic on a table that was changing rapidly.

It'd be nice to put this in a transaction somehow, but the commands are being run alternately on the publisher and the subscriber, so I don't know how that'd work.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.