Migrate SQL Server to PostgreSQL

Microsoft SQL Server and PostgreSQL are both extremely popular RDBMS having rich set of administration tools and programming APIs. However, MS SQL has quite restrictive licensing policy and high total cost of ownership while PostgreSQL goes with less restrictive license, provides rich collection of advanced features (user-defined types, full-text search, robust access control, foreign data wrappers and many others) and it is open source. Those are primary reasons forcing many companies and organizations to migrate SQL Server to PostgreSQL.

In common cases migration between two database management systems is arranged according to extract-transform-load technique that consists of the following steps: 

• Extract SQL Server table definitions with corresponding indexes and constraints in form of SQL CREATE-statements
• Convert these statements to comply with the destination format and load to PostgreSQL database
• Export SQL Server data into csv files (one file per table) as an intermediate storage 
• Transform the data according to the destination format and load it into the target database
• Extract SQL Server views, stored procedures and triggers in form of SQL statements and source code
• Translate these statements and source code into the PostgreSQL format with respect to operators, built-in functions and other features missing in the target database
• Import the converted statements and source code into PostgreSQL database
• Perform overall testing to verify that all components of the database are working as expected

The approach explored above proves that database migration requires many efforts when doing it manually. Moreover, manual attempt to migrate SQL Server to PostgreSQL implies high risk of data loss or corruption due to the human factor. That’s why database professionals in most cases use special software to automate the migration process. 

Migration via PgLoader

For experienced database administrators and developers there is efficient open-source solution to migrate SQL Server to PostgreSQL that is worth to try. This is pgloader, small tool that provides schema conversion, data migration, can handle data type mapping to make the migration process even more flexible. It is important to understand basic steps of migration and valuable things to validate for complex cases. However, `pgloader` can significantly simplify multiple important actions included in the migration such as reading the source data and meta-data and writing to the target database.

There are few actions required to migrate SQL Server to PostgreSQL using PgLoader:

• Install the product using package managers or via direct download from the vendor’s site
• Compose configuration file that defines SQL Server and PostgreSQL connections, rules for mapping data types and other important settings
• Execute PgLoader

There is template of configuration file coming with PgLoader called ms.load. It looks as follows: 

load database

     from mssql:pswd1//user@host/dbname

     into postgresql:pswd2///dbname

including only table names like ‘GlobalAccount’ in schema ‘dbo’

Modify the connection parameters and other settings according to your requirements before running the migration.

SQL Server to PostgreSQL Converters

For those who feel uncomfortable working with command line or wish to avoid spending much time on configuration routines, it is recommended to use commercial migration tools having user-friendly graphical interface. 

One of such easy-to-use yet powerful tools to migrate SQL Server to PostgreSQL has been developed by Intelligent Converters software company. It offers high performance due to direct reading and writing using extremely efficient libraries for low-level reading and writing the data. The tool supports all versions of SQL Server and PostgreSQL including SAAS platforms (Azure, Amazon RDS, Google Cloud, etc). Command line support makes easy to script and schedule database migration tasks. SQL Server to PostgreSQL migration tool also can merge or synchronize existing PostgreSQL database with MS SQL data, although it does not support incremental synchronization. 

When the migration project requires to process only certain rows of SQL Server tables, the tool allows to filter migrated data though SELECT queries. This feature can be used to select individual columns, records, merge multiple tables in a single one and even pre-process the data before sending it to PostgreSQL database. 

Some migration projects may require to customize column names or type mapping. For these purposes, SQL Server to PostgreSQL converter offers “edit table” feature. This is the dialog window to change properties of any column of SQL Server table and to exclude some columns from conversion. 

For the situations when PostgreSQL server does not allow remote connection or the target DBMS is a cloud platform with limited access, the converter offers exporting the data into SQL script. Following this way SQL Server database is migrated into a local script file containing SQL statements to database objects and insert the data. Later this file can be loaded into PostgreSQL database the standard client tools like pgAdmin.

Learn more about how to migrate SQL Server to PostgreSQL and product’s features on the official site of Intelligent Converters.