AWS DMS (Database Migration Service) is a powerful tool for database migrations, especially between AWS RDS databases. It is well suited for heterogeneous replication (e.g., Oracle to MySQL), but you can also use DMS to create an exact copy of your RDS for the purpose of database relocation without downtime. However, as straightforward as DMS is, you must be aware of some prerequisites to manage a successful migration.

This guide will show you how I managed to migrate dozens of RDS PostgreSQL and MySQL databases to another AWS account and what I learned from this experience.

1. Define your replication method

DMS provides 3 types of replication methods:

• Full Load: When using this method, the database is migrated all at once. It’s useful when there are no writes to the source database during the replication process.
• Change Data Capture (CDC): This method only replicates changes to the target database without migrating the initial data.
• Full Load and CDC: This method performs a Full Load on each table. Once the table is loaded, it then performs the Change Data Capture on the table. Any changes made during the Full Load are replicated before the CDC phase. Note that this process is per table, not per database.

2. Define the target table preparation mode to “truncate”

By default, AWS DMS uses the ‘drop tables on target’ mode, which drops the target tables and recreates only certain objects (tables, primary keys, but not all indexes). For a full migration with the goal of pointing the production application to the target database, I strongly suggest using the ‘truncate’ mode.

The ‘truncate’ mode deletes all the target rows without modifying the table definition or recreating the table. Therefore, you will need to manually create all the tables on the target database before launching the replication task. This method ensures you obtain an exact copy of your database, including all indexes, non-primary key constraints, etc., just like the source. To achieve this, you can export the database definition using tools like mysqldump for MySQL (https://dev.mysql.com/doc/refman/8.0/en/mysqldump-definition-data-dumps.html) and apply the SQL file to the target database. Alternatively, you can use the table definition if it’s versioned in your application’s Git repository and used with tools like Flyway (https://flywaydb.org/).

3. Ensure your collation and charset are identical

When creating an RDS database, it will setup the collation and charset depending on the version of the database. For instance, MySQL 5.7 uses latin1 and latin1_swedish_ci by default but for MySQL 8.0 it’s utf8mb4 and utf8mb4_0900_ai_ci . This can lead to text encoding problem if your target database is not set up with the exact same collation and charset. Use this command to find out the default collation and charset of your database (MySQL):

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '<your schema>';

4. Speed up your migration by deleting indexes.

You may have set up indexes to speed up reads on your database. These indexes are computed and stored when you add, update, or delete rows in your table. In the case where your source database has tables with millions of rows, it can significantly slow down the ‘full load’ process, extending it to several days. This is because a ‘full load’ involves a massive insert operation into your target database, with each insertion incurring CPU and disk I/O overhead. In order to speed up the ‘full load’ process, ensure you delete all primary/secondary indexes and referential integrity constraints, only on your target database, before starting the replication task.

After the ‘full load’ process is completed, don’t forget to add any missing indexes. The ‘CDC’ process requires these indexes.

More info here: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.Performance

5. Check the replication behavior with LOB

Large object Blob can lead to some issues with DMS. By default, DMS uses the ‘limited LOB’ mode, which only replicates the first 32 KB to speed up the ‘full load’ process. If you have LOBs larger than 32 KB, they will be truncated. Always prefer the ‘full LOB’ mode if you are unsure about the maximum size of LOBs in your tables.

A second issue I encountered with LOBs is how DMS inserts them. It will insert the row with all data except the LOB data. Then it will update the row with the LOB data. Always check if your LOB columns are nullable; it will fail if they are not.

More info here:

Best practices for AWS Database Migration Service

6. Exclude generated columns

Generated columns are columns that are evaluated when rows are read (virtual mode) or when rows are inserted or updated (stored mode). Since you cannot directly write data to these columns, you must use the DMS Schema Conversion Tool (SCT) to create a transformation rule and filter for these columns. More info here: https://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-transformation-rules.html

7. Debug common migration errors

After launching your replication task, you may encounter errors during the process.

• “Deadlock found when trying to get lock; try restarting transaction”: Ensure your application doesn’t write on the target database while replicating.
• “Invalid JSON text: “The document is empty.” at position 0 in value for column ‘xxx’ “, “Invalid JSON text: “Missing a colon after a name of object member.” at position y”: Check your LOB replication settings. Also make sure the LOB column can be nullable.
• “Cannot drop table ‘’. DROP TABLE is not currently supported”: currently DMS doesn’t support all MySQL DDL (data definition language) statements such as DROP TABLE, RENAME TABLE. Make sure your application doesn’t apply these statements to the source database during CDC process. More info on MySQL limitations here: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.MySQL.html#CHAP_Target.MySQL.Limitations

8. Ensure you drop replication slot after stopping CDC task (PostgreSQL)

DMS uses a replication_slot when you use PostgreSQL source databases. When you stop the CDC task, it will retain all updates on the source database in order to resume the CDC task if desired.

If you forget to drop the replication slot, the disk usage of your source database will quickly grow:

To prevent this, enable WAL heartbeat feature in DMS: set “HeartbeatEnable” to true. (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.ConnectionAttrib)

To clear the replication slot after stopping the CDC task use these commands:

SELECT * FROM pg_replication_slots ;
SELECT pg_drop_replication_slot('slot_name'); 

Final thoughts

AWS DMS is a very powerful tool for migrating different database engines. However, one must be very vigilant regarding the options of replication tasks, which can lead to migration failures. I recommend reading the AWS DMS documentation for each source and destination database engine, as behaviors may vary.

DMS best practices: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

Source database engine doc: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.html

Target database engine doc:

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.html

8 things to know when using AWS DMS for DB relocation was originally published in ekino-france on Medium, where people are continuing the conversation by highlighting and responding to this story.