Database Migration to Azure for PostgreSQL

PostgreSQL has been an extremely popular open-source relational database management system for a long time. Following the long-term trend of migration to SAAS platforms, many organizations choose the Azure cloud since it provides rich infrastructure including more than 200 applications. That is why database migration to Azure for PostgreSQL is a reasonable choice. 

This article explores basic approaches to three types of migration:

• Between different engines of Azure databases
• From on-premise PostgreSQL to Azure for PostgreSQL
• From different on-premise databases to Azure for PostgreSQL

Azure Data Factory

If both source and destination databases are hosted in the same Azure account under the same resource group and location, we can run migration according to the extract-transfer-load (ETL) approach via Azure Data Factory. An instance of the tool must be created in the same resource group and region as database instances. Before running the ETL database specialists have to go through preparation tasks such as exploring and assessing the source, designing the migration rules, and other steps to build the optimal approach to migration.

Although database migration with Azure Data Factory is a straightforward procedure, it includes many steps to set up migration procedure:

• Azure Data Factory portal and select the “Copy Data” option
• Enter the task name and execution frequency (it is “Run once now” for one-time migration)
• Select the source database, it is Azure SQL for our task
• Provide credentials to connect from Azure Data Factory to Azure SQL Database. Click the “Test Connection” button to make sure that the connection is available.
• Select the tables to migrate or build custom queries to reorganize the data
• Specify the destination database, in our example, it is Azure Database for PostgreSQL
• Provide credentials to connect from Azure Data Factory to Azure Database for PostgreSQL. Click the “Test Connection” button to make sure that the connection is available.
• The next step is to map tables and this is the real bottleneck of the entire process because the system cannot automate it. Even if both source and destination have tables with the same name you have to specify the equivalence manually.
• Finally, we can set up data consistency verification, logging, and other parameters related to migration performance and data integrity

After execution of the task completes data will be migrated from Azure SQL database to Azure Database for PostgreSQL.

Azure Database Migration Service

If the source of migration is an on-premise PostgreSQL server, Azure Database Migration Service (DMS) can be used to migrate the database to Azure for PostgreSQL. Before starting database migration, you need to check the following prerequisites:

• The version of Azure for PostgreSQL is not less than the version of the on-premises PostgreSQL
• Connection to on-premises PostgreSQL is set up through either ExpressRoute or VPN
• Windows firewall does not block access to TCP port of the source PostgreSQL Server (it is 5432 by default) for Azure DMS
• Create firewall rule for Azure Database for PostgreSQL to allow Azure Database Migration Service to access to the target databases (see the related screenshot below)
• Install and run the CLI version 2.18 or above from the Azure Cloud Shell that can be launched by clicking the Cloud Shell button in the upper-right corner of the Azure portal.

Now everything is set up and prepared for database migration. Follow these steps to migrate schemas, indexes, and stored procedures from on-premise PostgreSQL server to Azure for PostgreSQL:

• Create dump file with schemas for the source database:

• Import schemas from the dump file into the target database:

• The data is migrated using sync extension of Database Migration Service

The details of the database migration procedure through Azure Database Migration Service are specified in Microsoft Azure official documentation.  

Third-party Tools  

When migrating different on-premise databases such as MySQL, Oracle, or IBM DB2 to Azure for PostgreSQL it is reasonable to use special automation tools designed for this purpose. For example, MySQL to PostgreSQL converter developed by Intelligent Converters can simplify migration of both on-premise and cloud MySQL instances to Azure for PostgreSQL.

Key features of MySQL to PostgreSQL converter:

• All versions, forks, and SAAS variations of PostgreSQL (starting from v9.0) and MySQL are supported including Heroku, Azure, MariaDB and Percona
• Tables, data, indexes, constraints, and views are migrated
• SSL connection for PostgreSQL is supported
• Filtering and pre-processing the source data through SELECT-queries
• Option to migrate MySQL database into PostgreSQL dump file containing SQL statements to create database objects and load the data
• Option to merge or synchronize existing PostgreSQL database with the source data
• Command-line support

Although the product has an intuitive easy-to-use interface, there are two important points to control when configuring the remote connection to Azure for PostgreSQL.

First, make sure that the IP address of your machine is in the list of allowed IPs on Azure:

• Open the Microsoft Azure Dashboard and click on the server name you want to access
• Click on the "Connection security" item in the menu on the left side of the window
• Make sure that the control "Allow access to Azure services" is "Yes"
• If the "Add current client IP address" link is active it means that your IP address is not on the list. Click the link to add it.

The next step is to get the name of the Azure PostgreSQL Server and check SSL status:

• Go back to Microsoft Azure Dashboard and click on the database server you want to use
• Locate the "Server name" item, in our example, it is "ictest3.postgres.database.azure.com" (see the item marked red on the screenshot below)

If Azure for PostgreSQL is configured with "SSL enforce status" enabled, it is necessary to specify SSL certificate BaltimoreCyberTrustRoot.crt that can be downloaded from the official Microsoft Azure site. To point the certificate in MySQL to the PostgreSQL converter, click the "Advanced..." button on the "Connect to PostgreSQL Server" wizard page and enter the path to the file in the "CA Certificate" field of the dialog box.

Conclusion

This article explored three options of database migration to Azure for PostgreSQL that includes:

• Azure Data Factory can be used to migrate between two instances of Azure databases
• Azure Database Migration Service (DMS) migrates database from on-premise PostgreSQL server to Azure for PostgreSQL
• Third-party database migration tools like MySQL to PostgreSQL converter can migrate any on-premise or cloud data source to Azure for PostgreSQL with just a few clicks of the mouse button