Azure SQL PostgreSQL Integration: 3 Easy Methods

The Microsoft Azure platform offers several alternatives for hosting database servers, including Infrastructure-as-a-Service (IaaS), Platform-as-a-Service (PaaS), and Software-as-a-Service (SaaS). 

Platform as a Service (PaaS) database engine Azure SQL Database is fully managed and performs most database maintenance tasks like upgrading, patching, backups, and monitoring without user intervention.

This is a fantastic database with different features for different users. It has been challenging for years to move a database created in SQL Server to Microsoft Azure SQL Database. Any PostgreSQL workload is a feasible target, so if you’re seeking to migrate, now is a wonderful time to move those workloads to the cloud.

This article introduces you to Azure SQL PostgreSQL Integration and discusses 3 easy methods to set up the Azure SQL PostgreSQL integration. Read along to learn their steps and decide which way is best for your business!

Prerequisites

At the outset, you must understand that though both Azure SQL and PostgreSQL are ANSI-SQL compliant, there are subtle differences in SQL syntax, data types offered/supported and how things are implemented internally. To set up the Azure SQL PostgreSQL integration, you must:

• The first task would be to map the data types between the two and find appropriate data types in the destination database which can house the data types from the source database. 
• Add tables or fields in the source database that record the time of the latest data transfer, it could be timestamps or fields like updated_at/created_by in tables, these will help you identify the records that need to be updated and the frequency of data refreshes. 
• Identify product-specific features that are not present in the other database, and plan for alternatives. 

What is the Need to Set Up Azure SQL PostgreSQL Integration?

Azure SQL is a paid solution, whereas Postgres is free, so in cases where the budget could be limited, Postgres is a good choice. 

Azure SQL is a proprietary software whereas Postgres is developed and actively maintained by a global community of open-source developers; hence Postgres tends to be more versatile and open to integration with diverse technologies. 

Postgres is not cloud-based, whereas Azure SQL is maintained and operated by Microsoft as an IAAS, so in cases where technical resources would be used for innovation/business logic requirements rather than infrastructure maintenance, Azure SQL is a good choice. Therefore, it is good to set up Azure SQL PostgreSQL integration.

Methods to Set Up Azure SQL PostgreSQL Integration

This section will discuss some methods to connect Azure SQL to PostgreSQL and migrate data. With a little innovation, these methods could also work the other way round, i.e., connect PostgreSQL to Azure SQL.  

• Method 1: Using Hevo Data
• Method 2: Using Fine-Grained Manual Connections
• Method 3: Using Free Automation Tools

Method 1: Azure SQL PostgreSQL Integration Using Hevo Data

Hevo Data, a No-code Data Pipeline helps you directly transfer data from Azure SQL and 150+ other data sources (including 40+free data sources), Data Warehouses, or a destination of your choice, such as PostgreSQL, in a completely hassle-free & automated manner. Hevo allows you to move data to the desired data destination.

Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.

You can establish the Azure SQL PostgreSQL connection via Hevo Data using the following 2 steps:

• Configure Source: Establish your Azure SQL account as the source for your Hevo Pipeline.

• Authenticate Destination: Set up PostgreSQL as your destination with Hevo, as shown in the below image.

Method 2: Azure SQL PostgreSQL Integration Using Fine-Grained Manual Connections

Your data in PostgreSQL or Azure may not necessarily be single-valued or relational. Instead, it could be multi-valued as well as hierarchically structured. 

Step 1: Export Your Data from PostgreSQL

So choose your destination data types accordingly, PostgreSQL gives you Arrays/JSON/XML/Enumeration/Geometric/Range as composite/multi-valued types. 

Azure SQL gives you NVarchar/VarBinary/XML etc. So the JSON object in PostGre should be mapped to NVarchar in AzureSQL, and Blob to VarBinary in AzureSQL. 

So, the first prerequisite is a working AzureSQL database and tables with data in them. If you don’t already have a database in Azure, create it using  SQL Server Management Studio (SSMS). 

The first step would be to create a schema in Postgres such that the schema can ingest the data from AzureSQL. This schema should have a combination of tables with appropriate data types ( Integer, Float, JSON, String etc.) to house each of the Azure SQL datums. 

To create a database in Postgres emter:

CREATE DATABASE  my_Azure_DB WITH ENCODING='UTF8' LC_CTYPE='en_US.UTF-8' LC_COLLATE='en_US.UTF-8' OWNER=postgres CONNECTION LIMIT=-1; 

Use UTF8 as it supports all languages and it’s one of the most encompassing, connection limit is set to -1 to allow multiple connections. The owner is Postgres to allow the widest possible set of permissions for a smooth transfer. You can learn more about it here.

Next, create a table in Postgres:- 

CREATE TABLE my_pg_table  (
id Integer SERIAL, 
success BOOLEAN, 
text_field text, 
my_chars varchar(100), 
additional_data JSON, 
created_at TIMESTAMP WITH TIMEZONE 
); 

You can create more corresponding tables in PostgreSQL this way. 

Step 2: Create a PostgreSQL Function

