Postgresql – How to mark a table as pre-populated with postgresql logical replication

postgresqlreplicationtransactional-replication

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.

Best Answer

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.

Related Question