How do I troubleshoot data replication issues and CDC failures when I use AWS DMS to migrate source databases to Amazon Redshift?

Resolution

CDC failures

If you experience CDC failures, then review the AWS DMS task logs for error messages that are related to specific tables. You can also check the CDCLatencySource and CDCLatencyTarget Amazon CloudWatch metrics for your AWS DMS task.

Take one or more of the following actions based on the error message that you receive:

• Verify that the source tables have primary keys. For PostgreSQL sources, captured tables must have a primary key for AWS DMS to process DELETE and UPDATE operations.
• Confirm that you set the change logs retention period on the source database to at least 24 hours.
• Make sure that the AWS account that AWS DMS uses has the required permissions for both the source and target databases.

Data inconsistencies

To resolve data inconsistencies, complete the following steps:

1. Create a validation only task to identify mismatched records between the source and target. For more information, see Redshift validation performance.
2. Query the awsdms_validation_failures_v1 table in the target database to view inconsistencies.
3. Check the awsdms_apply_exceptions table on the target database for detailed error information about failed migrations.
4. Verify that all replicated target tables have a primary key or unique index.
   Note: Amazon Redshift doesn't enforce unique, primary key, and foreign key constraints. So, duplicates might occur.
5. If you use PostgreSQL as a source, then you must define primary keys for the source and target tables.
6. For tables with inconsistencies, use the AWS DMS task to reload the tables.

Type conversion issues

To resolve type conversion issues, complete the following steps:

1. Review the AWS DMS task logs for errors that are related to data truncation or string length that exceeds the Data Definition Language (DDL) length.

2. Check the awsdms_apply_exceptions table for failed migrations.

3. Adjust column data types in the target Redshift database to accommodate larger data. For example, change varchar to text for columns with length issues.

4. Modify the MaxLobSize parameter in the task settings to handle larger data sizes, as shown in the following example:

   {
     "TargetMetadata": {
       "MaxLobSize": 32
     }
   }

5. Confirm that you're using a supported data type, and make sure that you correctly mapped the source and target.

Performance issues and high latency

To improve performance and reduce latency, take the following actions:

• Review the CDCLatencySource and CDCLatencyTarget CloudWatch metrics for the AWS DMS task. If the values show latency, then see the following documentation:
  How can I troubleshoot high target latency on an AWS DMS task?
  How do I troubleshoot high source latency on an AWS DMS task?
• Check that you're using the correct replication instance size and configuration for your workload.
• Use the ParallelLoadThreads, ParallelLoadBufferSize, ParallelApplyThreads, ParallelApplyBufferSize multithreaded task settings for full load and CDC tasks.
• Increase memory settings to allow AWS DMS to hold transactions in memory longer, as shown in the following example:

  {
    "ChangeProcessingTuning": {
      "MemoryLimitTotal": 20240,
      "MemoryKeepTime": 120
    }
  }

Duplicate records

To prevent duplicate records, take the following actions:

• Confirm that the replicated target tables have a primary key or unique index.
• Manually create tables on the target with primary keys and set the TargetTablePrepMode to DO_NOTHING or TRUNCATE_BEFORE_LOAD in the task settings.
• Use the primary-key-def AWS DMS table mapping rule to explicitly define primary keys.

Missing or incomplete data

To resolve missing or incomplete data, take the following actions:

• Review the AWS DMS task logs for warnings or errors that are related to specific tables.
• Check the CDCIncomingChanges metric to determine whether AWS DMS captures all changes from the source.
• Verify that the source database user account that AWS DMS uses has the necessary permissions to access all required tables and schemas.
• Create a validation only task to identify mismatched records. For more information, see Redshift validation performance.
• For tables with consistent issues, you can create individual AWS DMS tasks to troubleshoot more granularly.

Related information

Best practices for AWS Database Migration Service

Troubleshooting migrations tasks in AWS Database Migration Service

AWS DMS data validation