current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a rather large database (2 GB of data, 25 million rows in the largest table) on a shared web hosting. The database engine is Microsoft SQL Server 2008.

Due to high fees for the SQL Server web hosting, I would like to migrate my data from SQL Server to a Postgres database.

In my SQL Server database I have tables, indexes, constraints and stored procedures. The data types in my tables are VARCHAR, TINYINT, SMALLINT, INT, SMALLDATETIME, REAL and FLOAT.

In my stored procedures I am using some built-in T-SQL Date/Time manipulation functions like DateAdd() or DatePart().

Is there any FREE tool available that would help me migrate my data (tables, constraints, and if possible also the stored procedures) from SQL Server to Postgres?

share|improve this question

closed as off-topic by Paul White, RolandoMySQLDBA, marc_s, Michael - sqlbot, Mark Storey-Smith Feb 1 at 21:47

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Paul White, RolandoMySQLDBA, marc_s, Michael - sqlbot, Mark Storey-Smith
If this question can be reworded to fit the rules in the help center, please edit the question.
2  
There is a new project for this: github.com/dalibo/sqlserver2pgsql but I don't know how good this is. –  a_horse_with_no_name Jan 30 at 9:41
2  
Pg PL/SQL is quite different from T-SQL, so it may be overall cheaper to rewrite functions. Good luck with your migration! –  A-K Jan 30 at 15:21
2  
Please, just please, do not say you have a "rather large database" for something that tiny. –  TomTom Jan 30 at 17:39
    
Have a look at tPostgres, which essentially runs T-SQL against PostgreSql. openscg.com/2014/01/… –  A-K Jan 30 at 20:57
    
How many tables are there? If it is just a few, you could use an ETL tool like Pentaho. But it does not move data structures/code. –  Jayadevan Jan 31 at 5:38

2 Answers 2

up vote 0 down vote

I had written a process back in 2005 to use bcp to dump the data out then pg_import the data and do all the schema scripting and conversion necessary. Procs are slightly different because of syntax discrepancies.

share|improve this answer
up vote 0 down vote

TinyInt and SmallDateTime (AFAIK) are non-SQL Standard datatypes. EDIT: SQL Standard types nearest equivalents are, respectively, SMALLINT and TIMESTAMP. I suggest you do an conversion of columns using those types before the migration to see if you identify any issues with the application. It's your call to solve that before the migration or note that in a checklist for use after. The nearest MSSQL equivalent of TIMESTAMP (AFAIK) is DATETIME2.

I believe that most of the tables creation script is capable of running with few edits. Additional index creation can be more trouble if you used some unique capabilities of SQL Server. An decent DB case tool can make that transition less difficult (sorry, don't know of a free one - we use ER/Studio here for MSSQL).

Stored Procedures - that's where is the real pain. You have to learn the PL/pgSQL and translate your logic to it. Since tricks used on one db engine have very high chance of not working/getting very lousy performance on another, get an Postgres database developer to help you on that.
EDIT: While an DB case tool can do a decent job converting an db structure from an engine to another, I firmly believe that an tool to convert stored procedures from an procedure language to another can cause more harm than progress.

share|improve this answer
    
There is no DATETIME2 in Postgres (and in ANSI SQL). The ANSI types are date, time and timestamp –  a_horse_with_no_name Jan 30 at 17:39
    
Yes, it seems I'll have to use timestamp or 'timestamp with time zone' instead of SmallDateTime. –  jirikadlec2 Jan 31 at 10:45
    
@a_horse_with_no_name I though that modification as a step BEFORE the OP start making the PGSQL database - still in MSSQL2008 –  Fabricio Araujo Jan 31 at 16:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.