32

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 :)

Fred
  • 3,786
  • 7
  • 41
  • 52
  • you can use my answer here: https://stackoverflow.com/questions/6563846/how-to-migrate-a-postgresql-database-into-a-sqlserver-one/70241329#70241329 – Simin Ghasemi Dec 06 '21 at 05:48

6 Answers6

41

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.

Ehsan Mirsaeedi
  • 6,924
  • 1
  • 41
  • 46
  • 3
    Yes, as per step 1 the chosen driver architecture (32 vs 64 bit) is important. And somehow I needed the 32-bit ODBC driver to export data from 64-bit SQL Server Management Studio. I think it's running an external exporter tool that is still 32-bit. – Jonas May 15 '19 at 13:50
  • 2
    thanks, this works better than any other tools/ways i have tried. i had to change the name of the Driver. – Laurence Nov 27 '19 at 17:19
  • 2
    This helped a lot. A couple of gotchas for me, that might be helpful for future viewers: 1) You'll need to add a data source name for the Postgres driver 2) Make sure your data source name matches the value set for Drive={your-data-source-name} – mohsen Dec 09 '19 at 16:27
  • Will this method be scaleable, I have a database with 1.4 billion rows. Any idea how much time it will take? – Alex Ferguson Jun 07 '20 at 15:59
  • @AlexFerguson I don't how long it will take. It depends on the volume of your data. But as far as I know, this method is the fastest way for moving your data. – Ehsan Mirsaeedi Jun 09 '20 at 19:17
  • 6
    Modern driver has its name changed to `PostgreSQL Unicode`. – Vertigo Oct 26 '20 at 07:02
  • See also [this answer](https://stackoverflow.com/a/40667376/393672) if SSIS/DTS reports error about columns mapping. – Vertigo Oct 26 '20 at 09:33
  • 3
    Here's a [screenshot](https://i.stack.imgur.com/M3J2s.png) of my working configs for anyone interested. Installing both the 32-bit and 64-bit versions of the ODBC driver seemed to help me. – Rishi Latchmepersad Nov 25 '21 at 18:04
  • I'm getting the error 08001: password authentication failed for user "", but I use the same connection string on another software and works, anyone with the same problem? – kavain Jan 25 '22 at 13:49
  • I tried to follow this approach, and it works except at the last step, where the data types "binary" and "bit" are not properly recognized. The first one gets the name 128 (16 bytes), whereas the second one gets name "bool", and then leads to an error when importing. If I edit the Mappings by hand, then the errors disappear, however I have 10k+ fields, so I cannot do this by hand. Is there any trick on how to automatize this mapping conversion? – ferdymercury May 07 '22 at 22:21
8

Take a look at the Software Catalogue. Under Administration/development tools I see DBConvert for MS SQL & PostgreSQL. Probably there are other similar tools listed.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
  • 2
    perfect, that's exactly what I was looking for. I took it for a test drive, liked it so I purchased the full version and it's working like a champ. For those not looking for this specific solution, but solutions that are similar, they offer tools for converting and synchronizing between MSSQL, Postgres,Oracle,MS Access, Firebird, etc. I can only speak for the MSSQL/Postgres conversions, but if those conversions are any indication, this software package is well worth the $79. – Fred Sep 03 '09 at 16:10
  • @Milen A. Radev, +1 Great! – Sarfaraz Makandar Jun 06 '14 at 10:07
  • Warning! It adds %number%-TRIAL-%number% at the beginning for text fields when using the trial version. – Ashen One Dec 28 '21 at 16:11
5

You can use the MS DTS functionality (renamed to SSIS in the latest version I think). One issue with the DTS is that I've been unable to make it do a commit after each row when loading the data into pg. Which is fine if you only have a couple of 100k rows or so, but it's really very slow.

I usually end up writing a small script that dumps the data out of SQLServer in CSV format, and then use COPY WITH CSV on the PostgreSQL side.

Both those only take care of the data though. Taking care of the schema is a bit harder, since datatypes don't necessarily map straight over. But it can easily be scripted together with a static load of the schema. If the schema is simple (just varchar/int datatypes for example), that part can also easily be scripted off the data in INFORMATION_SCHEMA.

Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
0

Well there are .NET bindings for MS SQL Server 2005 (obviously) and also for PostgreSQL. So it would only take a few lines of code to code up a program that could transfer data safely from one to the other. The view would probably have to be done manually as Postgres doesn't use the same language for views as SQL Server.

Matthew Talbert
  • 5,998
  • 35
  • 38
  • yes, but that is going to require a lot of maintenance to keep up with over time, I'd prefer something that didn't require me to dive into code every time the DB schema changes. and if I'm making it generic then it becomes not so simple as now I'm having to make decisions based upon datatypes, etc. – Fred Sep 02 '09 at 23:01
  • Well, I don't think there is going to be an easy route. See here http://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding for some of the issues involved. It's much more likely that you will be able to code something that works in your situation than that someone will be able to create a tool that works for all. – Matthew Talbert Sep 03 '09 at 02:25
0

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

Mario Ariyanto
  • 157
  • 1
  • 1
  • 12
0

I had a problem with SSMS and bit values (1/0 in SqlServer, but in PostgreSql it is true/false) so I searched and stopped on DBeaver, it's free and pretty much maps the tables and columns all by himself so it's very easy. On a relatively small values (~100 tables, ~20mil rows) it performs fine, under 30min.
However, on large tables (~10mil+ rows) with significant amount of columns it may struggle. I ended up using DBeaver for everything except the big table which I exported via SSIS in CSV format and imported in PostgreSql via COPY command. Also dropping PK, Contraints, indexes on the large table and re-adding them after the migration helped a lot.
If you're running PostgreSql on linux, you may consider using FDW which can import schema and data without much effort.

Agasi Mkhitaryan
  • 162
  • 2
  • 11