migrate data from MS SQL to PostgreSQL?

user_119513 0
Viewed 49091

I've looked around and can't seem to find anything that answers this specific question.

What is the simplest way to move data from an MS SQL Server 2005 DB to a Postgres install (8.x)?

I've looked into several utilities like "Full Convert Enterprise", etc, and they all fail for one reason or another, ranging from strange errors that make it blow up to inserting nulls rather than actual data (wth?).

I'm looking at a DB with all table except for a single view, no stored procs, functions, etc.

At this point I'm about to write a small utility to do it for me, I just can't believe that's necessary. Surely there's something somewhere that can do this? I'm not even too worried about cost, although free is preferable :)

migration postgresql sql-server
5 Answers

I don't know why nobody has mentioned the simplest and easiest way using robust MS SQL Server Management Studio.

Simply you just need to use the built-in SSIS Import/export feature. You can follow these steps:

  1. Firstly, you need to install the PostgreSQL ODBC Driver for Windows. It's very important to install the correct version in terms of CPU arch (x86/x64).

  2. Inside Management Studio, Right click on your database: Tasks -> Export Data

  3. Choose SQL Server Native Client as the data source.

  4. Choose .Net Framework Data Provider for ODBC as the destination driver.

  5. Set the Connection String to your database in the following form:

    Driver={PostgreSQL ODBC Driver(UNICODE)};Server=;Port=;Database=;UID=;PWD=

  6. In the next page, you just need to select which tables you want to export. SQL Server will generate a default mapping and you are free to edit it. Probably you`ll encounter some Type Mismatch problems which take some time to solve. For example, if you have a boolean column in SQL Server you should export it as int4.

Microsoft Docs hosts a detailed description of connecting to PostgreSQL through ODBC.

PS: if you want to see your installed ODBC Driver, you need to check it via ODBC Data Source Administrator.

user_17807600

This answer is to help summarize current connection string because someone may overlooked the comment.

Current version of ODBC connection string is:

For 32-bit system

Driver={PostgreSQL UNICODE};Server=192.168.1.xxx;Port=5432;Database=yourDBname;Uid=postgres;Pwd=admin;

For 64-bit system

Driver={PostgreSQL UNICODE(x64)};Server=192.168.1.xxx;Port=5432;Database=yourDBname;Uid=postgres;Pwd=admin;

You can check the driver name by typing ODBC in windows search. And open ODBC Data Source Administrator

user_96726510
Related