Duplicate Rows Appear Despite a Source Primary Key

Issue

Duplicate rows with the same primary key value appear in the destination.

The affected source table has a valid primary key and uses REPLICA IDENTITY FULL.

Environment

Connector: Amazon RDS for PostgreSQL
Incremental sync method: Logical replication

Resolution

To resolve this issue:

Confirm that the table has a valid primary key.

Change its replica identity to DEFAULT:

ALTER TABLE <schema_name>.<table_name> REPLICA IDENTITY DEFAULT;

Monitor subsequent syncs to confirm that no new duplicate rows appear.

To remove existing duplicate rows, re-sync the affected table. To learn how, see How to Trigger Historical Re-Syncs.

Only use REPLICA IDENTITY DEFAULT if the table has a valid primary key. If the table doesn't have a primary key, confirm the appropriate replica identity configuration for your setup before making changes.

Cause

This issue occurs when a table with a primary key uses REPLICA IDENTITY FULL. Setting the replica identity to DEFAULT makes PostgreSQL use the primary key to identify rows during logical replication.