How to migrate database from SQL Server to PostgreSQL

[Validate Migration] [Types] [Queries] [Triggers]

Microsoft SQL is one of the world's most popular database management systems with user-friendly interface that is easy to learn and use. However, it has certain disadvantages such as high cost of ownership for large databases and strict licensing policy. These facts may lead some users to the need of shifting from MS SQL to another DBMS.

Why PostgreSQL?

When planning a database migration, it is reasonable to review open-source systems in order to reduce total cost of ownership. There are two major open-source RDBMS: MySQL and PostgreSQL.

MySQL provides a lot of features that are expected from advanced RDBMS such as security, scalability, wide range of supported storage types for different purposes. However, it does not implement the full SQL standard, does not provide full text search and has poor support for concurrent writes in some database engines.

PostgreSQL is a very powerful standards-compliant RDBMS providing integral object-oriented and/or relational database functionality. This all make it the best choice for projects that require high level of reliability and data integrity.

Migration Steps

Here is the sequence of steps to migrate a database from SQL Server to PostgreSQL:

export table definitions from the source SQL database
convert them into PostgreSQL format
load the resulting statements to PostgreSQL server
export MS SQL data into intermediate storage
convert it into PostgreSQL format and load into the target database

Now let's consider these steps more closely. This is how you can export SQL Server table definitions:

in SQL Server 2008 and earlier you can script objects and data. Right-click on database in Management Studio, Tasks, Generate Scripts. Go through the wizard and make sure to check "data" which is false by default.

in SQL Server 2012 and later: right-click on database in Management Studio, Tasks, Generate Scripts. On the "Set scripting options" tab click on Advanced, then select "data only", or "data and schema" for "Types of data to script" (in the General section).

The resulting script must be corrected before loading to PostgreSQL as follows:

remove SQL Server specific statements (i.e. "SET ANSI_NULLS ON", "SET QUOTED_IDENTIFIER ON", "SET ANSI_PADDING ON")
replace square brackets around database object names by double quotes
remove square brackets around types
replace default SQL Server schema "dbo" by PostgreSQL "public"
remove all non-supported optional keywords (i.e. "WITH NOCHECK", "CLUSTERED")
remove all reference to filegroup (i.e. "ON PRIMARY")
replace types "INT IDENTITY(...)" by "SERIAL"
update all non-supported data types (i.e. "DATETIME" becomes "TIMESTAMP", "MONEY" becomes NUMERIC(19,4))
replace the MS SQL query terminator "GO" with the PostgreSQL one ";"

Next step is to migrate data from SQL Server to PostgreSQL. It also can be done via Microsoft SQL Management Studio: right-click on database, Tasks, Export Data. Go through the wizard and select "Microsoft OLE DB Provider for SQL Server" as data source, "Flat File Destination" as destination. After the export is completed, you can see all data exported into the specified file in comma-separated values (CSV) format.

If some table contains binary data it is required to apply the workaround. Select "Write a query to specify the data to transfer" option on the wizard page called "Specify Table Copy or Query". On the next wizard page called "Provide a Source Query" you should compose SELECT-query as follows:

select <non-binary field #1>, <non-binary field #2>, cast(master.sys.fn_varbintohexstr(
	cast(<binary field name> as varbinary(max))) as varchar(max)) as <binary field name> 
from <table name>;

Unfortunately, this approach is not applied for large binary data (1MB+) since the query goes into infinite hang. You can import the resulting CSV file into PostgreSQL table via "COPY" command:

COPY <table name> FROM <path to csv file> DELIMITER ',' CSV;

If you see "Permission denied" error after running this statement, try to use "\COPY" command instead.

Tools to migrate from SQL Server to PostgreSQL

As you may see, database migration from SQL Server to PostgreSQL is a complicated time-consuming process requiring a lot of efforts. Manual conversion is costly and slow process, it can lead to incorrect results and cause data loss or corruption. Fortunately, there are many free tools which can partially automate migration between those two DBMS:

SQL Server to PostgreSQL Converter

In the previous section we explored the most popular free tools that can simplify SQL Server to PostgreSQL database migration. However, each of those solutions requires a lot of efforts to install prerequisites and set up configuration of the main product and supplementary tools.

Customers who need to run the migration procedure with just a few clicks of mouse button should consider advanced commercial solutions being solely designed for this purpose. Intelligent Converters offer one of such solutions - SQL Server to PostgreSQL converter.

This product supports all modern on-premises and cloud variations of SQL Server and PostgreSQL (Heroku, Azure, Amazon RDS, Google Cloud). It migrates schemas, sequences, data, indexes, constraints and views with all required properties and attributes. All SQL Server types are mapped into PostgreSQL equivalents, including image, xml, spatial and other complicated types.

SQL Server to PostgreSQL converter achieves high performance of the database migration due to efficient low-level techniques of reading and writing data, it does not use ODBC or another middleware. It allows to customize a lot of migration rules (types mapping, use insert or copy techniques to load the data to PostgreSQL, etc) to tailor the migration right to the customer's project. Option of filtering and preprocessing the data through SELECT-queries and make the migration procedure even more flexible and customizable.

For those cases when PostgreSQL does not allow direct connection, SQL Server to PostgreSQL converter can export the source database into SQL script complied with PostgreSQL. That script contains SQL statements to create all required database objects and load the data.

Have questions? Contact us