dcsimg
December 18, 2018
Hot Topics:

Migrating MySQL to PostgreSQL on AWS RDS, Part 2

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

MySQL Database and PostgreSQL database are the top two open source relational database management systems (RDBMS), with each providing some unique benefits. In the previous article, "Migrating MySQL to PostgreSQL on AWS RDS, Part 1," we discussed some of the advantages of PostgreSQL over MySQL and introduced AWS Database Migration Service (DMS) to perform a migration from MySQL to PostgreSQL. We started by creating a AWS IAM user for DMS. In this continuation tutorial, we shall create instances of MySQL and PostgreSQL databases on RDS, and connect to each. This tutorial has the following sections:

Creating AWS RDS Instances for a MySQL Database and PostgreSQL Database

Create the source and target databases, MySQL database and PostgreSQL database respectively, on AWS RDS. Using RDS is discussed in an earlier tutorial. For the source database, MySQL database, select MySQL, as shown in Figure 1.

Selecting MySQL in RDS
Figure 1: Selecting MySQL in RDS

Select the Production or the Dev/Test deployment option, as shown in Figure 2. Click Next step.

Selecting Deployment type
Figure 2: Selecting Deployment type

Specify DB details, most of which have suitable default settings. Select DB Instance Class (db.t2.micro or db.t1.micro). Specify a DB Instance Identifier (MYSQLDB), which must not be a MySQL database reserved word such as 'MYSQL'. Specify a Master Username, Master Password (must be at least 8 characters), and click Next step. Next, configure Advance Settings. For VPC, select Create new VPC. For Subnet Group, select Create new DB Subnet Group. For Publicly Accessible, select Yes. For Availability Zone, select No Preference. For VPC Security Group, select Create new Security Group. Specify Database Options, including a Database Name (MYSQLDB), which must not be a reserved word such as 'MYSQL', and Database Port (3306). Keep the default settings for Backup; automatic backups are required to use MySQL on RDS as a DMS Source. Keep the default settings for Monitoring and Maintenance and click Launch DB Instance. The MySQL database on RDS gets created.

For a PostgreSQL database, select RDS Database engine as PostgreSQL, as shown in Figure 3.

Selecting PostgreSQL Database Engine
Figure 3: Selecting PostgreSQL Database Engine

Select the Dev/Test deployment type and click Next Step. Specify DB details; most default settings are suitable. Specify DB Instance Identifier (postgresdb), Master Username, Master Password, and click Next Step. Next, configure advanced settings, including the Database Options for Database Name (postgresdb), Database Port (5432), and click Launch DB Instance. A PostgreSQL database gets launched on RDS. The MySQL database and PostgreSQL database on RDS are shown in Figure 4.

MySQL and PostgreSQL Database on RDS
Figure 4: MySQL and PostgreSQL Database on RDS

Configuring Connectivity between Databases and the DMS

By default, Database Migration Service (DMS) is not able to connect with RDS DB instances. We need to add connectivity to the Security Groups for the RDS DB instance. Click the Security Group link in Configuration Details for the MySQL RDS DB instance, as shown in Figure 5.

Security Group for MySQL on RDS
Figure 5: Security Group for MySQL on RDS

In the Security Group console, select the Inbound tab and click Edit (see Figure 6).

Security Group>Edit
Figure 6: Security Group>Edit

In Edit Inbound rules, select Type as All traffic, Protocol as All, Port Range as 0-65535, Source as Anywhere and click Save, as shown in Figure 7.

Configuring Inbound Rules to allow All traffic
Figure 7: Configuring Inbound Rules to allow All traffic

The Inbound rules get configured to allow all traffic, as shown in Figure 8.

Inbound>All traffic
Figure 8: Inbound>All traffic

The Outbound rules also should get set to All traffic, as shown in Figure 9, when the Inbound rules are configured.

Outbound>All traffic
Figure 9: Outbound>All traffic

Similarly, click the Security Group link for the PostgreSQL RDS DB instance, as shown in Figure 10, and configure Inbound/Outbound rules to allow all traffic.

Configuring Security Group for PostgreSQL to allow all traffic
Figure 10: Configuring Security Group for PostgreSQL to allow all traffic

Connecting to the MySQL Database

When migrating a MySQL database instance on RDS, the complete database may be migrated, which includes all the schemas and tables, including the system schemas and tables. In this section, we shall connect to the MySQL database on RDS and grant some user privileges to the dvohra user. To connect to MySQL database, obtain the database Endpoint from the RDS Dashboard (see Figure 11).

Obtaining Endpoint
Figure 11: Obtaining Endpoint

In a command shell, run the following MySQL command from the MySQL database bin directory (C:\Program Files\MySQL\MySQL Server 5.7\bin). This will start a MySQL CLI session.

mysql -h mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com
   -u dvohra -p

Specify the password at the Enter password: prompt. The SQL> command prompt gets displayed, as shown in Figure 12.

Connecting to MySQL Database with CLI
Figure 12: Connecting to MySQL Database with CLI

Next, grant privileges for the MySQL database instance on RDS to the DMS user (dvohra).

GRANT ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE,
   SELECT ON *.* TO 'dvohra'@'%';
GRANT ALL PRIVILEGES ON awsdms_control.* TO 'dvohra'@'%';

User privileges get set, as shown in Figure 13.

Adding User Privileges
Figure 13: Adding User Privileges

Connecting to a PostgreSQL Database

To connect to PostgreSQL database on RDS from a command shell, obtain the database Endpoint from the RDS dashboard, as shown in Figure 14.

Obtaining the PostgreSQL Database Endpoint on RDS
Figure 14: Obtaining the PostgreSQL Database Endpoint on RDS

Run the following command to connect to PostgreSQL database and start a command line interface (CLI).

psql --host=postgresdb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com
     --port=5432 --username dvohra --password --dbname=postgresdb

The PostgreSQL database command line interface (CLI) gets started, as shown in Figure 15.

Connecting to PostgreSQL Database on RDS with a CLI
Figure 15: Connecting to PostgreSQL Database on RDS with a CLI

List the schemas with the \dn command. The public schema gets listed (see Figure 16).

Listing Schemas
Figure 16: Listing Schemas

Conclusion

In this second of four tutorials on migrating a MySQL database instance on RDS to a Postgres database instance on RDS with AWS DMS, we created instances of the two databases on RDS and connected to the database instances. In the next tutorial, we shall create a DMS migration.






Comment and Contribute

 


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

 

 


Enterprise Development Update

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

Sitemap

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