CODEWITHSUNDEEP
sql-serverpostgresql
Lance
Lance

Reputation: 312

SQL Server to Postgres

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 ?

Upvotes: 9

Views: 6258

Answers (5)

Rahul Desai
Rahul Desai

Reputation: 1

Liquibase can help you in converting schema from MS SQL to PostgreSQL specific schema.(Few manual interventions required but its quite useful) For Migrating the data ,You can export the SQL Server into csv and import it into postgresql.

Upvotes: 0

Ispirer SQLWays Migrations
Ispirer SQLWays Migrations

Reputation: 1058

It depends on what exactly you need to convert from SQL Server to PostgreSQL. If you are talking about the DDL and data conversion process only, then you might try to export everything with SQL Server Management Studio as suggested, try running this script into the PostgreSQL database via PGAdmin or psql utility and try fixing the errors triggered during the import. But please note that even in that case there might be issues with the RESERVED WORDS conversion, IDENTITY conversion, calculated columns conversion, multilingual data conversion, clustered indexes conversion and others. Not sure if REGEXP can help you in resolving the issues connected with these features conversion.

If you are talking about the Business Logics objects conversion, like triggers, SPs, UDFs, then there will be much more issues in the overall conversion process even using the existing free utilities that does the migration for the SQL Server to PostgreSQL databases, because the syntax and techniques for specifying and maintaining of this or that operation in both databases are really different. Even if you are a real GURU in REGEXP in such a case, there will be really hard to automate the code change or code re-factoring in support of specifics of the source database. The most common features are: built-in functions, hierarchical queries, errors processing, transactions processing, result-sets returning and processing, Dynamic-SQL, statement triggers conversion and many others.

So, depending on your requirements, you can use the free of charge solutions, that in most cases require additional significant manual efforts in the conversion process.

Upvotes: 0

user192230
user192230

Reputation:

In migrating postgresql 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.

Upvotes: 3

Fiid
Fiid

Reputation: 1840

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

Upvotes: 2

anon
anon

Reputation:

Maybe these links / tools would be useful?

http://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding

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_plus_as/overview.do

Upvotes: 5

Related Questions