SQL Server to Postgres

Question

I am doing some research on migrating away from Microsoft sql server to postgresql and am looking for the best tools to get the job done.

Specifically looking for a tool similar to MySQL Migration Toolkit, I did a test migration of our databases to MySQL and had each one under way in under an hour.

It looks like pgAdmin will do most of what were doing with Sql Server Management Studio and the pg_stat tables will be good enough for performance tuning.

So what are the other key tools that everyone uses when developing for and working with postgresql ?

Answer 1

Maybe these links / tools would be useful?

http://wiki.postgresql.org/wiki/Microsoft%5FSQL%5FServer%5Fto%5FPostgreSQL%5FMigration%5Fby%5FIan%5FHarding

http://www.swissql.com/products/datamigration/data-migration.html

http://codebetter.com/blogs/karlseguin/archive/2009/10/22/migrating-to-postgresql-with-my-friend-nhibernate.aspx

http://www.enterprisedb.com/products/postgres%5Fplus%5Fas/overview.do

Answer 2

In migrating postgressql from one version to another which had no tools for it I used only one tool, unix's sed.

My recommendation is to simply export the sql. Clean up the export client specific eccentrics (usually in comments), then try importing it into a sql database and see what happens. The errors are your guidelines. Use sed to go through the mssql file and output the postgres sql file correcting the errors. Once you are in a functional environment, look at the data itself, some things may be other than you expected. After testing and further correction you are good to go.

Answer 3

I agree with Chisum. I would export the data from MSSQL as a SQL script (or a subset if it's huge - perhaps one table at a time or smaller ranges); and try running it against postgres; and see what happens. You may have to write a sed or a small perl script if there's any gnarlies to make it work right, but I wouldn't expect it to be ridiculous.

Import the data into postgres and see what happens. You're likely to run into more issues with the table structure, indices and references than other things.

Theres some info on the Postgres website here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server