Published On: Wed, Mar 22nd, 2017

Migrate database from MSSQL to PostgreSQL

The user-friendly capabilities and easy to learn interface of the Microsoft SQL (MS SQL), makes it to be one of the most widely known database management system (DBMS) in this field. Nevertheless, the system has two significant drawbacks that could sometimes suggest users to lookout for a replacement for the DBMS. This disadvantages includes:

  • Strict licensing policies
  • High cost of ownership (not good for owners of large databases)

Evaluating the open-source databases is advisable so as to cut-down on total cost of ownership. In this category, you’ll find three leading open-source databases such as:

  • SQLite
  • MySQL
  • PostgreSQL.

SQLite can be referred to as a document-based database and a self-contained database system, created and integrated only into programs, therefore, it cannot be used in the multi-user environment as an alternative for large databases.

The MySQL, on the contrary, is more robust. It offers features that are typically of a sophisticated RDBMS. These functions consist of: scalability, security, and various storage units for various functions. A few of its disadvantages consist of,

  • No assistance for full text search
  • Does not carry out the full SQL standard
  • Inferior support for parallel writes in certain database engines.

The PostgreSQL is the normal RDBMS that comes with a relational feature as well as abuilt-in object-oriented database functionality. This is one of the features that makes it the best option when it comes to data integrity with high level of reliability.

To effectively move database from MS SQL to PostgreSQL, the following need to be done:

  • export MS SQL table definitions
  • convert them to the PostgreSQL format
  • load the results to a PostgreSQL server
  • export the source SQL data to an intermediate storage
  • convert data to the PostgreSQL format
  • Load into the target database.

The Best Way To Export MS SQL Table Definitions

  • For the SQL 2008 and earlier versions; 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 2012 and later versions: right-click on database in Management Studio, then click on Tasks, Generate Scripts. On the “Set scripting options” tab click on Advanced, and select “data only”, or “data and schema” for “Types of data to script” (in the General section).

Correct the resulting script before you proceed to the next step.

The Best Way To Load Results To Postsql

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

The next move is to process the data, which can be accomplished with the use of the MS SQL Management Studio.

  • Right-click on database, then click Tasks, Export Data
  • Go through the wizard and select “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.

As soon as the export is finalized, the exported data will exists in the destination file in the comma-separated values (CSV) format.

Workaround must be employed if some of the tables contain binary data. To achieve this, click on the “Write a query to specify the data to transfer” option after going through the wizard page. This wizard page is also called the “Specify Table Copy or Query”. On the next wizard page known as “Provide a Source Query”, create the following SELECT-query:

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>

The query runs into an infinite hang, this method is not suitable for heavy binary data, let’s say 1MB and above.

The Best Way To Load The Finished CSV File Into The Target Postgresql Table

* Use the “COPY” as follows: COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;

Try the “\COPY” command if you receive a “Permission denied” error message with the “COPY” command.

The series of actions mentioned above implies that the database migration requires a whole lot of effort and most times, it is often an advanced procedure.

Manual conversions are expensive, time-consuming, and may often result to data loss or corruption which could lead to inaccurate results. You can find modern-day solutions, which can transform and migrate data between two DBMS in a couple of clicks. One of these solutions is SQL Server to PostgreSQL migration tool by Intelligent Converters, a software vendor, who specializes in database conversion and synchronization techniques. They have been in this business since 2001.

With direct connection to both the source and target databases, the tool offers a high performance conversion that doesn’t require ODBC drivers or other middleware elements. It also enables scripting, automation and scheduling of conversions.

About the Author