Database migration is a critical aspect of modern IT operations, especially when you need to switch database systems while ensuring minimal downtime and data loss.
AWS DMS Serverless is a feature of AWS Database Migration Service (AWS DMS) that provides automatic provisioning, scaling, built-in high availability, and a reduced billing model based on workload changes to increase operations agility and optimize your costs. The serverless feature removes replication instance management tasks like capacity estimation, provisioning, cost optimization, and managing replication engine versions and patching.
The following diagram shows components required to setup AWS DMS Serverless replication setup.
In this post, we explore how to perform a database migration from MySQL to PostgreSQL using the AWS DMS Serverless replication and the AWS Command Line Interface (AWS CLI).
Benefits of the AWS CLI
The AWS CLI serves as a versatile tool for efficiently managing AWS resources. It enables you to automate tasks, integrate AWS functionality into scripts and applications, and work across diverse operating systems. The AWS CLI grants programmatic access to a broad spectrum of AWS services, allowing for flexible customization and efficient resource management. You can securely interact with AWS resources using AWS Identity and Access Management (IAM) roles and permissions. Overall, the AWS CLI is a powerful, cross-platform solution that streamlines operations, facilitates automation, and offers a consistent interface for AWS resource management.
Benefits of AWS DMS Serverless
AWS DMS Serverless offers several benefits over provisioned instances for database migration and data replication tasks. The following are some of the key advantages:
- Simplified management and operation – AWS DMS Serverless removes the need to manually provision, manage, and scale replication instances. It automatically scales resources up or down based on the demand, making sure that your migration tasks always have the resources they need to run efficiently. This hands-off approach saves you time and effort, allowing you to focus on other aspects of your project.
- Cost-effectiveness – With AWS DMS Serverless, you don’t have any upfront costs. AWS DMS Serverless autoscaling makes it an attractive option for unpredictable workloads or migrations with fluctuating data volumes. Additionally, AWS DMS Serverless can help you optimize costs by automatically adjusting resource allocation based on real-time migration needs.
- High availability – AWS DMS Serverless uses Amazon’s global infrastructure to provide high availability for your migration tasks. The Multi-AZ option automatically replicates AWS DMS data across Availability Zones, ensuring that your migration tasks remain accessible. This redundancy helps protect your business from downtime and data loss.
- Scalability – AWS DMS Serverless can scale up or down to handle any workload, from small-scale migrations to large-scale enterprise data transfers. It can automatically adjust resources based on real-time data transfer rates, ensuring that your migration tasks always have the resources they need to run smoothly.
- Ease of use – AWS DMS Serverless is straightforward to set up and use. You can create and manage replication tasks using the AWS DMS console or the AWS CLI. There is no need to worry about provisioning or managing underlying infrastructure, which frees up your time to focus on your migration efforts.
Autoscaling for AWS DMS Serverless
AWS DMS Serverless autoscaling relies on monitoring of underlying instance metrics in Amazon CloudWatch to determine when a replication should be scaled up or down for AWS DMS capacity unit (DCUs). As of March 2024, AWS DMS Serverless uses scaling CloudWatch metrics CPUUtilization and CapacityUtilization.
The following is the current scale-up threshold:
CPUUtilization > 90% for 10 datapoints within last 10 minsCapacityUtilization > 80% for 10 datapoints within last 10 mins
After an scale up event, AWS DMS Serverless doesn’t scale up again for 1 hour
After an scale up event, AWS DMS Serverless doesn’t scale down again for 2 hours
The following is the current scale-down threshold:
CapacityUtilization < 50% for 60 datapoint within last 1 hourCPUUtilization < 10% for 60 datapoint within last 1 hour
After a scale down event, AWS DMS Serverless doesn’t scale up again for 1 hour
After a scale down event, AWS DMS Serverless doesn’t scale down again for 2 hours
AWS DMS Serverless can also scale up when a failed replication is being retried and resumed after an out-of-memory issue.
Key differences between AWS DMS Serverless and provisioned:
The following table summarizes the key differences between AWS DMS Serverless and provisioned:
| Feature |
AWS DMS Serverless |
AWS DMS provisioned |
| Resource provisioning and scaling |
Automatic |
Manual |
| Billing |
Reduced
cost based on workload |
Fixed
costs |
| Management |
Simplified |
Complex to manage and optimize DMS
resources based on workload changes |
| Configuration
options |
Limited |
More |
| Data sources and targets (as of March
2024) |
Microsoft SQL Server, PostgreSQL,
MySQL, MariaDB (source only), Oracle, IBM DB2 LUW (source only) and as target-only for Amazon S3, Amazon Redshift, Amazon DynamoDB, Amazon Kinesis Data
Streams, Amazon MSK, Amazon OpenSearch Service, Amazon DocumentDB, Amazon
Neptune For current list, refer to the documentation |
Wider range of sources and targets |
Consider using AWS DMS Serverless in the following scenarios:
- You have a simple migration with predictable workloads
- You want to avoid the complexity of managing resources
- You are on a budget
Consider using AWS DMS provisioned in the following cases:
- You need more control over your migration environment
- You have a complex migration with fluctuating resource requirements
- You need to migrate data from or to a data source or target that is not supported by AWS DMS Serverless
Solution overview
In the following sections, we go through the steps to migrate a database table from MySQL to PostgreSQL using AWS DMS Serverless replication using the AWS CLI. The high-level steps are as follows:
- Create the source and target AWS DMS endpoints.
- Identify the AWS DMS replication subnet group and Amazon Elastic Compute Cloud (Amazon EC2) security group used to connect the source and target endpoints.
- Create the AWS DMS Serverless replication configuration.
- Start AWS DMS Serverless replication.
- Stop AWS DMS Serverless replication.
- (Optional) Modify the AWS DMS Serverless replication configuration.
- (Optional) Resume or reload the AWS DMS Serverless replication.
- (Optional) Reload a single table while the AWS DMS Serverless replication is running.
- (Optional) Check the table statistics of the AWS DMS Serverless replication.
- Clean up post-migration.
Prerequisites
You should have the following prerequisites:
Create the source and target endpoints
Use the following code to define the endpoints for the source MySQL database and the target PostgreSQL database:
Note that we are using the default region as configured in CLI to change you can add –region ap-southeast-2 (for Sydney region) at the end of each command.
# mysql source endpoint
aws dms create-endpoint \
--endpoint-identifier climysqlsource \
--endpoint-type source \
--engine-name mysql \
--server-name rds-mysqldb-3f346432534.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com \
--username dbuser \
--password \
--port 3306
# output
{
"Endpoint":
{
"EndpointIdentifier": "climysqlsource",
"EndpointType": "SOURCE",
"EngineName": "mysql",
"EngineDisplayName": "MySQL",
"Username": "dbuser",
"ServerName": "rds-mysqldb-3f346432534.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Port": 3306,
"TransferFiles": false,
"ReceiveTransferredFiles": false,
"Status": "active",
"KmsKeyId": "arn:aws:kms:ap-southeast-2:123456789012:key/ab12cd3e-fg45-6hij-789k-l0l1m234n5o6",
"EndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"SslMode": "none",
"MySQLSettings":
{
"Port": 3306,
"ServerName": "rds-mysqldb-3f346432534.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Username": "dbuser"
}
}
}
# describe the source endpoint
aws dms describe-endpoints --query "Endpoints[?EngineName=='mysql' && contains(EndpointIdentifier,'cli')]"
# output
[
{
"EndpointIdentifier": "climysqlsource",
"EndpointType": "SOURCE",
"EngineName": "mysql",
"EngineDisplayName": "MySQL",
"Username": "dbuser",
"ServerName": "rds-mysqldb-3f346432534.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Port": 3306,
"TransferFiles": false,
"ReceiveTransferredFiles": false,
"Status": "active",
"KmsKeyId": "arn:aws:kms:ap-southeast-2:123456789012:key/ab12cd3e-fg45-6hij-789k-l0l1m234n5o6",
"EndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"SslMode": "none",
"MySQLSettings": {
"Port": 3306,
"ServerName": "rds-mysqldb-3f346432534.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Username": "dbuser"
}
}
]
# psql target endpoint
aws dms create-endpoint \
--endpoint-identifier clipsqltarget \
--endpoint-type target \
--engine-name postgres \
--server-name rds-postgresql-cba9abc9defg.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com \
--username dbuser \
--password <password> \
--port 5432 \
--database-name mydata
# output
{
"Endpoint":
{
"EndpointIdentifier": "clipsqltarget",
"EndpointType": "TARGET",
"EngineName": "postgres",
"EngineDisplayName": "PostgreSQL",
"Username": "dbuser",
"ServerName": "rds-postgresql-cba9abc9defg.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Port": 5432,
"DatabaseName": "mydata",
"TransferFiles": false,
"ReceiveTransferredFiles": false,
"Status": "active",
"KmsKeyId": "arn:aws:kms:ap-southeast-2:123456789012:key/ab12cd3e-fg45-6hij-789k-l0l1m234n5o6",
"EndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"SslMode": "none",
"PostgreSQLSettings":
{
"DatabaseName": "mydata",
"Port": 5432,
"ServerName": "rds-postgresql-cba9abc9defg.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Username": "dbuser"
}
}
}
# describe the target endpoint
aws dms describe-endpoints --query "Endpoints[?EngineName=='postgres' && contains(EndpointIdentifier,'cli')]"
# output
[
{
"EndpointIdentifier": "clipsqltarget",
"EndpointType": "TARGET",
"EngineName": "postgres",
"EngineDisplayName": "PostgreSQL",
"Username": "dbuser",
"ServerName": "rds-postgresql-cba9abc9defg.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Port": 5432,
"DatabaseName": "mydata",
"TransferFiles": false,
"ReceiveTransferredFiles": false,
"Status": "active",
"KmsKeyId": "arn:aws:kms:ap-southeast-2:123456789012:key/ab12cd3e-fg45-6hij-789k-l0l1m234n5o6",
"EndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"SslMode": "none",
"PostgreSQLSettings": {
"DatabaseName": "mydata",
"Port": 5432,
"ServerName": "rds-postgresql-cba9abc9defg.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Username": "dbuser"
}
}
]
Identify the AWS DMS replication subnet group and EC2 security group
You need to identify the AWS DMS replication subnet group used for the AWS DMS Serverless migration and the EC2 security group name used to connect to the source MySQL database and the target PostgreSQL database. For this post, the replication subnet group has the dms keyword in the name and the EC2 security group has EC2. We use these keywords to search using the –query option and only output the required fields from the AWS CLI command. See the following code:
#find the pre-created DMS replication subnetgroup
aws dms describe-replication-subnet-groups --query "ReplicationSubnetGroups[?contains(ReplicationSubnetGroupIdentifier,'dms')].{ReplicationSubnetGroupIdentifier:ReplicationSubnetGroupIdentifier}"
# output
[
{
"ReplicationSubnetGroupIdentifier": "dmsreplicationsubnetgroup-2b2cdefghijklm7no"
}
]
#find the pre-created EC2 Securitygroup
aws ec2 describe-security-groups --query "SecurityGroups[?contains(GroupName,'EC2')].{grpname:GroupName,id:GroupId}"
# output
[
{
"grpname": "rds-myClientSG-12AB3CDEFGHIJ",
"id": "sg-1123ab4566cd7ef89"
}
]
Create the AWS DMS Serverless replication config
We need to define the AWS DMS Serverless replication config, which is essentially the AWS DMS replication task settings along with the compute config that defines the AWS DMS capacity unit (DCUs) and other options for the underlying AWS DMS Serverless replication instance like MultiAZ, ReplicationSubnetGroupId and VpcSecurityGroupIds. See the following code:
#create replication config
aws dms create-replication-config \
--replication-config-identifier climysql2psql \
--source-endpoint-arn arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F \
--target-endpoint-arn arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA \
--compute-config '{
"MaxCapacityUnits": 4,
"MultiAZ": false,
"ReplicationSubnetGroupId": "dmsreplicationsubnetgroup-2b2cdefghijklm7no",
"VpcSecurityGroupIds": ["sg-1123ab4566cd7ef89"]
}' \
--replication-type full-load-and-cdc \
--table-mappings '{
"rules": [
{
"rule-id": "783282541",
"rule-name": "783282541",
"rule-type": "selection",
"rule-action": "include",
"object-locator": {
"schema-name": "mydata",
"table-name": "newtable"
},
"filters": []
},
{
"rule-id": 1152034124,
"rule-name": "lkbtwrp3-a00w9q",
"rule-type": "transformation",
"rule-target": "schema",
"rule-action": "rename",
"object-locator": {
"schema-name": "mydata"
},
"value": "public"
}
]
}' \
--replication-settings '{
"TargetMetadata": {
"TargetSchema": "",
"SupportLobs": true,
"FullLobMode": false,
"LobChunkSize": 64,
"LimitedSizeLobMode": true,
"LobMaxSize": 32,
"InlineLobMaxSize": 0,
"LoadMaxFileSize": 0,
"ParallelLoadThreads": 0,
"ParallelLoadBufferSize": 0,
"BatchApplyEnabled": false,
"TaskRecoveryTableEnabled": false,
"ParallelLoadQueuesPerThread": 0,
"ParallelApplyThreads": 0,
"ParallelApplyBufferSize": 0,
"ParallelApplyQueuesPerThread": 0
},
"FullLoadSettings": {
"TargetTablePrepMode": "DROP_AND_CREATE",
"CreatePkAfterFullLoad": false,
"StopTaskCachedChangesApplied": false,
"StopTaskCachedChangesNotApplied": false,
"MaxFullLoadSubTasks": 8,
"TransactionConsistencyTimeout": 600,
"CommitRate": 10000
},
"Logging": {
"EnableLogging": true,
"EnableLogContext": false,
"LogComponents": [
{ "Id": "DATA_STRUCTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "COMMUNICATION", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "IO", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "COMMON", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "FILE_FACTORY", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "FILE_TRANSFER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "REST_SERVER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "ADDONS", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TARGET_LOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TARGET_APPLY", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "SOURCE_UNLOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "SOURCE_CAPTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TRANSFORMATION", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "SORTER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TASK_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TABLES_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "METADATA_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "PERFORMANCE", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "VALIDATOR_EXT", "Severity": "LOGGER_SEVERITY_DEFAULT" }
],
"CloudWatchLogGroup": null,
"CloudWatchLogStream": null
},
"ControlTablesSettings": {
"historyTimeslotInMinutes": 5,
"ControlSchema": "",
"HistoryTimeslotInMinutes": 5,
"HistoryTableEnabled": false,
"SuspendedTablesTableEnabled": false,
"StatusTableEnabled": false,
"FullLoadExceptionTableEnabled": false
},
"StreamBufferSettings": {
"StreamBufferCount": 3,
"StreamBufferSizeInMB": 8,
"CtrlStreamBufferSizeInMB": 5
},
"ChangeProcessingDdlHandlingPolicy": {
"HandleSourceTableDropped": true,
"HandleSourceTableTruncated": true,
"HandleSourceTableAltered": true
},
"ErrorBehavior": {
"DataErrorPolicy": "LOG_ERROR",
"EventErrorPolicy": "IGNORE",
"DataTruncationErrorPolicy": "LOG_ERROR",
"DataErrorEscalationPolicy": "SUSPEND_TABLE",
"DataErrorEscalationCount": 0,
"TableErrorPolicy": "SUSPEND_TABLE",
"TableErrorEscalationPolicy": "STOP_TASK",
"TableErrorEscalationCount": 0,
"RecoverableErrorCount": -1,
"RecoverableErrorInterval": 5,
"RecoverableErrorThrottling": true,
"RecoverableErrorThrottlingMax": 1800,
"RecoverableErrorStopRetryAfterThrottlingMax": true,
"ApplyErrorDeletePolicy": "IGNORE_RECORD",
"ApplyErrorInsertPolicy": "LOG_ERROR",
"ApplyErrorUpdatePolicy": "LOG_ERROR",
"ApplyErrorEscalationPolicy": "LOG_ERROR",
"ApplyErrorEscalationCount": 0,
"ApplyErrorFailOnTruncationDdl": false,
"FullLoadIgnoreConflicts": true,
"FailOnTransactionConsistencyBreached": false,
"FailOnNoTablesCaptured": true
},
"ChangeProcessingTuning": {
"BatchApplyPreserveTransaction": true,
"BatchApplyTimeoutMin": 1,
"BatchApplyTimeoutMax": 30,
"BatchApplyMemoryLimit": 500,
"BatchSplitSize": 0,
"MinTransactionSize": 1000,
"CommitTimeout": 1,
"MemoryLimitTotal": 1024,
"MemoryKeepTime": 60,
"StatementCacheSize": 50
},
"PostProcessingRules": null,
"CharacterSetSettings": null,
"LoopbackPreventionSettings": null,
"BeforeImageSettings": null,
"FailTaskWhenCleanTaskResourceFailed": false,
"TTSettings": {
"EnableTT": false,
"FailTaskOnTTFailure": false,
"TTS3Settings": null,
"TTRecordSettings": null
}
}'
# output
{
"ReplicationConfig":
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"ComputeConfig":
{
"MaxCapacityUnits": 4,
"MultiAZ": false,
"ReplicationSubnetGroupId": "dmsreplicationsubnetgroup-2b2cdefghijklm7no",
"VpcSecurityGroupIds": [
"sg-1123ab4566cd7ef89"
]
},
"ReplicationSettings": "{\"TargetMetadata\":{\"TargetSchema\":\"\",\"SupportLobs\":true,\"FullLobMode\":false,\"LobChunkSize\":64,\"LimitedSizeLobMode\":true,\"LobMaxSize\":32,\"InlineLobMaxSize\":0,\"LoadMaxFileSize\":0,\"ParallelLoadThreads\":0,\"ParallelLoadBufferSize\":0,\"BatchApplyEnabled\":false,\"TaskRecoveryTableEnabled\":false,\"ParallelLoadQueuesPerThread\":0,\"ParallelApplyThreads\":0,\"ParallelApplyBufferSize\":0,\"ParallelApplyQueuesPerThread\":0},\"FullLoadSettings\":{\"TargetTablePrepMode\":\"DROP_AND_CREATE\",\"CreatePkAfterFullLoad\":false,\"StopTaskCachedChangesApplied\":false,\"StopTaskCachedChangesNotApplied\":false,\"MaxFullLoadSubTasks\":8,\"TransactionConsistencyTimeout\":600,\"CommitRate\":10000},\"Logging\":{\"EnableLogging\":true,\"EnableLogContext\":false,\"LogComponents\":[{\"Id\":\"TRANSFORMATION\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_UNLOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"IO\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_LOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"PERFORMANCE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_CAPTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SORTER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"REST_SERVER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"VALIDATOR_EXT\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_APPLY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TASK_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TABLES_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"METADATA_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"FILE_FACTORY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"COMMON\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"ADDONS\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"DATA_STRUCTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"COMMUNICATION\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"FILE_TRANSFER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"}],\"CloudWatchLogGroup\":null,\"CloudWatchLogStream\":null},\"ControlTablesSettings\":{\"historyTimeslotInMinutes\":5,\"ControlSchema\":\"\",\"HistoryTimeslotInMinutes\":5,\"HistoryTableEnabled\":false,\"SuspendedTablesTableEnabled\":false,\"StatusTableEnabled\":false,\"FullLoadExceptionTableEnabled\":false},\"StreamBufferSettings\":{\"StreamBufferCount\":3,\"StreamBufferSizeInMB\":8,\"CtrlStreamBufferSizeInMB\":5},\"ChangeProcessingDdlHandlingPolicy\":{\"HandleSourceTableDropped\":true,\"HandleSourceTableTruncated\":true,\"HandleSourceTableAltered\":true},\"ErrorBehavior\":{\"DataErrorPolicy\":\"LOG_ERROR\",\"EventErrorPolicy\":\"IGNORE\",\"DataTruncationErrorPolicy\":\"LOG_ERROR\",\"DataErrorEscalationPolicy\":\"SUSPEND_TABLE\",\"DataErrorEscalationCount\":0,\"TableErrorPolicy\":\"SUSPEND_TABLE\",\"TableErrorEscalationPolicy\":\"STOP_TASK\",\"TableErrorEscalationCount\":0,\"RecoverableErrorCount\":-1,\"RecoverableErrorInterval\":5,\"RecoverableErrorThrottling\":true,\"RecoverableErrorThrottlingMax\":1800,\"RecoverableErrorStopRetryAfterThrottlingMax\":true,\"ApplyErrorDeletePolicy\":\"IGNORE_RECORD\",\"ApplyErrorInsertPolicy\":\"LOG_ERROR\",\"ApplyErrorUpdatePolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationPolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationCount\":0,\"ApplyErrorFailOnTruncationDdl\":false,\"FullLoadIgnoreConflicts\":true,\"FailOnTransactionConsistencyBreached\":false,\"FailOnNoTablesCaptured\":true},\"ChangeProcessingTuning\":{\"BatchApplyPreserveTransaction\":true,\"BatchApplyTimeoutMin\":1,\"BatchApplyTimeoutMax\":30,\"BatchApplyMemoryLimit\":500,\"BatchSplitSize\":0,\"MinTransactionSize\":1000,\"CommitTimeout\":1,\"MemoryLimitTotal\":1024,\"MemoryKeepTime\":60,\"StatementCacheSize\":50},\"PostProcessingRules\":null,\"CharacterSetSettings\":null,\"LoopbackPreventionSettings\":null,\"BeforeImageSettings\":null,\"FailTaskWhenCleanTaskResourceFailed\":false,\"TTSettings\":{\"EnableTT\":false,\"FailTaskOnTTFailure\":false,\"TTS3Settings\":null,\"TTRecordSettings\":null}}",
"TableMappings": "{\n \"rules\": [\n {\n \"rule-id\": \"783282541\",\n \"rule-name\": \"783282541\",\n \"rule-type\": \"selection\",\n \"rule-action\": \"include\",\n \"object-locator\": {\n \"schema-name\": \"mydata\",\n \"table-name\": \"newtable\"\n },\n \"filters\": []\n },\n {\n \"rule-id\": 1152034124,\n \"rule-name\": \"lkbtwrp3-a00w9q\",\n \"rule-type\": \"transformation\",\n \"rule-target\": \"schema\",\n \"rule-action\": \"rename\",\n \"object-locator\": {\n \"schema-name\": \"mydata\"\n },\n \"value\": \"public\"\n }\n ]\n}",
"ReplicationConfigCreateTime": "2023-07-31T15:28:56.540000+10:00",
"ReplicationConfigUpdateTime": "2023-07-31T15:28:56.540000+10:00"
}
}
# describe the newly created replication config
aws dms describe-replication-configs --query "ReplicationConfigs[?contains(ReplicationConfigIdentifier,'cli')]"
# output
[
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"ComputeConfig": {
"MaxCapacityUnits": 4,
"MultiAZ": false,
"ReplicationSubnetGroupId": "dmsreplicationsubnetgroup-2b2cdefghijklm7no",
"VpcSecurityGroupIds": [
"sg-1123ab4566cd7ef89"
]
},
"ReplicationSettings": "{\"TargetMetadata\":{\"TargetSchema\":\"\",\"SupportLobs\":true,\"FullLobMode\":false,\"LobChunkSize\":64,\"LimitedSizeLobMode\":true,\"LobMaxSize\":32,\"InlineLobMaxSize\":0,\"LoadMaxFileSize\":0,\"ParallelLoadThreads\":0,\"ParallelLoadBufferSize\":0,\"BatchApplyEnabled\":false,\"TaskRecoveryTableEnabled\":false,\"ParallelLoadQueuesPerThread\":0,\"ParallelApplyThreads\":0,\"ParallelApplyBufferSize\":0,\"ParallelApplyQueuesPerThread\":0},\"FullLoadSettings\":{\"TargetTablePrepMode\":\"DROP_AND_CREATE\",\"CreatePkAfterFullLoad\":false,\"StopTaskCachedChangesApplied\":false,\"StopTaskCachedChangesNotApplied\":false,\"MaxFullLoadSubTasks\":8,\"TransactionConsistencyTimeout\":600,\"CommitRate\":10000},\"Logging\":{\"EnableLogging\":true,\"EnableLogContext\":false,\"LogComponents\":[{\"Id\":\"TRANSFORMATION\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_UNLOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"IO\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_LOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"PERFORMANCE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_CAPTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SORTER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"REST_SERVER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"VALIDATOR_EXT\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_APPLY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TASK_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TABLES_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"METADATA_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"FILE_FACTORY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"COMMON\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"ADDONS\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"DATA_STRUCTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"COMMUNICATION\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"FILE_TRANSFER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"}],\"CloudWatchLogGroup\":null,\"CloudWatchLogStream\":null},\"ControlTablesSettings\":{\"historyTimeslotInMinutes\":5,\"ControlSchema\":\"\",\"HistoryTimeslotInMinutes\":5,\"HistoryTableEnabled\":false,\"SuspendedTablesTableEnabled\":false,\"StatusTableEnabled\":false,\"FullLoadExceptionTableEnabled\":false},\"StreamBufferSettings\":{\"StreamBufferCount\":3,\"StreamBufferSizeInMB\":8,\"CtrlStreamBufferSizeInMB\":5},\"ChangeProcessingDdlHandlingPolicy\":{\"HandleSourceTableDropped\":true,\"HandleSourceTableTruncated\":true,\"HandleSourceTableAltered\":true},\"ErrorBehavior\":{\"DataErrorPolicy\":\"LOG_ERROR\",\"EventErrorPolicy\":\"IGNORE\",\"DataTruncationErrorPolicy\":\"LOG_ERROR\",\"DataErrorEscalationPolicy\":\"SUSPEND_TABLE\",\"DataErrorEscalationCount\":0,\"TableErrorPolicy\":\"SUSPEND_TABLE\",\"TableErrorEscalationPolicy\":\"STOP_TASK\",\"TableErrorEscalationCount\":0,\"RecoverableErrorCount\":-1,\"RecoverableErrorInterval\":5,\"RecoverableErrorThrottling\":true,\"RecoverableErrorThrottlingMax\":1800,\"RecoverableErrorStopRetryAfterThrottlingMax\":true,\"ApplyErrorDeletePolicy\":\"IGNORE_RECORD\",\"ApplyErrorInsertPolicy\":\"LOG_ERROR\",\"ApplyErrorUpdatePolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationPolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationCount\":0,\"ApplyErrorFailOnTruncationDdl\":false,\"FullLoadIgnoreConflicts\":true,\"FailOnTransactionConsistencyBreached\":false,\"FailOnNoTablesCaptured\":true},\"ChangeProcessingTuning\":{\"BatchApplyPreserveTransaction\":true,\"BatchApplyTimeoutMin\":1,\"BatchApplyTimeoutMax\":30,\"BatchApplyMemoryLimit\":500,\"BatchSplitSize\":0,\"MinTransactionSize\":1000,\"CommitTimeout\":1,\"MemoryLimitTotal\":1024,\"MemoryKeepTime\":60,\"StatementCacheSize\":50},\"PostProcessingRules\":null,\"CharacterSetSettings\":null,\"LoopbackPreventionSettings\":null,\"BeforeImageSettings\":null,\"FailTaskWhenCleanTaskResourceFailed\":false,\"TTSettings\":{\"EnableTT\":false,\"FailTaskOnTTFailure\":false,\"TTS3Settings\":null,\"TTRecordSettings\":null}}",
"TableMappings": "{\n \"rules\": [\n {\n \"rule-id\": \"783282541\",\n \"rule-name\": \"783282541\",\n \"rule-type\": \"selection\",\n \"rule-action\": \"include\",\n \"object-locator\": {\n \"schema-name\": \"mydata\",\n \"table-name\": \"newtable\"\n },\n \"filters\": []\n },\n {\n \"rule-id\": 1152034124,\n \"rule-name\": \"lkbtwrp3-a00w9q\",\n \"rule-type\": \"transformation\",\n \"rule-target\": \"schema\",\n \"rule-action\": \"rename\",\n \"object-locator\": {\n \"schema-name\": \"mydata\"\n },\n \"value\": \"public\"\n }\n ]\n}",
"ReplicationConfigCreateTime": "2023-07-31T15:28:56.540000+10:00"
}
]
Start the AWS DMS Serverless replication
You start the AWS DMS Serverless replication using the replication config ARN and by setting start-replication-type to start-replication. This launches a serverless replication instance and task which connects to the source and the target. See the following code:
#start the replication (dms serverless task)
aws dms start-replication \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC \
--start-replication-type start-replication
# output
{
"Replication": {
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"Status": "initializing",
"ProvisionData": {
"ProvisionedCapacityUnits": 0,
"IsNewProvisioningAvailable": false
},
"FailureMessages": [],
"ReplicationStats": {
"FullLoadProgressPercent": 0,
"ElapsedTimeMillis": 0,
"TablesLoaded": 0,
"TablesLoading": 0,
"TablesQueued": 0,
"TablesErrored": 0
},
"StartReplicationType": "start-replication",
"ReplicationCreateTime": "2023-07-31T15:28:56.540000+10:00",
"ReplicationUpdateTime": "2023-07-31T15:28:56.540000+10:00"
}
}
# query replications
aws dms describe-replications --query "Replications[?contains(ReplicationConfigIdentifier,'cli')]"
# output
[
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"Status": "preparing_metadata_resources",
"ProvisionData": {
"ProvisionedCapacityUnits": 0,
"IsNewProvisioningAvailable": false
},
"FailureMessages": [],
"ReplicationStats": {
"FullLoadProgressPercent": 0,
"ElapsedTimeMillis": 0,
"TablesLoaded": 0,
"TablesLoading": 0,
"TablesQueued": 0,
"TablesErrored": 0
},
"StartReplicationType": "start-replication",
"ReplicationCreateTime": "2023-07-21T16:11:01.687000+10:00",
"ReplicationUpdateTime": "2023-07-21T16:21:45.586000+10:00"
}
]
Stop AWS DMS Serverless replication
When the migration is complete, you can use stop-replication to stop the AWS DMS Serverless replication. With AWS DMS Serverless replication, when the replication type is set to full load only, then AWS DMS Serverless replication stops by itself after loading all the tables. See the following code:
# stop replication
aws dms stop-replication \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC
# output
{
"Replication":
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"Status": "stopping",
"ProvisionData":
{
"ProvisionState": "provisioned",
"ProvisionedCapacityUnits": 4,
"IsNewProvisioningAvailable": false
},
"FailureMessages": [],
"ReplicationStats":
{
"FullLoadProgressPercent": 100,
"ElapsedTimeMillis": 1988,
"TablesLoaded": 1,
"TablesLoading": 0,
"TablesQueued": 0,
"TablesErrored": 0,
"FreshStartDate": "2023-07-21T16:57:46.566000+10:00",
"StartDate": "2023-07-21T16:57:46.566000+10:00",
"FullLoadStartDate": "2023-07-21T16:57:47.089000+10:00",
"FullLoadFinishDate": "2023-07-21T16:57:49.078000+10:00"
},
"StartReplicationType": "start-replication",
"ReplicationCreateTime": "2023-07-21T16:11:01.687000+10:00",
"ReplicationUpdateTime": "2023-07-21T16:58:17.988000+10:00"
}
}
Modify AWS DMS Serverless replication config
If you want to modify task settings or mapping JSON you can use modify-replication-config. The following example turns off the task logging to CloudWatch:
# modify config to turn off logging
aws dms modify-replication-config \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC \
--replication-settings '{
"TargetMetadata": {
"TargetSchema": "",
"SupportLobs": true,
"FullLobMode": false,
"LobChunkSize": 64,
"LimitedSizeLobMode": true,
"LobMaxSize": 32,
"InlineLobMaxSize": 0,
"LoadMaxFileSize": 0,
"ParallelLoadThreads": 0,
"ParallelLoadBufferSize": 0,
"BatchApplyEnabled": false,
"TaskRecoveryTableEnabled": false,
"ParallelLoadQueuesPerThread": 0,
"ParallelApplyThreads": 0,
"ParallelApplyBufferSize": 0,
"ParallelApplyQueuesPerThread": 0
},
"FullLoadSettings": {
"TargetTablePrepMode": "DROP_AND_CREATE",
"CreatePkAfterFullLoad": false,
"StopTaskCachedChangesApplied": false,
"StopTaskCachedChangesNotApplied": false,
"MaxFullLoadSubTasks": 8,
"TransactionConsistencyTimeout": 600,
"CommitRate": 10000
},
"Logging": {
"EnableLogging": false,
"EnableLogContext": false,
"LogComponents": [
{ "Id": "DATA_STRUCTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "COMMUNICATION", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "IO", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "COMMON", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "FILE_FACTORY", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "FILE_TRANSFER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "REST_SERVER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "ADDONS", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TARGET_LOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TARGET_APPLY", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "SOURCE_UNLOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "SOURCE_CAPTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TRANSFORMATION", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "SORTER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TASK_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "TABLES_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "METADATA_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "PERFORMANCE", "Severity": "LOGGER_SEVERITY_DEFAULT" },
{ "Id": "VALIDATOR_EXT", "Severity": "LOGGER_SEVERITY_DEFAULT" }
],
"CloudWatchLogGroup": null,
"CloudWatchLogStream": null
},
"ControlTablesSettings": {
"historyTimeslotInMinutes": 5,
"ControlSchema": "",
"HistoryTimeslotInMinutes": 5,
"HistoryTableEnabled": false,
"SuspendedTablesTableEnabled": false,
"StatusTableEnabled": false,
"FullLoadExceptionTableEnabled": false
},
"StreamBufferSettings": {
"StreamBufferCount": 3,
"StreamBufferSizeInMB": 8,
"CtrlStreamBufferSizeInMB": 5
},
"ChangeProcessingDdlHandlingPolicy": {
"HandleSourceTableDropped": true,
"HandleSourceTableTruncated": true,
"HandleSourceTableAltered": true
},
"ErrorBehavior": {
"DataErrorPolicy": "LOG_ERROR",
"EventErrorPolicy": "IGNORE",
"DataTruncationErrorPolicy": "LOG_ERROR",
"DataErrorEscalationPolicy": "SUSPEND_TABLE",
"DataErrorEscalationCount": 0,
"TableErrorPolicy": "SUSPEND_TABLE",
"TableErrorEscalationPolicy": "STOP_TASK",
"TableErrorEscalationCount": 0,
"RecoverableErrorCount": -1,
"RecoverableErrorInterval": 5,
"RecoverableErrorThrottling": true,
"RecoverableErrorThrottlingMax": 1800,
"RecoverableErrorStopRetryAfterThrottlingMax": true,
"ApplyErrorDeletePolicy": "IGNORE_RECORD",
"ApplyErrorInsertPolicy": "LOG_ERROR",
"ApplyErrorUpdatePolicy": "LOG_ERROR",
"ApplyErrorEscalationPolicy": "LOG_ERROR",
"ApplyErrorEscalationCount": 0,
"ApplyErrorFailOnTruncationDdl": false,
"FullLoadIgnoreConflicts": true,
"FailOnTransactionConsistencyBreached": false,
"FailOnNoTablesCaptured": true
},
"ChangeProcessingTuning": {
"BatchApplyPreserveTransaction": true,
"BatchApplyTimeoutMin": 1,
"BatchApplyTimeoutMax": 30,
"BatchApplyMemoryLimit": 500,
"BatchSplitSize": 0,
"MinTransactionSize": 1000,
"CommitTimeout": 1,
"MemoryLimitTotal": 1024,
"MemoryKeepTime": 60,
"StatementCacheSize": 50
},
"PostProcessingRules": null,
"CharacterSetSettings": null,
"LoopbackPreventionSettings": null,
"BeforeImageSettings": null,
"FailTaskWhenCleanTaskResourceFailed": false,
"TTSettings": {
"EnableTT": false,
"FailTaskOnTTFailure": false,
"TTS3Settings": null,
"TTRecordSettings": null
}
}'
# output
{
"ReplicationConfig": {
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"ComputeConfig": {
"MaxCapacityUnits": 4,
"MultiAZ": false,
"ReplicationSubnetGroupId": "dmsreplicationsubnetgroup-2b2cdefghijklm7no",
"VpcSecurityGroupIds": [
"sg-1123ab4566cd7ef89"
]
},
"ReplicationSettings": "{\"Logging\":{\"EnableLogging\":true,\"EnableLogContext\":false,\"LogComponents\":[{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"TRANSFORMATION\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"SOURCE_UNLOAD\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"IO\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"TARGET_LOAD\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"PERFORMANCE\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"SOURCE_CAPTURE\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"SORTER\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"REST_SERVER\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"VALIDATOR_EXT\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"TARGET_APPLY\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"TASK_MANAGER\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"TABLES_MANAGER\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"METADATA_MANAGER\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"FILE_FACTORY\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"COMMON\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"ADDONS\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"DATA_STRUCTURE\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"COMMUNICATION\"},{\"Severity\":\"LOGGER_SEVERITY_DEFAULT\",\"Id\":\"FILE_TRANSFER\"}],\"CloudWatchLogGroup\":null,\"CloudWatchLogStream\":null},\"StreamBufferSettings\":{\"StreamBufferCount\":3,\"CtrlStreamBufferSizeInMB\":5,\"StreamBufferSizeInMB\":8},\"ErrorBehavior\":{\"FailOnNoTablesCaptured\":true,\"ApplyErrorUpdatePolicy\":\"LOG_ERROR\",\"FailOnTransactionConsistencyBreached\":false,\"RecoverableErrorThrottlingMax\":1800,\"DataErrorEscalationPolicy\":\"SUSPEND_TABLE\",\"ApplyErrorEscalationCount\":0,\"RecoverableErrorStopRetryAfterThrottlingMax\":true,\"RecoverableErrorThrottling\":true,\"ApplyErrorFailOnTruncationDdl\":false,\"DataTruncationErrorPolicy\":\"LOG_ERROR\",\"ApplyErrorInsertPolicy\":\"LOG_ERROR\",\"EventErrorPolicy\":\"IGNORE\",\"ApplyErrorEscalationPolicy\":\"LOG_ERROR\",\"RecoverableErrorCount\":-1,\"DataErrorEscalationCount\":0,\"TableErrorEscalationPolicy\":\"STOP_TASK\",\"RecoverableErrorInterval\":5,\"ApplyErrorDeletePolicy\":\"IGNORE_RECORD\",\"TableErrorEscalationCount\":0,\"FullLoadIgnoreConflicts\":true,\"DataErrorPolicy\":\"LOG_ERROR\",\"TableErrorPolicy\":\"SUSPEND_TABLE\"},\"ValidationSettings\":{\"ValidationPartialLobSize\":0,\"EnableValidation\":false,\"ThreadCount\":5,\"PartitionSize\":10000,\"RecordFailureDelayLimitInMinutes\":0,\"SkipLobColumns\":false,\"FailureMaxCount\":10000,\"HandleCollationDiff\":false,\"ValidationQueryCdcDelaySeconds\":0,\"ValidationMode\":\"ROW_LEVEL\",\"TableFailureMaxCount\":1000,\"ValidationOnly\":false},\"TTSettings\":{\"TTS3Settings\":null,\"TTRecordSettings\":null,\"FailTaskOnTTFailure\":false,\"EnableTT\":false},\"FullLoadSettings\":{\"CommitRate\":10000,\"StopTaskCachedChangesApplied\":false,\"StopTaskCachedChangesNotApplied\":false,\"MaxFullLoadSubTasks\":8,\"TransactionConsistencyTimeout\":600,\"CreatePkAfterFullLoad\":false,\"TargetTablePrepMode\":\"DROP_AND_CREATE\"},\"TargetMetadata\":{\"ParallelApplyBufferSize\":0,\"ParallelApplyQueuesPerThread\":0,\"ParallelApplyThreads\":0,\"TargetSchema\":\"\",\"InlineLobMaxSize\":0,\"ParallelLoadQueuesPerThread\":0,\"SupportLobs\":true,\"LobChunkSize\":64,\"TaskRecoveryTableEnabled\":false,\"ParallelLoadThreads\":0,\"LobMaxSize\":32,\"BatchApplyEnabled\":false,\"FullLobMode\":false,\"LimitedSizeLobMode\":true,\"LoadMaxFileSize\":0,\"ParallelLoadBufferSize\":0},\"BeforeImageSettings\":null,\"ControlTablesSettings\":{\"HistoryTimeslotInMinutes\":5,\"StatusTableEnabled\":false,\"SuspendedTablesTableEnabled\":false,\"HistoryTableEnabled\":false,\"ControlSchema\":\"\",\"FullLoadExceptionTableEnabled\":false},\"LoopbackPreventionSettings\":null,\"CharacterSetSettings\":null,\"FailTaskWhenCleanTaskResourceFailed\":false,\"ChangeProcessingTuning\":{\"StatementCacheSize\":50,\"CommitTimeout\":1,\"BatchApplyPreserveTransaction\":true,\"BatchApplyTimeoutMin\":1,\"BatchSplitSize\":0,\"BatchApplyTimeoutMax\":30,\"MinTransactionSize\":1000,\"MemoryKeepTime\":60,\"BatchApplyMemoryLimit\":500,\"MemoryLimitTotal\":1024},\"ChangeProcessingDdlHandlingPolicy\":{\"HandleSourceTableDropped\":true,\"HandleSourceTableTruncated\":true,\"HandleSourceTableAltered\":true}}",
"TableMappings": "{\n \"rules\": [\n {\n \"rule-id\": \"783282541\",\n \"rule-name\": \"783282541\",\n \"rule-type\": \"selection\",\n \"rule-action\": \"include\",\n \"object-locator\": {\n \"schema-name\": \"mydata\",\n \"table-name\": \"newtable\"\n },\n \"filters\": []\n },\n {\n \"rule-id\": 1152034124,\n \"rule-name\": \"lkbtwrp3-a00w9q\",\n \"rule-type\": \"transformation\",\n \"rule-target\": \"schema\",\n \"rule-action\": \"rename\",\n \"object-locator\": {\n \"schema-name\": \"mydata\"\n },\n \"value\": \"public\"\n }\n ]\n}",
"ReplicationConfigCreateTime": "2023-07-21T16:11:01.687000+10:00",
"ReplicationConfigUpdateTime": "2023-07-21T16:55:58.862000+10:00"
}
}
Resume or reload the AWS DMS Serverless replication
If you need to resume the task from where it last stopped, or if you want to reload the target fresh, use the following code:
# resume replication
aws dms start-replication \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC \
--start-replication-type resume-processing
# output
{
"Replication":
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"Status": "initializing",
"ProvisionData":
{
"ProvisionState": "provisioned",
"ProvisionedCapacityUnits": 4,
"IsNewProvisioningAvailable": false
},
"FailureMessages": [],
"ReplicationStats":
{
"FullLoadProgressPercent": 100,
"ElapsedTimeMillis": 242,
"TablesLoaded": 1,
"TablesLoading": 0,
"TablesQueued": 0,
"TablesErrored": 0,
"FreshStartDate": "2023-07-31T16:10:36.974000+10:00",
"StartDate": "2023-07-31T16:10:36.974000+10:00",
"StopDate": "2023-07-31T16:13:47.002000+10:00",
"FullLoadStartDate": "2023-07-31T16:10:38.977000+10:00",
"FullLoadFinishDate": "2023-07-31T16:10:39.220000+10:00"
},
"StartReplicationType": "resume-processing",
"RecoveryCheckpoint": "checkpoint:V1#1#mysql-bin-changelog.009875:153:-1:153:0#0#0#*#0#0",
"ReplicationCreateTime": "2023-07-31T15:28:56.540000+10:00",
"ReplicationUpdateTime": "2023-07-31T16:14:40.753000+10:00"
}
}
# reload target
aws dms start-replication \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC \
--start-replication-type reload-target
# output
{
"Replication":
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"Status": "initializing",
"ProvisionData":
{
"ProvisionState": "provisioned",
"ProvisionedCapacityUnits": 4,
"IsNewProvisioningAvailable": false
},
"FailureMessages": [],
"ReplicationStats":
{
"FullLoadProgressPercent": 100,
"ElapsedTimeMillis": 281,
"TablesLoaded": 1,
"TablesLoading": 0,
"TablesQueued": 0,
"TablesErrored": 0,
"FreshStartDate": "2023-07-31T16:06:35.627000+10:00",
"StartDate": "2023-07-31T16:06:35.627000+10:00",
"StopDate": "2023-07-31T16:09:55.643000+10:00",
"FullLoadStartDate": "2023-07-31T16:06:37.834000+10:00",
"FullLoadFinishDate": "2023-07-31T16:06:38.115000+10:00"
},
"StartReplicationType": "reload-target",
"RecoveryCheckpoint": "checkpoint:V1#3#mysql-bin-changelog.009874:487:-1:514:42408507080932:-:0#0#0#*#0#0",
"ReplicationCreateTime": "2023-07-31T15:28:56.540000+10:00",
"ReplicationUpdateTime": "2023-07-31T16:10:12.123000+10:00"
}
}
Reload a specific table of the AWS DMS Serverless replication
If you need to reload a specific table while the AWS DMS Serverless replication is running (only applicable if the replication type is either Full Load and CDC or CDC only), use the following code:
# Reload a single table while the task is running
aws dms reload-replication-tables --replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replicationconfig:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC --tables-to-reload "SchemaName=mydata,TableName=newtable"
# output
{
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC"
}
Check table statistics of the AWS DMS Serverless replication
If you need to check the table statistics of the tables being migrated, use the following code:
# check table statistics
aws dms describe-replication-table-statistics \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC
# output
{
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"ReplicationTableStatistics":
[
{
"SchemaName": "mydata",
"TableName": "newtable",
"Inserts": 0,
"Deletes": 0,
"Updates": 0,
"Ddls": 0,
"AppliedInserts": 0,
"AppliedDeletes": 0,
"AppliedUpdates": 0,
"AppliedDdls": 0,
"FullLoadRows": 29,
"FullLoadCondtnlChkFailedRows": 0,
"FullLoadErrorRows": 0,
"FullLoadStartTime": "2023-07-31T16:10:38.977000+10:00",
"FullLoadEndTime": "2023-07-31T16:10:39.220000+10:00",
"FullLoadReloaded": false,
"LastUpdateTime": "2023-07-31T16:10:42.116000+10:00",
"TableState": "Table completed",
"ScheduledForReload": false,
"ValidationPendingRecords": 0,
"ValidationFailedRecords": 0,
"ValidationSuspendedRecords": 0,
"ValidationState": "Not enabled"
}
]
}
Clean up post-migration
When the migration is complete, you can clean up the resources after stopping the replication:
# cleanup
# first stop the task
aws dms stop-replication \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC
# output
{
"Replication":
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"Status": "stopping",
"ProvisionData":
{
"ProvisionState": "provisioned",
"ProvisionedCapacityUnits": 4,
"IsNewProvisioningAvailable": false
},
"FailureMessages": [],
"ReplicationStats":
{
"FullLoadProgressPercent": 100,
"ElapsedTimeMillis": 1924,
"TablesLoaded": 1,
"TablesLoading": 0,
"TablesQueued": 0,
"TablesErrored": 0,
"FreshStartDate": "2023-07-31T16:06:35.627000+10:00",
"StartDate": "2023-07-31T16:06:35.627000+10:00",
"FullLoadStartDate": "2023-07-31T16:06:36.191000+10:00",
"FullLoadFinishDate": "2023-07-31T16:06:38.115000+10:00"
},
"StartReplicationType": "start-replication",
"ReplicationCreateTime": "2023-07-31T15:28:56.540000+10:00",
"ReplicationUpdateTime": "2023-07-31T16:07:40.251000+10:00"
}
}
# delete replication config
aws dms delete-replication-config \
--replication-config-arn arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC
# output
{
"ReplicationConfig":
{
"ReplicationConfigIdentifier": "climysql2psql",
"ReplicationConfigArn": "arn:aws:dms:ap-southeast-2:123456789012:replication-config:ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC",
"SourceEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"TargetEndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"ReplicationType": "full-load-and-cdc",
"ComputeConfig":
{
"MaxCapacityUnits": 4,
"MultiAZ": false,
"ReplicationSubnetGroupId": "dmsreplicationsubnetgroup-2b2cdefghijklm7no",
"VpcSecurityGroupIds": [
"sg-1123ab4566cd7ef89"
]
},
"ReplicationSettings": "{\"TargetMetadata\":{\"TargetSchema\":\"\",\"SupportLobs\":true,\"FullLobMode\":false,\"LobChunkSize\":64,\"LimitedSizeLobMode\":true,\"LobMaxSize\":32,\"InlineLobMaxSize\":0,\"LoadMaxFileSize\":0,\"ParallelLoadThreads\":0,\"ParallelLoadBufferSize\":0,\"BatchApplyEnabled\":false,\"TaskRecoveryTableEnabled\":false,\"ParallelLoadQueuesPerThread\":0,\"ParallelApplyThreads\":0,\"ParallelApplyBufferSize\":0,\"ParallelApplyQueuesPerThread\":0},\"FullLoadSettings\":{\"TargetTablePrepMode\":\"DROP_AND_CREATE\",\"CreatePkAfterFullLoad\":false,\"StopTaskCachedChangesApplied\":false,\"StopTaskCachedChangesNotApplied\":false,\"MaxFullLoadSubTasks\":8,\"TransactionConsistencyTimeout\":600,\"CommitRate\":10000},\"Logging\":{\"EnableLogging\":true,\"EnableLogContext\":false,\"LogComponents\":[{\"Id\":\"TRANSFORMATION\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_UNLOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"IO\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_LOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"PERFORMANCE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_CAPTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SORTER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"REST_SERVER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"VALIDATOR_EXT\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_APPLY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TASK_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TABLES_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"METADATA_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"FILE_FACTORY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"COMMON\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"ADDONS\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"DATA_STRUCTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"COMMUNICATION\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"FILE_TRANSFER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"}],\"CloudWatchLogGroup\":\"dms-serverless-replication-ABCXXDEFGHIJJXABC4ABC2ABCABCVAB2TNQ3ABC\",\"CloudWatchLogStream\":\"dms-serverless-serv-res-id-1690783496649-cvz\"},\"ControlTablesSettings\":{\"historyTimeslotInMinutes\":5,\"ControlSchema\":\"\",\"HistoryTimeslotInMinutes\":5,\"HistoryTableEnabled\":false,\"SuspendedTablesTableEnabled\":false,\"StatusTableEnabled\":false,\"FullLoadExceptionTableEnabled\":false},\"StreamBufferSettings\":{\"StreamBufferCount\":3,\"StreamBufferSizeInMB\":8,\"CtrlStreamBufferSizeInMB\":5},\"ChangeProcessingDdlHandlingPolicy\":{\"HandleSourceTableDropped\":true,\"HandleSourceTableTruncated\":true,\"HandleSourceTableAltered\":true},\"ErrorBehavior\":{\"DataErrorPolicy\":\"LOG_ERROR\",\"EventErrorPolicy\":\"IGNORE\",\"DataTruncationErrorPolicy\":\"LOG_ERROR\",\"DataErrorEscalationPolicy\":\"SUSPEND_TABLE\",\"DataErrorEscalationCount\":0,\"TableErrorPolicy\":\"SUSPEND_TABLE\",\"TableErrorEscalationPolicy\":\"STOP_TASK\",\"TableErrorEscalationCount\":0,\"RecoverableErrorCount\":-1,\"RecoverableErrorInterval\":5,\"RecoverableErrorThrottling\":true,\"RecoverableErrorThrottlingMax\":1800,\"RecoverableErrorStopRetryAfterThrottlingMax\":true,\"ApplyErrorDeletePolicy\":\"IGNORE_RECORD\",\"ApplyErrorInsertPolicy\":\"LOG_ERROR\",\"ApplyErrorUpdatePolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationPolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationCount\":0,\"ApplyErrorFailOnTruncationDdl\":false,\"FullLoadIgnoreConflicts\":true,\"FailOnTransactionConsistencyBreached\":false,\"FailOnNoTablesCaptured\":true},\"ChangeProcessingTuning\":{\"BatchApplyPreserveTransaction\":true,\"BatchApplyTimeoutMin\":1,\"BatchApplyTimeoutMax\":30,\"BatchApplyMemoryLimit\":500,\"BatchSplitSize\":0,\"MinTransactionSize\":1000,\"CommitTimeout\":1,\"MemoryLimitTotal\":1024,\"MemoryKeepTime\":60,\"StatementCacheSize\":50},\"PostProcessingRules\":null,\"CharacterSetSettings\":null,\"LoopbackPreventionSettings\":null,\"BeforeImageSettings\":null,\"FailTaskWhenCleanTaskResourceFailed\":false,\"TTSettings\":{\"EnableTT\":false,\"FailTaskOnTTFailure\":false,\"TTS3Settings\":null,\"TTRecordSettings\":null}}",
"TableMappings": "{\n \"rules\": [\n {\n \"rule-id\": \"783282541\",\n \"rule-name\": \"783282541\",\n \"rule-type\": \"selection\",\n \"rule-action\": \"include\",\n \"object-locator\": {\n \"schema-name\": \"mydata\",\n \"table-name\": \"newtable\"\n },\n \"filters\": []\n },\n {\n \"rule-id\": 1152034124,\n \"rule-name\": \"lkbtwrp3-a00w9q\",\n \"rule-type\": \"transformation\",\n \"rule-target\": \"schema\",\n \"rule-action\": \"rename\",\n \"object-locator\": {\n \"schema-name\": \"mydata\"\n },\n \"value\": \"public\"\n }\n ]\n}",
"ReplicationConfigCreateTime": "2023-07-31T15:28:56.540000+10:00",
"ReplicationConfigUpdateTime": "2023-07-31T16:04:56.847000+10:00"
}
}
# Delete the source endpoint
aws dms delete-endpoint \
--endpoint-arn arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F
# output
{
"Endpoint":
{
"EndpointIdentifier": "climysqlsource",
"EndpointType": "SOURCE",
"EngineName": "mysql",
"EngineDisplayName": "MySQL",
"Username": "dbuser",
"ServerName": "rds-mysqldb-3f346432534.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Port": 3306,
"TransferFiles": false,
"ReceiveTransferredFiles": false,
"Status": "deleting",
"KmsKeyId": "arn:aws:kms:ap-southeast-2:123456789012:key/ab12cd3e-fg45-6hij-789k-l0l1m234n5o6",
"EndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:ABCD4E67FGHIJJKLMNOPQRSTUUVWXY2ZABCDE3F",
"SslMode": "none",
"MySQLSettings":
{
"Port": 3306,
"ServerName": "rds-mysqldb-3f346432534.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Username": "dbuser"
}
}
}
# Delete the target endpoint
aws dms delete-endpoint \
--endpoint-arn arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA
# output
{
"Endpoint":
{
"EndpointIdentifier": "clipsqltarget",
"EndpointType": "TARGET",
"EngineName": "postgres",
"EngineDisplayName": "PostgreSQL",
"Username": "dbuser",
"ServerName": "rds-postgresql-cba9abc9defg.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Port": 5432,
"DatabaseName": "mydata",
"TransferFiles": false,
"ReceiveTransferredFiles": false,
"Status": "deleting",
"KmsKeyId": "arn:aws:kms:ap-southeast-2:123456789012:key/ab12cd3e-fg45-6hij-789k-l0l1m234n5o6",
"EndpointArn": "arn:aws:dms:ap-southeast-2:123456789012:endpoint:HG7FED2CB5AZ7YXWVUTSRQPO3M3LKJIHGFED4CBA",
"SslMode": "none",
"PostgreSQLSettings":
{
"DatabaseName": "mydata",
"Port": 5432,
"ServerName": "rds-postgresql-cba9abc9defg.abc2ds23sf2.ap-southeast-2.rds.amazonaws.com",
"Username": "dbuser"
}
}
}
Conclusion
In this post, we showed how to run an AWS DMS Serverless migration from MySQL to PostgreSQL using the AWS CLI.
After you have set up an AWS DMS Serverless replication, you can attempt to establish a new replication with the type set as change data capture (CDC), specifying a particular starting CDC position. Make sure to add comments detailing the changes you’ve made.
Remember to back up your data before starting the migration process and thoroughly test the application after the migration to confirm that everything works as expected. If you have questions or feedback, leavem them in the comments section.
About the Authors
Subhash Raut is a Database Migration Specialist Solutions Architect at AWS. He works on challenges related to database migrations and works closely with customers to help them realize the true potential of AWS DMS. He has helped customers to migrate databases to the AWS Cloud by using AWS DMS and the AWS SCT.
Dilip Sasikumar is a Technical Account Manager with Amazon Web Services and is based in Melbourne, Australia. He works with enterprise support customers providing assistance on operational excellence, cost optimization and security. Dilip is also a database enthusiast with keen interest in relational databases and database migrations.
Viqash Adwani is a Sr. Database Specialty Architect with Amazon Web Services. He works with internal and external Amazon customers to build secure, scalable, and resilient architectures in the AWS Cloud and help customers perform migrations from on-premises databases to Amazon RDS and Amazon Aurora databases.