The Best Way to Migrate SQL Server to PostgreSQL

Because of the user-friendly and easy to understand interface of the Microsoft SQL (MS SQL), it is among the most commonly known database management systems across the world. On the other hand, DBMS has two notable drawbacks, which may force organization to seek alternative database:

• Risk of vendor lockup since most of the tools are produces by Microsoft
• Strict licensing policy and high cost of ownership (especially for large corporate scale databases)

In order to cut back on expenses of IT infrastructure, it is reasonable to migrate to open-source DBMS. Actually, there are only two advanced database management systems distributed under open-source license that provide enough capabilities to become an alternative for SQL Server: MySQL and PostgreSQL.

The MySQL offers generic set of features expected from a sophisticated RDBMS: scalability, security, as well as other storage units for various purposes. At the same time, it has some limitations that may be crucial for complicated databases:

• does not implement the full SQL standard
• inefficient handling of very large data
• it is hard to debug stored procedures in MySQL

Unlike MySQL, PostgreSQL completely supports the “gold standard” of relational DBMS known as SQL92 enhancing it with object-oriented capabilities that makes it the ideal choice for powerful and reliable corporate scale data warehousing.

To migrate SQL Server to PostgreSQL all of the following steps should be performed:

• extract definitions of SQL Server database tables in form of DDL statements
• transform those statements according to PostgreSQL syntax and load to the target database
• export the data from SQL Server database into an intermediate CSV files
• transform the data from CSV files according to PostgreSQL format
• load the improved data into PostgreSQL database using its facilities of importing CSV files

In order to export SQL Server table definitions: open Management Studio, right-click on database in and navigate to Tasks > Generate Scripts item of popup menu. Then Find “Set scripting options” tab, click on “Advanced” link and select parameter “Types of data to script” as “data only” or “data and schema” in the General section.

Correct the resulting script before you proceed to the next step of SQL Server to PostgreSQL database migration.

The following steps are required to load resulting DDL scripts into PostgreSQL properly:

• remove SQL Server specific keywords from the statements (i.e. SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON, SET ANSI_PADDING ON)
• all square brackets enclosing database object names must be replaced by double quotes
• remove square brackets around types as PostgreSQL does not accept this kind of syntax
• default schema for SQL Server is “dbo” while in PostgreSQL it is “public”, so the appropriate replacement is required
• all reference to filegroup must be removed as PostgreSQL does not support this feature (i.e. “ON PRIMARY”)
• replace auto increment types INT IDENTITY(…) by SERIAL, BIGINT IDENTITY(…) by BIGSERIAL
• convert all non-supported SQL Server data types into PostgreSQL equivalents (i.e. DATETIME becomes TIMESTAMP, MONEY becomes NUMERIC(19,4))
• replace all SQL Server statement terminators “GO” by semicolon that is the PostgreSQL synonym

The next step will be to process the data, which can also be done using the SQL Server Management Studio. On the main pane right-click the database name, then select Tasks and Export Data popup menu items. Then pass through all steps of the wizard selecting “Microsoft OLE DB Provider for SQL Server” as data source and “Flat File Destination” as the target.

Once those steps are completed, the SQL Server data will appear at the specified location in form of comma-separated values (CSV) files. If some table contains binary data, it may require the workaround as follows. Walk through the wizard until the option “Write a query to specify the data to transfer” appears. This wizard page is designed to specify table to copy or query. On the next page known as “Provide a Source Query”, compose the following SELECT-query:

select non-binary-field1, non-binary-field2, cast( master.sys.fn_varbintohexstr( cast( binary-field-name as varbinary(max))) as varchar(max)) as binary-field-name from table-name

The query goes into an infinite hang, making this approach not applicable for large binary data, say 1MB and above.

Now it is time to load the resulting CSV data into the target database to finalize data migration from SQL Server to PostgreSQL. For this purpose, use the standard bulk insert command “COPY” as follows:

COPY table-name FROM path-to-csv-file DELIMITER ‘,’ CSV;

In case of “Permission denied” error message try to use “\COPY” command instead.

The brief guide above indicates that SQL Server to PostgreSQL database migration requires a lot of efforts. Manual migration is costly, time-consuming, and can often cause data loss or corruption that can break database logic completely. Therefore, many database professionals suggest using special tools, which can automate database migration within a few clicks of mouse.

SQL Server to PostgreSQL converter is one of those tools developed by Intelligent Converters that is focused on database migration and synchronization for more than 20 years. The tool offers high performance of the migration due to low-level reading and writing algorithms, no ODBC drivers or another middleware are required.

Key features of this SQL Server to PostgreSQL database migration tool:

• all major database entries are migrated (schemas, data, indexes, constraints, views)
• all modern versions of on-premises and cloud SQL Server and PostgreSQL are supported
• option to merge and synchronize existing PostgreSQL database with SQL Server data
• filtering rows to migrate through SELECT-queries
• option to customize name, type and other attributes of every column
• command line version allows to script and schedule the database migration

Visit the official vendor site to learn more about how to migrate SQL Server to PostgreSQL.