Do you want to migrate data from one type of DB engine (MySQL) to another type of DB engine(PostgreSQL) with little or no downtime? In today’s fast-paced environment, application, and database availability must be considered 24 hours a day, seven days a week. Some of your applications may be unable to tolerate substantial downtime while migrating data from one type of DB engine to another type of DB engine. The ideal solution in this case is AWS Relational Database Service (RDS). Let’s assume that you are running a streaming application that is hosted in an EC2 instance or Lambda functions in the AWS cloud. Currently, you are using the RDS MySQL database engine. Now you are going to add new functionalities which require long-running queries to handle JSON, XML, etc. In this case, you can migrate from the existing RDS MySQL database to RDS PostgreSQL to take advantage of the PostgreSQL database. You can do this migration easily with little or no downtime using AWS DMS.
AWS Database Migration Service (AWS DMS) is a cloud service that is one of the easiest ways to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. In this blog, we will demonstrate how to migrate the RDS MySQL database to the RDS PostgreSQL database using AWS Database Migration Service.
AWS Database Migration Service(DMS)
The AWS Database Migration Service (AWS DMS) is a cloud service that makes migrating relational databases, data warehouses, NoSQL databases, and other data storage simple. AWS DMS may be used to migrate data into the AWS Cloud, between on-premises instances (through an AWS Cloud setup), and between cloud and on-premises deployments. It also helps us to migrate databases of different Database Engines.
Hands-On
Prerequisites:
To continue this hands-on, you should be having followings:
- An RDS DB instance running with MySQL DB Engine.
- An RDS DB instance running with PostgreSQL DB Engine.
In this hands-on, we will do following:
- Setup a Replication Instance
- Creating an Endpoint for Source Database (RDS MySQL)
- Creating an Endpoint for Target Database (RDS PostgreSQL)
- Creating Database Migration Task
Setup a Replication Instance
To setup a replication instance, go to AWS DMS console
Click create replication instance
We have to configure the replication instance
Enter the name for the replication instance.
For the Instance class, choose the preferred instance type and the Engine Version be a default.
Enter the size of the storage required for the replication instance.
Choose the VPC where the replication instance should be created.
If you want the replication instance to be deployed in Multi AZ mode, select it.
Under advanced security and network configuration,
Select the default VPC subnet group.
For the VPC security group(s), choose the default security group.
The default KMS master key will be used.
And finally click create.
The Replication instance has been created.
The Replication instance is successfully created and is available.
Creating Endpoint for Source Database (MySQL)
In the navigation pane, click endpoints and then choose Create endpoint.
For the endpoint type, choose Source endpoint.
Check, select RDS DB Instance and then choose the Source RDS DB Instance which is MySQL.
For the Endpoint configuration, enter the Endpoint identifier (It is fetched by default after choosing the RDS DB instance).
For Source Engine, choose MySQL.
For access to the endpoint database, select Provide access information manually.
Which will automatically fetch the source DB endpoint, Port, and the User name.
Make the changes if required and Enter the Password of the MySQL RDs DB instance.
We can test the connectivity to the RDS MySQL DB instance, by doing so.
Expand test endpoint connection (optional)
Choose the VPC and select the Replication instance then click Run test.
If all the provided information is correct, then the status should be successful.
And finally click create Endpoint.
The Endpoint for the RDS MySQL instance is created and is Active.
Creating Endpoint for Target Database (PostgreSQL)
Click Create endpoint.
For the Endpoint type, choose Target endpoint.
Select RDS DB Instance and then select the Target RDS DB Instance which is PostgreSQL.
For the Endpoint configuration, enter the Endpoint identifier (It is fetched by default after choosing the RDS DB instance).
For the Target engine, choose PostgreSQL.
Access to the endpoint database, choose to provide access information manually.
It automatically fetches the Target DB endpoint, Port, and User name.
Make the required changes if any and Enter the Password of the PostgreSQL RDS DB instance.
And Enter the Target Database name.
We can test the connectivity to the RDS PostgreSQL DB instance, to expand test endpoint connection (optional).
Choose the VPC and the Replication instance and then click Run test.
If the provided information is correct, the connectivity test should be successful.
And at last click Create endpoint.
The Endpoint for the RDS PostgreSQL instance is created successfully and is Active.
Creating Database Migration Task
To create a migration task, in the navigation pane, click Database migration tasks.
Click create task, enter the name for the task.
Select the Replication instance that you have created.
Select the Source database endpoint and the target database endpoint.
For the Migration type, select Migrate existing data.
As we perform migration from MySQL to PostgreSQL engine, the AWS schema conversion tool will automatically convert the database scheme.
Under Task settings,
For the Target table preparation mode, choose Do nothing
To Include LOB columns in replication, choose Limited LOB mode and the Maximum LOB size be 32KB.
For Table mappings, add a new selection rule.
For Migration task startup configuration, choose Automatically on create.
And finally click create task which will start the migration process immediately.
The Database migration task creation is in progress.
The Migration task is ready now.
And you can see the migration process has started immediately.
Conclusion
AWS Database Migration Service supports homogeneous migrations such as MySQL to MySQL, and also heterogeneous migrations between different database platforms, such as MySQL to PostgreSQL. In this blog, we have successfully migrated from the RDS MySQL DB instance to the RDS PostgreSQL DB instance using AWS Database Migration Service. The existing migration task can be used for replicating new data inserted into the MySQL DB engine. You can use AWS DMS to migrate your data to and from the most widely used commercial and open-source databases such as Oracle, Amazon Redshift, Microsoft SQL Server, Amazon Aurora, PostgreSQL, MariaDB, and MySQL. Stay tuned to keep getting all updates about our upcoming new blogs on AWS and relevant technologies.
Meanwhile …
Keep Exploring -> Keep Learning -> Keep Mastering
This blog is part of our effort towards building a knowledgeable and kick-ass tech community. At Workfall, we strive to provide the best tech and pay opportunities to AWS-certified talents. If you’re looking to work with global clients, build kick-ass products while making big bucks doing so, give it a shot at workfall.com/partner today.
