CODEWITHSUNDEEP
postgresqlchange-data-capture
sir
sir

Reputation: 61

CDC. cannot delete from table because it does not have a replica identity and publishes deletes

Setting up the Change Data Capture with Postgresql. Enabled replica for one table: alter table TABLE_NAME REPLICA IDENTITY FULL;

When trying to delete a record from a completely different table, we get an error.

ERROR:  cannot delete from table TABLE_NAME because it does not 
        have a replica identity and publishes deletes

HINT:  To enable deleting from the table, set REPLICA IDENTITY 
       using ALTER TABLE.

Even there is to disable replication at the level of all databases, the error remains. Please tell me how to deal with this problem? Maybe there is a link to the relevant article? Internet search did not return any results.

Upvotes: 0

Views: 2424

Answers (1)

light souls
light souls

Reputation: 728

By logical replication the table itself must have an unique index or primary key field. Without having this option, replication is not working.

Consider you have created an unique index or primary key in order to replicate your data from table1 to another table2. lets say you have deleted unique key or primary key on your source table. Then you tried to delete your data from table, it will throw exception like this:

Execute failed: ERROR: cannot delete from table "table1" because it does not have a replica identity and publishes deletes Hint: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

To get rid of this exception you have to drop your table from publication or you have to add a primary key or unique key to your table.

I experienced similar exception. There is two solutions:

  1. removing table from publication
  2. creating primary key or unique index on source table which is replicated.

In my case i have decided not to use publicaton table any more. But forget to drop table from publication. I hope it helps.

Upvotes: 0

Related Questions