DMS Serverless Migration Errors on scale-up migrating to Aurora Postgres

Hey Brien, Looks like you have already spent a quite a bit of time troubleshooting the issue. Let's see in different angle now. Your description indicates DMS works little different when you use serverless over provisioned DMS configuration. It's worth checking DMS logs as well . Please enable cloudwatch log for the DMS migration task if not done already and check the all the log entries just before the time of time of errors in Aurora PG logs. This will give some useful information for root cause of the errors. Check the below document: https://repost.aws/knowledge-center/dms-task-error-status

Also as per the below AWS document, duplicate records on the target table is expected while running the Full load and CDC. In your case since the primary key is enabled on the target DMS is erroring out. Despite these table errors, let the job run (I assume it is not failing) and check the manually compare and validate the records on source and target tables. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.General.DuplicateRecords

The "Duplicate key violates unique key" error typically occurs when attempting to insert a record into a PostgreSQL database table using a field value that already exists and is designated as a unique key constraint in the table. In a DMS (Database Migration Service) serverless environment, this issue can arise during data replication if the source and target tables are not in sync, leading to conflicts between primary key or unique key values.

To address this issue, you can take the following steps:

Data Analysis: Identify the specific table and field where the duplicate key violation occurs. This can be done through analyzing the error logs or monitoring the data replication process.

Data Consistency: Ensure that the source and target tables are consistent. Verify that no conflicting data exists that could violate unique key constraints.

Conflict Resolution: If there are conflicting data between the source and target tables, you'll need to resolve these conflicts before continuing the replication process. This can involve updating the existing data, changing unique key constraints, or using data transformation techniques.

DMS Configuration: Review the DMS configuration settings to ensure that it aligns with the unique key constraints of your PostgreSQL database. Pay close attention to the replication rules and conflict resolution mechanisms provided by the DMS service.

Error Handling: Implement error handling and logging mechanisms within your DMS setup to capture and track the "Duplicate key violates unique key" errors. This will help in identifying the root cause of the issue and applying appropriate corrective actions.

By following these steps, you can address the "Duplicate key violates unique key" errors in your target PostgreSQL database within a DMS serverless environment and ensure a smooth data replication process.