Database migration from SQL Server to PostgreSQL

Microsoft SQL Server and PostgreSQL are both very popular RDBMShavingrich set of administration tools and programming APIs. However, MS SQL has quite restrictive licensing policyand high total cost of ownership while PostgreSQL goes with less restrictive license and it is open source. That is why many businesses and organizations consider migrating their databases from SQL Server to PostgreSQL.

In general, database migration consists of the following steps:

  • Extract table definitions from the source database in form of DDL SQL statements
  • Convert these statements into the destination format and load into the target database
  • Export data from the source database into an intermediate storage (for example, csv files)
  • Transform the data according to the destination format and load it into the target database
  • Extract views, stored procedures and triggersfrom SQL Server database in form of SQLImage result for Database migration from SQL Server to PostgreSQL

statements and source code

  • Convert these statements and source code into the destination format and load into the target database

Obviously, the procedure of migratingdatabase from MS SQL to PostgreSQL requires many efforts when doing it manually. In addition, there is high risk of data loss or corruption due to the human factor. It would be wise to use special software to automate the conversion process.

One of such conversion tools is SQL to PostgreSQL developed by Intelligent Converters. Intelligent Converters is a software vendor specializing in solutions for database conversion, migration and synchronization since 2001. Their MS SQL to PostgreSQL converter has extremely high performance due to direct reading and writing data without anymiddleware libraries or components. It supports all versions of SQL Server and PostgreSQL including cloud solutions. The product provides command line version in order to automate and to schedule database conversion. MS SQL to PostgreSQL converter allows not only migrate data from SQL Server into new database but also merge or synchronize it with existing PostgreSQL database.

Do you need to migrate only certain records to the destination database? No problem, the tool allows to filter data via SELECT queries. With this option, it is possible to select particular columns and records or even transform the data before converting it into PostgreSQL format. Here are a few examples of using this feature for different purposes.

  • Filtering records: SELECT * FROM MyTable WHERE KeyColumn> 1000
  • Select and rename individual columns: SELECT column1 AS Name, column2 as Phone FROM MyTable
  • Skip NULL values: SELECT * FROM MyTable WHERE Address IS NOT NULL

Sometimes it is necessary to change column type in the resulting database. For this purpose MS SQL to PostgreSQL converter provides feature called “customcolumn mapping”. It is a dialog window that allows to change name, type, default value and NULL-attribute for any column of table as well as exclude certain columns from conversion.

If the target PostgreSQL server does not allow remote connection, the program provides option to export the data into SQL script. In this approach source database is exported into local file containing SQL-statements to create tables with all indexes and constraints and fill them with the data. Then database administrator can import the script file to PostgreSQL server via one of the following tools:

  1. PostgreSQL console client (psql)

psql -h host -d database -U user -f script_file

  1. phpPgAdmin
    • Highlight the target database
    • Navigate to the “SQL” button
    • Push “Choose File” button and select the SQL script file to import
    • Click “Execute”

As every product of Intelligent Converters, SQL to PostgreSQL converter is supplied with unlimited support and 1 year subscription for updates.