Database Migration From MSSQL To PostgreSQL
It is challenging to obtain a precise manual process and collect data about database migration from MSSQL to PostgreSQL due to a lack of much information on this subject over the internet. To throw light on this topic, here is a well-defined solution to easily migrate into PostgreSQL database as per the client’s requirement.
Road map for migration of MSSQL to PostgreSQL DB
- Create a database and schema in PostgreSQL similar to MSSQL
Create a new database and schema in PostgreSQL DB with similar nomenclature as in MSSQL DB and set permissions for the same.
- Take database backup from MSSQL
Obtain MSSQL DB backup and restore it into PostgreSQL DB, because backup is a necessary step in any migration process.
- Create a list of syntax and data types from both the databases
While working with such complex databases, it is apparent that they might vary in terms of their syntax and data types. Hence, before moving on to database object migration, it is essential to maintain a list of all the data types and syntax structures from both the databases. This is the best way to avoid chaos and unnecessary errors, thereby increasing productivity. An example is shown below:
|Action||SQL Server Code||PostgreSQL Code|
|Datediff||datediff(MINUTE,@TicketInTime,@TicketOutTime)||DATE_PART(‘hours’,TicketOutTime – TicketInTime ) * 60 + DATE_PART(‘minute’, TicketOutTime – TicketInTime )|
|DATEPART||DATEPART( datepart , date )||date_part( text , timestamp ) date_part( text , interval )|
|String Concatenation||SELECT FirstName + LastName FROM employee;||SELECT FirstName || LastName FROM employee;|
|CHARINDEX||SELECT CHARINDEX(‘our’, ‘resource’);||SELECT POSITION(‘our’ in ‘resource’);|
- Export table definitions from the MSSQL and convert all table definitions into PostgreSQL format
It is necessary to maintain the same tables after migration in PostgreSQL and for that reason, we need to export table definitions from MSSQL. After exporting, changes need to be made in the definitions, according to PostgreSQL format. It is important to ensure that the constraints remain the same as per MSSQL DB while naming convention changes to suit PostgreSQL. All the converted statements should be run into PostgreSQL DB.
- Export all other scripts from MSSQL, perform requisite code changes in all scripts and run in PostgreSQL
Export other remaining database objects such as User-defined functions, Stored Procedures, Triggers and Views into PostgreSQL format. Make changes with the help of the list of data types and syntax of both databases compiled previously. Conversion of these objects is a critical aspect of migrating as further functionality depends on this. Ensure that after the database object scripts migration, the result sets from both the databases is same.
SQL Server Agent
- Install pgAgent for job creation
pgAgent is a PostgreSQL Agent provided for creating and scheduling jobs. Its counterpart in MSSQL is SQL Server Agent. Create a job in pgAgent and set similar specifications and configurations as per the ones in MSSQL Server Agent.
- Make the necessary changes in the related application aspects
Integration of all the aspects of the system is crucial. Therefore, it is essential to get the changes done in front-end as well as back-end parts of the application after database migration into PostgreSQL as required for smooth functioning of the application.
- Test all application functionality after the DB migration and system integration
After the completion of database migration and integration, it is necessary to test whether all result sets of the newly created PostgreSQL DB are synchronous with the old MSSQL DB. This step ensures proper migration and integration of the database.
FLOW DIAGRAM FOR MSSQL DB TO POSTGRESQL DB MIGRATION
Challenges faced while migrating MSSQL to PostgreSQL
Using the existing migration tools, anyone can migrate the DB with a few clicks. But using the manual process, a developer can customize their code safely. Also, the process is highly feasible as far as data security is concerned, because there is no need to hand over the data to a third party and confidentiality is maintained internally.
Exporting the object definitions from MSSQL to PostgreSQL leads to non-uniformity with PostgreSQL format. Hence, converting to PostgreSQL format and the naming convention is a time-consuming endeavor.
Benefits of migrating to PostgreSQL
- PostgreSQL is an open-source and free relational database management system.
- It is maintained by a global community of open source developers.
- It is available for multiple platforms such as Windows, Mac, Solaris, FreeBSD and Linux.
- PostgreSQL is a very user-friendly and cross-platform database engine.
- Multiple add-ons are available to improve the database’s performance.
Positive outcomes from the manual migration process
- Meeting the custom requirements of DB migration without using any third-party tool – Manual process leads to a decrease in reliance on a third party tool which might have been restricted to our customer’s requirements.
- Data security – As we eliminate the need of sending data to a third-party tool, it can be assured that the data does not leave the organization, resulting in increased data security.
The article offers insights about the process of secure database migration from MSSQL to PostgreSQL, without using a third-party tool to fulfil the customer’s requirements. The open-source helps in minimizing additional costs. MOURI Tech always prefers the most updated technologies. The manual approach is a highly recommended practice if one is willing to migrate their database into PostgreSQL.
Contact for further details
Priyanka Ashok Narvekar
Technology Analyst – Digital Java