Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2016
    Posts
    4

    Red face Answered: Tool for Migrating MS Sql Server to Postgres

    Hi All,

    Is there any open source tool that we can use for migrating MS SQL Server to Postgres. Here we are looking for only tables,views and data migration. No Stored procedures migration.

    Thank you in advance.

    Regards
    Mallan

  2. Best Answer
    Posted by shammat

    "When migrate databases to Postgres we usually just re-create all tables in Postgres either by doing smart search and replace on the original SQL scripts or by adjusting our Liquibase changesets to get rid of things that would not work in Postgres. Then we just dump the original database to CSV and import that into Postgres. We mainly use SQL Workbench/J for that sometimes we only use it for dumping data and then using Postgres' COPY command to import it, because that is a bit faster. It also has the ability to dump a database schema in a somewhat DBMS independent XML file that can be converted to a different SQL dialect through XSLT.

    what are the challenges involved? precautions that we need to take.
    The easy part are the syntax differences e.g. "||" vs "+" for string concatenation.

    As Postgres doesn't support cross-database queries and you use that in SQL Server you most probably want to map each database to a schema (unless you also use multiple schemas in a single database - then things will become a bit more complicated).

    You need to learn that for Postgres every statement has to be properly terminated with a ;

    Postgres (like many other DBMS) is also case sensitive when comparing strings - unlike SQL Server (at least with the default installation).

    One thing that SQL Server users seem to stumble upon very often are UPDATE statements using JOINs. In SQL Server you have to repeat the target table in the FROM clause.

    You must not do that in Postgres.

    The following T-SQL update statement:
    Code:
    update foo
       set foo.x = bar.y 
    from foo, bar
    where foo.id = bar.foo_id
    needs to be re-written to:
    Code:
    update foo
       set foo.x = bar.y 
    from bar --<< only the other table is listed here!
    where foo.id = bar.foo_id
    Another thing that might bite you is that SQL Server will not allow multiple NULL values in a unique index, whereas Postgres will. The following will fail in SQL Server, but will work in Postgres
    Code:
    CREATE TABLE foo (col1 integer, col2 integer);
    CREATE UNIQUE INDEX idx_foo ON foo (col1, col2);
     
    INSERT INTO foo (col1, col2) VALUES (1, null);
    INSERT INTO foo (col1, col2) VALUES (1, null);
    "


  3. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The only (free) one I know of is:

    https://github.com/megahbite/mssql2postgres

    But I have never used it.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Jun 2016
    Posts
    4
    Thank you shammat. Will look into it. Do you have any experience in migrating MS SQL to Postgres? if yes, Which tool did you use? what are the challenges involved? precautions that we need to take.

    Regards
    mallan

  5. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    When migrate databases to Postgres we usually just re-create all tables in Postgres either by doing smart search and replace on the original SQL scripts or by adjusting our Liquibase changesets to get rid of things that would not work in Postgres. Then we just dump the original database to CSV and import that into Postgres. We mainly use SQL Workbench/J for that sometimes we only use it for dumping data and then using Postgres' COPY command to import it, because that is a bit faster. It also has the ability to dump a database schema in a somewhat DBMS independent XML file that can be converted to a different SQL dialect through XSLT.

    what are the challenges involved? precautions that we need to take.
    The easy part are the syntax differences e.g. "||" vs "+" for string concatenation.

    As Postgres doesn't support cross-database queries and you use that in SQL Server you most probably want to map each database to a schema (unless you also use multiple schemas in a single database - then things will become a bit more complicated).

    You need to learn that for Postgres every statement has to be properly terminated with a ;

    Postgres (like many other DBMS) is also case sensitive when comparing strings - unlike SQL Server (at least with the default installation).

    One thing that SQL Server users seem to stumble upon very often are UPDATE statements using JOINs. In SQL Server you have to repeat the target table in the FROM clause.

    You must not do that in Postgres.

    The following T-SQL update statement:
    Code:
    update foo
       set foo.x = bar.y 
    from foo, bar
    where foo.id = bar.foo_id
    needs to be re-written to:
    Code:
    update foo
       set foo.x = bar.y 
    from bar --<< only the other table is listed here!
    where foo.id = bar.foo_id
    Another thing that might bite you is that SQL Server will not allow multiple NULL values in a unique index, whereas Postgres will. The following will fail in SQL Server, but will work in Postgres
    Code:
    CREATE TABLE foo (col1 integer, col2 integer);
    CREATE UNIQUE INDEX idx_foo ON foo (col1, col2);
     
    INSERT INTO foo (col1, col2) VALUES (1, null);
    INSERT INTO foo (col1, col2) VALUES (1, null);
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #5
    Join Date
    Jun 2016
    Posts
    4
    Hi Shamat,

    Indeed it's a good summary of migration process that will definitely help us. Thank you so much for your time.

    One more point that's bothering us is if SQL Server has portioned tables how it'll be migrated to Postgres. Some of our customers are having portioned tables. Any pointers in this direction will definitely help us.

    Regards
    Mallan

  7. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If you are talking about "partitioned" tables, then yes this is possible with Postgres - but currently it is really cumbersome to setup and has several limitations.

    See the manual for details: https://www.postgresql.org/docs/curr...titioning.html

    How big are those tables? And why are they partitioned?

    In my experience you can go a long way in Postgres without partitioning. With the current implementation I would only consider it for administrative purposes, e.g. when you need to delete a large amount of data from a large table, e.g. delete 10 million rows out of 100 million on a regular basis. In those cases simply dropping the partitions will be a lot faster.

    Partitioning is being worked on in Postgres and a "proper" implementation will very likely make it into the version after the upcoming 9.6 (which is currently in beta). So something like end of next year.

    If you do decide that you do need partitioning in Postgres you probably want to use something like pg_partman to make managing the partitions easier: https://github.com/keithf4/pg_partman
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #7
    Join Date
    Jun 2016
    Posts
    4
    Some customers are using partitions to better distribute the content. (on different disks). Right now we're evaluating DbConvert which I think does our job.

    Thank you so much for your help in explaining tips about migration.

    Regards
    Mallan

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •