How to Migrate MySQL to PostgreSQL in AWS?

Compared to other database engines migration from MySQL to PostgreSQL can be challenging, especially if you want to minimize downtime or have a desire to preserve data integrity. AWS offers a variety of tools to help with database migration, such as the Database Migration Service (DMS).

In this article, we are going to explain in detail how to perform MySQL to PostgreSQL migration on AWS using DMS where for Amazon RDS we will be setting up the source and target environments and defining migration tasks. With this PostgreSQL migration guide at your side, your migration will be as swift and hassle-free as possible, whether you are simply taking advantage of PostgreSQL’s more complex functionalities or seeking to enhance your system’s capacity.

Prerequisites

• Amazon RDS Instances: Ensure you have two databases in Amazon RDS—one for MySQL and one for PostgreSQL.
  
• MySQL Table Creation: Use MySQL Workbench or any other MySQL client to create the necessary tables in your MySQL database.

How to migrate MySQL to PostgreSQL in AWS

We will be creating a DMS instance then we will be creating endpoints and then replication tasks with this replication will happen from source to destination database. So basically DMS service needs a platform or infrastructure on which these replication tasks will run. So we have to create a DMS instance and then create the endpoints for the source and target database and then replication task all rules we can define here.

Note: In AWS, in services, amazon RDS, create two databases named as a database of My SQL and database-1 of PostgreSQL. Make sure that you have created a table in MySQL Workbench.

Step 1: Create a DMS Replication Instance

1. Open AWS DMS:

• Navigate to the AWS Management Console and select "Database Migration Service" from the services menu.

2. Create a Replication Instance:

• Click on "Replication Instances" in the left sidebar.
• Click on "Create replication instance".

Fill in the following details:

• Name: Give a unique name to your replication instance.
• Description: Provide a short description.
• Instance Class: Select dms.t3.micro.
• Engine Version: Choose 3.4.4.
• VPC: Select the appropriate Virtual Private Cloud (VPC).
• Click on the "Create" button to create the replication instance.

Fill up details:

Create Replication Instance:

Step 2: Create Endpoints

Source Endpoint

1. Go to Endpoints:

• Click on "Endpoints" in the left sidebar.
• Click on "Create endpoint".

2. Configure Source Endpoint:

• Endpoint Identifier: Enter a name for your source endpoint.
• Source Engine: Select "Microsoft SQL Server".
• Access Information: Check "Provide access information manually".

Fill in the details from your source database (MySQL). Go to your database-1 which is the source database and pick up the details from there like endpoint and port.

• Server Name: Copy the endpoint from your databse-1 and paste it here.
• Port: Enter the port number (default is 3306).
• Username: Enter your MySQL username.
• Password: Enter your MySQL password.
• Database Name: Enter the name of your MySQL database.
• Click "Create endpoint".

Target Endpoint

1. Go to Endpoints:

• Click on "Endpoints" in the left sidebar.
• Click on "Create endpoint".

2. Configure Target Endpoint:

• Endpoint Identifier: Enter a name for your target endpoint.
• Target Engine: Select "Amazon Aurora PostgreSQL" (if using Aurora PostgreSQL) or "PostgreSQL" for standard PostgreSQL.
• Access Information: Check "Provide access information manually".
• Fill in the details from your target database (PostgreSQL).
• Server Name: Enter the endpoint of your PostgreSQL RDS instance.
• Port: Enter the port number (default is 5432).
• Username: Enter your PostgreSQL username.
• Password: Enter your PostgreSQL password.
• Database Name: Enter the name of your PostgreSQL database.
• Click "Create endpoint".

Step 3: Test the Connections

1. Test Source Endpoint Connection:

• In the Endpoints section, select the source endpoint.
• Click on "Test connection" from the dropdown.

• Click "Run test"

2. Test Target Endpoint Connection:

• In the Endpoints section, select the target endpoint.
• Click on "Test connection" from the dropdown.
• Click "Run test".

Step 4: Create a Database Migration Task

1. Go to Database Migration Tasks

• Click on "Database migration tasks" in the left sidebar.
• Click on "Create task".

2. Configure Migration Task

• Task Identifier: Provide a name for your migration task.

Database Migration Task:

• Migration Type: Choose "Migrate existing data and replicate ongoing changes" for minimal downtime migration.
• Selection Roles: Fill in all the details. It contains the details of the source.

• Transformation Roles: Fill in all the details. It contains the details of the target.

• Click on "Create task".

3. Start the Migration Task

• After creating the task, select it from the list.
• From the dropdown menu, click "Restart/Resume".

Step 5: Verify the Migration

• Connect to your PostgreSQL database using a client like pgAdmin or psql.
• Verify that the data has been migrated correctly from MySQL to PostgreSQL.
• Perform data integrity checks to ensure all data is consistent.

Conclusion

Switching from MySql to PostgreSQL in AWS using the Database Migration Service is a well-sequenced and efficient approach that is not very disruptive and guarantees data consistency. Using the instructions provided in this guide—establishing a replication instance of DMS, generating and checking the endpoints, as well as configuring migration tasks—the migration to a PostgreSQL framework will appear effortless.

After migration is complete, it is necessary to check the correctness of data and further fine-tune the PostgreSQL database for the maximum response rate. Nevertheless, AWS DMS can become truly valuable to aid you in the migration process, as well as in leveraging all the features that PostgreSQL can offer in an AWS environment.