Secondary region for DMS

AWS DMS tasks in the "Created" state cannot be directly resumed from a specific CDC position in another region without a full reload [1]. However, you can implement a cross-region failover strategy using task assessment reports, task settings export/import, and pre-configured CDC start positions [2].

Current Limitations

DMS tasks in the "Created" state require a full reload when started because they haven't established the necessary replication state information. According to the documentation:

• Tasks can only resume CDC from a specific position if they have previously been running and have established checkpoints [1]
• The CdcStartPosition parameter allows specifying a position in date, checkpoint, or LSN/SCN format, but requires proper setup [3]

Possible Solutions

1. Task Settings Export/Import with CDC Position

You can export task settings from the primary region and import them to the secondary region with modifications:

1. Export the task settings from your primary region task
2. Capture the latest CDC position (checkpoint, LSN, or timestamp) from the running task
3. Create a new task in the secondary region with these settings, specifying the captured CDC position [3]

{
  "CdcStartPosition": "mysql-bin-changelog.000024:373"
}

For PostgreSQL sources, ensure a logical replication slot is created and associated with the source endpoint by setting the slotName extra connection attribute [4].

2. Checkpoint-Based Approach

For PostgreSQL and some other sources, you can use checkpoint format to resume replication:

--cdc-start-position "checkpoint:V1#27#mysql-bin-changelog.157832:1975:-1:2002:677883278264080:mysql-bin-changelog.157832:1876#0#0#*#0#93"

This requires extracting the checkpoint information from your primary region task before failover [3].

Important Considerations

• Database Engine Specifics: Different source databases (MySQL, PostgreSQL, Oracle, etc.) have different CDC position formats and requirements.
• Replication Slots: For PostgreSQL, ensure logical replication slots are properly configured in both regions. [4]
• Data Consistency: There may be a small gap or overlap in data depending on when you capture the CDC position and when you start the secondary task.
• Testing Required: This approach needs thorough testing to ensure proper failover without data loss.

Sources:

[1] Change processing tuning settings: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.ChangeProcessingTuning.html

[2] Working with AWS DMS task assessment reports: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.AssessmentReport.html

[3] ReplicationTask: https://docs.aws.amazon.com/dms/latest/APIReference/API_ReplicationTask.html

[4] Using PostgreSQL as a source for AWS DMS: https://docs.aws.amazon.com/dms/latest/userguide/

While AWS DMS doesn't provide a built-in mechanism for cross-region task state replication, you could consider implementing a custom solution using AWS Lambda functions that periodically capture CDC positions from primary region tasks and update configurations in the secondary region. This would require careful orchestration and testing to ensure proper failover capabilities.

[+] https://aws.amazon.com/blogs/database/build-an-incremental-data-load-solution-using-aws-dms-checkpoints-and-database-logs/