Incompatibilities to consider while migrating data from azure sql to azure database for postgres

Question

I observed there are few things to consider before migrating data from azure sql to azure database for postgres like

1. Data Types
2. Syntax

Would like to more incompatibilities to consider or review before migration.

Thank you

Answer 1

Hi, @Satya Sri Thanks for the question and for using the MS Q&A platform.
I am curious, to know the reason why you want to migrate Azure SQL to Postgres SQL.
Here are the things to consider and processes to follow.
This is migration across database types, there are no existing tools to achieve this directly since the backup file could not be used. The migration should be on the code level.

DB migration may need below steps:
1, Entities migration：
You may prepare scripts to create tables, views, users and any object being used in your application.

There is some difference in syntax and data type between these two databases, so you need to modify the create statement following PostgreSQL syntax.

2, Data migration:
Export data to the data file from the source database and import data to the target database.
ETL tool may help with data migration. ETL tools like ADF are supported on the Azure platform. You
can use the ADF pipeline to copy data from the source to the target table.
The data migration time depends on the data volume.
3, Application migration:
In this step, you need to change the connection string in your application code.
Also, if there are any functions and store procedures in the source DB. You may need to re-coding them based on Postgresql syntax in order to create on Postgresql successfully.
4, Fully Test:
There is no commitment that things work well after migration, So It’s very necessary for all full tests from the application side.

Hope this will help. Please let us know if any further queries.

Regards
Geetha