migrate data from MS SQL to PostgreSQL?

views:

478

answers:

+1 Q:

migrate data from MS SQL to PostgreSQL?

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

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 2009-09-02 22:31:30

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 2009-09-02 23:01:56

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 2009-09-03 02:25:19

+1 A:

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 2009-09-02 23:56:45

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 2009-09-03 16:10:05

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 2009-09-03 07:34:25

ansaurus

tags:

views:

answers:

migrate data from MS SQL to PostgreSQL?

related questions