Architecture & DesignMigrating MySQL to PostgreSQL on AWS RDS, Part 1

Migrating MySQL to PostgreSQL on AWS RDS, Part 1

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

MySQL database and PostgreSQL database are both open source relational database management systems (RDBMS) ranked 2nd and 4th, respectively, according to DBEngines.com. Both are implemented in C (C++ is also used for MySQL database) and both provide similar features, such as XML support, secondary indexes, concurrency, durability, and user-defined functions. PostgreSQL database provides several advantages, some of which are as follows:

  • Supports advanced data types such as multi-dimensional arrays and user-defined types
  • Common Table Expressions (CTEs) and Window functions have been available since PostgreSQL 8.4, while only being added to MySQL in version 8.0
  • Supports full outer joins
  • Supports VALUES lists to generate a ‘constant table’ that may be used in a query
  • Supports set returning functions generate_series
  • Supports parallel queries making use of multiple CPUs to answer queries faster since version 9.6
  • Supports Materialized Views

If migrating from MySQL to PostgreSQL database AWS, DMS (Database Migration Service) provides a reliable managed service with zero-downtime for continuous data replication. In four tutorials, we shall discuss migrating a MySQL database instance to PostgreSQL database, both running on AWS Relational Database Service (RDS). This tutorial has the following sections:

Setting the Environment

The only pre-requisite is an AWS account, which may be created at this link. Create a KMS master encryption key to be used with DMS if a master key is not already available. A KMS master key is created from the IAM>Encryption keys dashboard. We have used a KMS master encryption key called “dbms” in this tutorial.

Install the MySQL database and PostgreSQL database on a local machine to use the client interface applications to connect to MySQL and PostgreSQL database instances on RDS.

Creating an IAM User for DMS

DMS is fully integrated with several other AWS services, such as RDS for databases, IAM for identity and access management, KMS for data encryption, and CloudWatch for logging. DMS also supports S3 (Simple Storage Service) as a target for a migration. In this section, we shall create an IAM user that is used with DMS to migrate a MySQL Database instance to PostgreSQL database. The IAM user is created as follows:

  1. Create an IAM Policy with all the required permissions and policy statements.
  2. Create an IAM user based on the IAM Policy.

To create an IAM policy, click Create policy in the IAM Dashboard, as shown in Figure 1.

Create Policy
Figure 1: Create Policy

In the Create Policy wizard, select Create Your Own Policy, as shown in Figure 2.

Selecting the Create Your Own Policy Option
Figure 2: Selecting the Create Your Own Policy Option

In Review Policy, specify a Policy Name (DMS). Then, copy the following policy document into the Policy Document field or region.

{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": "dms:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "s3:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "ds:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "rds:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "kms:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "iam:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "ec2:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "cloudwatch:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "aws-marketplace:*",
         "Resource": "*"
      },
      {
         "Effect": "Allow",
         "Action": "logs:*",
         "Resource": "*"
      }
   ]
}

The policy document adds permissions to access CloudWatch for logging and RDS for databases. Click Validate Policy and, if the message is “The policy is valid,” click Create Policy, as shown in Figure 3.

Create Policy
Figure 3: Create Policy

If the policy gets created, the message shown in Figure 4 should get displayed.

DMS has been created
Figure 4: DMS has been created

Next, add an IAM user. Select Users in the IAM dashboard and click Add user, as shown in Figure 5.

Add user
Figure 5: Add user

Specify a User name (dvohra). Then, select both the listed options for Access type (see Figure 6).

Adding User Detail
Figure 6: Adding User Detail

Select the Custom password option for Console password, specify a password, and click Next: Permissions, as shown in Figure 7.

Configuring Access Type
Figure 7: Configuring Access Type

Next, set permissions for the user. Click Attach existing policies directly, as shown in Figure 8.

Attach existing policies directly
Figure 8: Attach existing policies directly

In the Policy type filter, search for “DMS”, which is the policy created earlier. Select the DMS policy as shown in Figure 9, and click Next.

Selecting the DMS Policy
Figure 9: Selecting the DMS Policy

In Review, the DMS policy should be listed as the Managed Policy in the Permissions summary. Click Create user, as shown in Figure 10.

Create user
Figure 10: Create user

The IAM user gets created (see Figure 11).

IAM User created
Figure 11: IAM User created

The IAM user gets listed in the IAM>Users dashboard, as shown in Figure 12.

IAM User
Figure 12: IAM User

To log in as the IAM user created, click the link for the user, select the Security credentials tab, and copy the Console login link, as shown in Figure 13.

Console login link
Figure 13: Console login link

Log in as the IAM user created using the Console login link for subsequent sections.

Conclusion

In this first of four tutorials on migrating a MySQL database instance on RDS to a Postgres database instance on RDS, we created an IAM user to perform the migration.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories