Tech

Methods of MS SQL to PostgreSQL Migration

By Louis Hays / April 3, 2023

Open-source databases are becoming more popular among developers, and PostgreSQL is a great option for migrating your database. Switching from proprietary DBMS like Microsoft SQL to open-source databases offer many benefits for developers and enterprises: reducing total cost of ownership, more control over system’s behavior, wide community of database developers having expertise in core engine of the database.

There are two ways to migrate your database from MS SQL to PostgreSQL: manual migration or using a migration tool. Manually migrating the database involves creating the same database schema in PostgreSQL as you have in SQL Server, and then exporting the data. This can be done by generating scripts in SQL Server Management Studio and executing them in PostgreSQL command line client or PgAdmin.

Here is the brief algorithm of MS SQL to PostgreSQL database migration using SQL Server Management Studio:

  1. Open the product and connect to MS SQL server. Right click on the database that needs to be migrated and select Task, Generate Scripts menu item.
  2. Choose the objects to script – this can be either entire database or specific database objects.
  3. Set the appropriate scripting option to generate single script file for all database objects or a separate script file for each object.
  4. Check selected objects and settings and click Finish.

However, there may be errors due to differences in datatype compatibility between MS SQL and PostgreSQL, naming rules for database entries, distinguished sets of built-in functions, etc. Also, database migration via generating scripts is a tedious time consuming procedure. Using a migration tool can save a lot of time and effort. One such tool is the PostgreSQL Migration Toolkit provided by Intelligent Converters.

Here is the list of steps to migrate database from SQL Server to PostgreSQL using that tool:

  1. Download and install the product from official site of Intelligent Converters
  2. Select option to migrate either to PostgreSQL directly or export into script file (this approach may help when target server does not allow direct connection)
  3. Provide all necessary details to connect to SQL Server

  1. Provide all necessary details to connect to PostgreSQL server (if this option has been chosen on the first wizard screen)

  1. Specify the source and target databases, generic conversion settings

  1. Select tables to migrate

  1. Double-click on a selected table to customize name, type and attributes of every column

  1. Select MS SQL views to convert into PostgreSQL format

Click next and migration process will be run. After migrating the database, there are a few things to keep in mind. First, PostgreSQL and MS SQL have different datatypes, so the Migration Toolkit will convert them during the migration process. However, there may be errors when inserting data into specific columns with specific datatypes. Second, SQL Server and PostgreSQL use different functions for the same thing, so these must be replaced during the migration. Finally, in MS SQL, database objects are treated in a case-insensitive manner, but in PostgreSQL, they are case-sensitive if they are enclosed in double quotes (“”).

All these bottlenecks require specials attention despite of what method was used for MS SQL to PostgreSQL database migration.

About Author

Louis Hays

See author's posts