dcsimg
September 26, 2018
Hot Topics:

Migrating MySQL to PostgreSQL on AWS RDS, Part 4

  • August 24, 2018
  • By Deepak Vohra
  • Send Email »
  • More Articles »

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

After the task has been created, the Status should become Ready, as shown in Figure 1. Click Start/Resume to start the task.

Start/Resume
Figure 1: Start/Resume

The status should become Starting, as shown in Figure 2.

Task Starting
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.

Status Running
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.

Status Load complete
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.

Table Statistics
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.

Full Load Rows for some tables is 0, whereas for other tables, the # of rows loaded
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.

Schemas migrated from MySQL to PostgreSQL Database
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.

Table Data is also migrated
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.

Migrated Data for a RDS Information Table
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.

Logs
Figure 10: Logs

The CloudWatch logs get listed (see Figure 11).

CloudWatch Logs
Figure 11: CloudWatch Logs

As a CloudWatch logs message in Figure 12 indicates, "All tables are loaded. Full load only task is stopped".

All tables are loaded
Figure 12: All tables are loaded

The number of records transferred are also listed, as for the sys.sys_config table in Figure 13.

Log entry for a Database Table includes # of records transferred
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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

Thanks for your registration, follow us on our social networks to keep up-to-date