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
- Configuring Connectivity between Databases and the DMS
- Connecting to the MySQL Database
- Connecting to a PostgreSQL Database
- Conclusion
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.
Figure 1: Selecting MySQL in RDS
Select the Production or the Dev/Test deployment option, as shown in Figure 2. Click Next step.
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.
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.
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.
Figure 5: Security Group for MySQL on RDS
In the Security Group console, select the Inbound tab and click Edit (see Figure 6).
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.
Figure 7: Configuring Inbound Rules to allow All traffic
The Inbound rules get configured to allow all traffic, as shown in Figure 8.
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.
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.
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).
Figure 11: Obtaining Endpoint
In a command shell, run the following MySQL command from the MySQL database bin directory (C:Program FilesMySQLMySQL Server 5.7bin). 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.
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.
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.
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.
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).
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.
