3

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?

5
  • 3
    There is a new project for this: github.com/dalibo/sqlserver2pgsql but I don't know how good this is.
    – user1822
    Jan 30, 2014 at 9:41
  • 3
    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, 2014 at 15:21
  • 1
    Have a look at tPostgres, which essentially runs T-SQL against PostgreSql. openscg.com/2014/01/…
    – A-K
    Jan 30, 2014 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, 2014 at 5:38
  • There's 25 tables in my DB. 10 of them are 'big' tables with my time-series data and the remaining ones are small metadata tables. I'll probably try sqlserver2postgresql to recreate the database schema and then take a look at Pentaho ETL tool to move the data. Jan 31, 2014 at 10:49

2 Answers 2

0

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.

0

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.

3
  • There is no DATETIME2 in Postgres (and in ANSI SQL). The ANSI types are date, time and timestamp
    – user1822
    Jan 30, 2014 at 17:39
  • Yes, it seems I'll have to use timestamp or 'timestamp with time zone' instead of SmallDateTime. Jan 31, 2014 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 Jan 31, 2014 at 16:46

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