Then formulate a function that can insert multiple rows into a PostgreSQL table, something like:- 

CREATE OR REPLACE FUNCTION insertdata(boolean bool_from_Azure, text text_from_Azure, char char_from_Azure, json data_from_Azure) ) 
  RETURNS VOID AS 
$$ 
 INSERT into my_pg_table values ( 0,  bool_from_Azure,  text_from_Azure, data_from_Azure, now() ) ; 
$$ 
LANGUAGE sql STRICT; 

Use 0 to allow PostGre to generate auto-increment(SERIAL) integers which would act as unique IDs for each record. Similarly, define insert functions for other tables. Then call these functions one by one, supply them with the necessary data and populate the tables you just created. 

Azure SQL gives you a function called “bcp” that is useful for bulk imports as well as bulk exports. So, to bulk export your data from Azure SQL, you can run the BCP utility as:- 

bcp my_Azure_DB.dbo.##My_Azure_Table out "C:MyFolderBCPFilesMy_Azure_TableFile.csv" -c -t -T 

This command will make a CSV backup of the whole table and place it under the specified folder path. Next, Postgres will readily accept this CSV and do a bulk import, via the COPY command. 

COPY my_pg_table FROM '/tmp/My_Azure_TableFile.csv' WITH (DELIMITER  ',' ); 

Step 3: Reverse Migration

Repeat Step 1 and Step 2 for every table in Azure, and import the data into Postgres. If you’re implementing the migration the other way round, from PostGre to AZureSQL your commands will reverse and look something like this:- 

COPY my_pg_table TO  '/tmp/My_PG_TableFile.csv' WITH (DELIMITER  ',' )  // TO replaces FROM 
and then 
bcp my_Azure_DB.dbo.##My_Azure_Table IN  "C:MyFolderBCPFilesMy_PG_TableFile.csv"  -T  -c   // IN replaces OUT 

Method 3: Azure SQL PostgreSQL Integration Using Free Automation Tools

This method uses certain free tools to accomplish all the boilerplate code for you and automate the Azure SQL to PostgreSQL database migration.  

Two popular tools for the purpose are:- 

•  sqlserver2pgsql – This tool migrates a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible. 

This toll will first convert your Azure SQL schema to a PostgreSQL schema, and then all the data will be migrated in the next step. 

To facilitate this process, the tool will generate 3 types of files. 

1.  tables-before.sql: holds the converted schema, you can manually check if everything looks good to you. 
2.  tables-after.sql: holds all the constraints on Azure SQL data, like foreign keys/triggers etc., which will be executed after data migration. 
3.  tables-unsure.sql: In case the tool hits an impediment or is unsure of what mapping/conversion to execute, it will store those problematic instructions here. 

 You will need to check and review tables-unsure.sql and instruct what needs to be done OR identify problematic conversions and perform them manually. To keep your data fresh and updated, this tool will also create an incremental version of the migration job, which you can periodically run to update what has changed since the last migration. 

•  Pgloader: Pgloader is a free tool offered by PostgreSQL which enables loading data from many other databases into PostgreSQL. 

Pgloader intelligently uses the COPY command internally, and maintains a separate file for problematic data but continues to load good data while you decide/mend the erroneous source data. 

Pgloader will automatically detect the source schema and its associated referential integrity constraints, indexes and primary keys etc. It then maps/finds appropriate matching data types in Postgres (destination) for the source data types, plans its execution and runs it. 

pgloader can also maintain a log, which can be helpful in case there are errors or you want to improve future migrations. 

Benefits of Integration Using Method 2 & 3

• You get fine-grained control over your collaboration/migration process. 
• You can fine-tune the migration to suit your application-specific needs. 
• As you minutely plan your connections, you can find bugs or improve the way you are using your data types in the database. 
  

Limitations of Azure SQL PostgreSQL Integration Using Method 2 & 3

• To ensure proper migration, you must keep an eye on the process as it may ask for your help/instructions. 
• All the above methods and tools are a bit old, from when ETL and BigData had not gained prominence. 
• With the advent of cloud-based data warehouses, data pipelining solutions and advancements in network and storage technologies, some powerful and versatile tools have gained prominence. These modern approaches to data ingestion deliver faster implementation than traditional ETL. 
• They also let expensive technical resources focus on innovation, business intelligence and core functions rather than ETL.

Learn More About:

How to Migrate Azure Postgres to Redshift

Conclusion

This article provided a step-by-step tutorial for 3 methods to configure your Azure SQL PostgreSQL integration. Utilizing Hevo – ETL pipelines saves you from a lot of juggling & struggling required to configure & monitor this integration. 

You do not require any extensive technical training to set up the pipeline.  While you must be technically efficient to establish the Integration, which requires you to write scripts even to connect Azure SQL PostgreSQL manually.  

In the 3rd approach, third-party programs like pgloader to link Azure SQL with PostgreSQL are used. One drawback of using these tools is that they may not be adaptable enough to accommodate a wide range of sources and destinations, and they may only be compatible with a limited number of databases.

FAQ on Azure SQL PostgreSQL Integration