In four tutorials, we are exploring migrating a MySQL database to a PostgreSQL database using the AWS Database Migration Service (DMS). We started by creating an IAM user for DMS in the first tutorial, “Migrating MySQL to PostgreSQL on AWS RDS, Part 1.” Subsequently, we discussed creating instances of MySQL and PostgreSQL on AWS Relational Database Service (RDS) in the second tutorial, “Migrating MySQL to PostgreSQL on AWS RDS, Part 2.” In the third tutorial, “Migrating MySQL to PostgreSQL on AWS RDS, Part 3,” we created a DMS migration. In this final tutorial, we shall discuss running the DMS migration to perform the migration.
This tutorial has the following sections:
- Running the DMS Migration Task
- Exploring the Migrated Database
- Exploring CloudWatch Logs
- Limitations in MySQL to PostgreSQL Migration with DMS
- Conclusion
Running the DMS Migration Task
After the task has been created, the Status should become Ready, as shown in Figure 1. Click Start/Resume to start the task.
Figure 1: Start/Resume
The status should become Starting, as shown in Figure 2.
Figure 2: Task Starting
When the task has established connection with the replication instance and the database endpoints and applied, the table mappings the status becomes Running, as shown in Figure 3.
Figure 3: Status Running
The migration is run as configured and, after the migration has completed successfully, the Status becomes Load complete, as shown in Figure 4. The Tables loaded column indicates the number of tables loaded.
Figure 4: Status Load complete
For the tables that have been migrated, click the Table Statistics tab, as shown in Figure 5. Table Statistics, including the Schema, Table, Status, # of Inserts, Deletes, Updates, DDLs, Full Load Rows, and Total get listed.
Figure 5: Table Statistics
If the source database table does not include tables, the Full Load Rows is 0. If a source table has rows, the Full Load Rows indicates the # of rows as shown for some of the rows in Figure 6.
Figure 6: Full Load Rows for some tables is 0, whereas for other tables, the # of rows loaded
Exploring the Migrated Database
Having migrated MySQL database schemas and tables to PostgreSQL, list the schemas in PostgreSQL CLI with the dn command. The schemas migrated get listed, as shown in Figure 7. The public schema already existed before the migration.
Figure 7: Schemas migrated from MySQL to PostgreSQL Database
The DMS migration converts the table column types automatically. A secondary index gets added for a primary key, created automatically in the table mapping.
Table data also gets migrated, as shown for the mysql.user table in Figure 8.
Figure 8: Table Data is also migrated
When migrating from one database type to another (heterogeneous migration), the data type mappings are made by DMS automatically. An intermediate DMS Data Type is used during a migration. When migrating from MySQL Database to PostgreSQL, first the MySQL data types are migrated to the DMS data type using the built-in mappings for MySQL database as source database. Subsequently, the DMS data type is mapped to PostgreSQL data types, again using built-in mappings for PostgreSQL as the target database. RDS-related tables in the MySQL database on RDS also get migrated to PostgreSQL database, such as the table “mysql”.”rds_replication_status” shown in Figure 9.
Figure 9: Migrated Data for a RDS Information Table
Exploring CloudWatch Logs
If logging is configured, as we did, the logs may be monitored from the Logs tab, as shown in Figure 10. Click the task link to list the CloudWatch logs.
Figure 10: Logs
The CloudWatch logs get listed (see Figure 11).
Figure 11: CloudWatch Logs
As a CloudWatch logs message in Figure 12 indicates, “All tables are loaded. Full load only task is stopped”.
Figure 12: All tables are loaded
The number of records transferred are also listed, as for the sys.sys_config table in Figure 13.
Figure 13: Log entry for a Database Table includes # of records transferred
CloudWatch logs may be used to debug a migration task run. The error messages are listed for the tables that did not migrate successfully in the CloudWatch logs.
Limitations in MySQL to PostgreSQL Migration with DMS
Migrating from MySQL database to PostgreSQL database with DMS is not without limitations. The DMS service itself has limits per AWS user account. Other DMS limitations could be specific to a database. DMS source data types for MySQL do not include the UTF-8 4 byte character set (utf8mb4). The character set supported by DMS is Code Page 1252 Windows Latin 1 (ANSI) and characters not in the character set could generate an error during a migration. Some limitations exist for MySQL database as DMS source. The only limitation for using PostgreSQL as a DMS target database is that the JSON data type is converted to Native CLOB data type.
Conclusion
In four tutorials, we discussed migrating a MySQL database instance on RDS to a Postgres database instance on RDS.
