Error: Table Does Not Have a Replica Identity and Publishes Deletes

Issue

I have configured my connection and source database to use Fivetran's logical replication update method. However, when I attempt to delete records from the database manually, the following error appears:

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

Environment

Connector: PostgreSQL

Resolution

To resolve this issue, we recommend adding a primary key to the table, allowing PostgreSQL to track deleted rows during logical replication. Use the following command, replacing <table> and <column> with the relevant values:

ALTER TABLE <table> ADD PRIMARY KEY (<column>);

If adding a primary key isn't an option, for example, in a legacy or temporary table, configure the table to log full row contents instead. Use the following command, replacing the placeholder <table>:

ALTER TABLE <table> REPLICA IDENTITY FULL;

For more information, see the PostgreSQL REPLICA IDENTITY documentation.

Cause

This issue occurs when a table doesn't have a primary key or replica identity, which PostgreSQL requires to track changes during logical replication. Without either, PostgreSQL can't determine which rows to log for delete operations.