Handling Unique Constraint Conflicts in Logical Replication

Authors: Ashutosh Sharma, Senior Software Engineer, and Gauri Kasar, Product Manager

Logical replication can keep your PostgreSQL environments in sync, helping replicate selected tables with minimal impact on the primary workload. But what happens when your subscriber hits a duplicate key error and replication grinds to a halt? If you’ve seen a unique‑constraint violation while replicating between Azure Database for PostgreSQL servers, you’re not alone. This blog covers common causes, prevention tips, and practical recovery options.

In PostgreSQL logical replication, the subscriber can fail with a unique-constraint error when it tries to apply a change that would create a duplicate key.

duplicate key value violates unique constraint

Understanding why this happens?

When an INSERT or UPDATE would create a value that already exists in a column (or set of columns) protected by a UNIQUE constraint (including a PRIMARY KEY). In logical replication, this most commonly occurs because of local writes on the subscriber or if the table is being subscribed from multiple publishers. These conflicts are resolved on the subscriber side. 

1. Local writes on the subscriber: a row with the same primary key/unique key is inserted on the subscriber before the apply worker processes the corresponding change from the publisher.
2. Multi-origin / multi-master without conflict-free keys: two origins generate (or replicate) the same unique key.
3. Initial data synchronization issues: the subscriber already contains data when the subscription is created with initial copy enabled, resulting in duplicate inserts during the initial table sync. 

How to avoid this?

• Avoid local writes on subscribed tables (treat the subscriber as read-only for replicated relations). 
• Avoid subscribing to the same table from multiple publishers unless you have explicit conflict handling and a conflict-free key design. 

Enabling server logs can help you identify and troubleshoot unique‑constraint conflicts more effectively. Refer to the official documentation to configure and access PostgreSQL logs.

How to handle conflicts (recovery options) 

Option 1: Delete the conflicting row on the subscriber 

Use the subscriber logs to identify the key (or row) causing the conflict, then delete the row on the subscriber with a DELETE statement. Resume apply and repeat if more conflicts appear. 

Option 2: Use conflict logs and skip the conflicting transaction (PostgreSQL 17+) 

Starting with PostgreSQL 17, logical replication provides detailed conflict logging on the subscriber, making it easier to understand why replication stopped and which transaction caused the failure. When a replicated INSERT would violate a non‑deferrable unique constraint on the subscriber for example, when a row with the same key already exists the apply worker detects this as an insert_exists conflict and stops replication. In this case, PostgreSQL logs the conflict along with the transaction’s finish LSN, which uniquely identifies the failing transaction.

ERROR: conflict detected on relation "public.t2": conflict=insert_exists
... in transaction 754, finished at 0/034F4090
ALTER SUBSCRIPTION <subscription_name> SKIP (lsn = '0/034F4090');

Option 3: Rebuild (re-sync) the table

Rebuilding (re‑syncing) a table is the safest and most deterministic way to resolve logical replication conflicts caused by pre‑existing data differences or local writes on the subscriber. This approach is especially useful when a table repeatedly fails with unique‑constraint violations and it is unclear which rows are out of sync.

Step 1 (subscriber): Disable the subscription.

ALTER SUBSCRIPTION <subscription_name> DISABLE;

Step 2 (subscriber): Remove the local copy of the table so it can be re-copied.

TRUNCATE TABLE <conflicting_table>;

Step 3 (publisher): Ensure the publication will (re)send the table (one approach is to recreate the publication entry for that table).

ALTER PUBLICATION <pub_with_conflicting_table> DROP TABLE <conflicting_table>;
CREATE PUBLICATION <pub_with_conflicting_table_rebuild> FOR TABLE <conflicting_table>;

Step 4 (subscriber): Create a new subscription (or refresh the existing one) to re-copy the table.

CREATE SUBSCRIPTION <sub_rebuild>
    CONNECTION '<connection_string>'
    PUBLICATION <pub_with_conflicting_table_rebuild>;

Step 5 (subscriber): Re-enable the original subscription (if applicable).

ALTER SUBSCRIPTION <subscription_name> ENABLE;

Conclusion

In most cases, these conflicts occur due to local changes on the subscriber or differences in data that existed before logical replication was fully synchronized. It is recommended to avoid direct modifications on subscribed tables and ensure that the replication setup is properly planned, especially when working with tables that have unique constraints.