Published on December 18th, 2020 | by Bibhuranjan

The Best Way To Move SQL Server Databases To PostgreSQL

Because of the user-friendly and easy to understand interface of the Microsoft SQL, it is among the most commonly known database management system (DBMS) across the world. The program however has two notable drawbacks, which may at times mean users need to seek alternative DBMS. They consist of:

strict licensing policies
high cost of ownership (not good for owners of large databases)

Reviewing the open-source databases is advisable in order to cut back on expenses of DBMS ownership. There are three main database management systems distributed under open-source license namely:

SQLite
MySQL
PostgreSQL

What are the primary differences in between them?

SQLite can be described as file-based database as well as a self-contained database system, created and embedded only into programs, therefore can’t be used by the multi-user environment as a substitute for big databases.

The MySQL, alternatively, is a lot more powerful and offers features usual for a sophisticated RDBMS. These functions include things like scalability, security, as well as other storage units for various purposes. Many of its drawbacks include:

no support for full text search
does not implement the full SQL standard
poor or lack of support for simultaneous writes

PostgreSQL follows the standard relational DBMS known as SQL92 enhanced with object-oriented capabilities that makes it the best option for powerful and reliable corporate scale data warehousing.

Migrating Databases

To migrate database from SQL Server to PostgreSQL all the following steps must be performed:

extract MS SQL table definitions as DDL scripts
convert them according to the syntax of PostgreSQL DDL statement
create tables in the target database using those script files
export the data from SQL Server database into 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

Below you can find hot to export table definitions on different versions of SQL Server:

An Update to SQL Server Management Studio is Available - Brent ...

for SQL Server version 2008 and earlier right-click on database in Management Studio, then click on Tasks, Generate Scripts. Ensure to check the wizard to see that “data” is set to false, which is default.
for SQL Server version 2012 and later right-click on database in Management Studio and navigate to Tasks > Generate Scripts item of popup menu. 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.

Before proceeding to the next step of SQL Server to PostgreSQL migration, it is required to correct DDL scripts according to PostgreSQL syntax:

remove SQL Server specific keywords from the statements (i.e. SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON, SET ANSI_PADDING ON)
square brackets around database object names is a part of MS SQL dialect and 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
remove all optional keywords that are not backed up by the target DBMS (i.e. WITH NOCHECK, CLUSTERED)
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 MS SQL data types into PostgreSQL equivalents (i.e. DATETIME becomes TIMESTAMP, MONEY becomes NUMERIC(19,4))
replace all SQL Server statement terminators “GO” by the PostgreSQL synonym “;”
The next step will be to process the data, which can be done using the MS SQL Management Studio.
on the main pane right-click the database name, then select Tasks and Export Data popup menu items
go through all steps of the appeared wizard, select “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.

Once export is carried out, the exported data will appear in the destination file within the comma-separated values (CSV) format.

One of the most important challenges when migrating from SQL Server to PostgreSQL is correct and accurate handling binary data. The workaround below can help with this task.

Walk through the wizard until the option “Write a query to specify the data to transfer” appears. This wizard page is furthermore referred to as “Specify Table Copy or Query”. Write this SELECT-query on wizard page “Provide a Source 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 works for small and medium size binary data and goes into an infinite hang when applying to volumes 1MB and above.

How To Load The Resulting CSV File Into PostgreSQL

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 sequence of steps listed above indicates that database migration does require a lot of effort and is usually a complex process. Manual conversions are costly, time-consuming, and can often cause data loss or corruption leading to incorrect results. There are however modern tools available now, which can convert and migrate data between two DBMS in a few clicks, and the MSSQL-to-PostgreSQL converter is one of those tools.

The program vendor, Intelligent Converters that is focused on database conversion and synchronization techniques since 2001, created that tool to simplify the migration procedure for most of database entries.

The converter, upon direct link with both source and target databases, provides a high-quality conversion that doesn’t require ODBC drivers or any other middleware components. It also permits scripting, automation and scheduling of SQL Server to PostgreSQL migration via the command line.

Image by 200 Degrees from Pixabay

Tags: database, MySQL, PostgreSQL, server database, SQL Database Migration

About the Author

Bibhuranjan Editorial Officer, technofaq.org I'm an avid tech enthusiast at heart. I like to mug up on new and exciting developments on science and tech and have a deep love for PC gaming. Other hobbies include writing blog posts, music and DIY projects.