There are a number of mysql to postgresql converters available, but
many of them have significant shortcomings. Has anyone found a tool
that works well? I am trying to convert a couple of relatively large,
public schema to postgresql.

Thanks,
Sean

Search Discussions

  • Merlin Moncure at Sep 30, 2008 at 4:44 pm

    On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis wrote:
    There are a number of mysql to postgresql converters available, but
    many of them have significant shortcomings. Has anyone found a tool
    that works well? I am trying to convert a couple of relatively large,
    public schema to postgresql.
    It can for the most part be done with text replacement with a good
    editor, or use sed. It's not that difficult.

    The data is even easier...a lot of times you can import a mysql (data
    only) dump directly into postgresql if you pass the right options to
    mysqldump.

    There are several tools that automate this process. One such tool is
    DTS from microsoft. Lately though I see less and less value in things
    like this when the same thing can be done with regex/sed.

    merlin
  • Sean Davis at Sep 30, 2008 at 4:48 pm

    On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis wrote:
    There are a number of mysql to postgresql converters available, but
    many of them have significant shortcomings. Has anyone found a tool
    that works well? I am trying to convert a couple of relatively large,
    public schema to postgresql.
    I started playing with sqlalchemy (python) which can reflect a schema
    to python objects. Those objects can then be used to instantiate
    another schema in a different database dialect. Works like a charm
    after modifying a couple of column names. It mirrors about 4000
    tables in about 45 seconds (of course, without the data).

    Sean
  • Merlin Moncure at Sep 30, 2008 at 5:18 pm

    On Tue, Sep 30, 2008 at 12:48 PM, Sean Davis wrote:
    On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis wrote:
    There are a number of mysql to postgresql converters available, but
    many of them have significant shortcomings. Has anyone found a tool
    that works well? I am trying to convert a couple of relatively large,
    public schema to postgresql.
    I started playing with sqlalchemy (python) which can reflect a schema
    to python objects. Those objects can then be used to instantiate
    another schema in a different database dialect. Works like a charm
    after modifying a couple of column names. It mirrors about 4000
    tables in about 45 seconds (of course, without the data).

    Does it get all the various constraints and stuff (if any)? Simple
    field to field copy techniques only tends to work if the database only
    uses a small subset of common features. Great for you if it works
    though.

    merlin
  • Sean Davis at Sep 30, 2008 at 5:37 pm

    On Tue, Sep 30, 2008 at 1:18 PM, Merlin Moncure wrote:
    On Tue, Sep 30, 2008 at 12:48 PM, Sean Davis wrote:
    On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis wrote:
    There are a number of mysql to postgresql converters available, but
    many of them have significant shortcomings. Has anyone found a tool
    that works well? I am trying to convert a couple of relatively large,
    public schema to postgresql.
    I started playing with sqlalchemy (python) which can reflect a schema
    to python objects. Those objects can then be used to instantiate
    another schema in a different database dialect. Works like a charm
    after modifying a couple of column names. It mirrors about 4000
    tables in about 45 seconds (of course, without the data).

    Does it get all the various constraints and stuff (if any)? Simple
    field to field copy techniques only tends to work if the database only
    uses a small subset of common features. Great for you if it works
    though.
    To the extent that the MySQL databases used anything interesting
    (defaults, basically), it seems to, yes. I have used it for other
    projects as an ORM and it seems to support pretty much anything I can
    dream up on the postgres side for DDL.

    Sean
  • Chris at Sep 30, 2008 at 11:16 pm

    Sean Davis wrote:
    There are a number of mysql to postgresql converters available, but
    many of them have significant shortcomings. Has anyone found a tool
    that works well? I am trying to convert a couple of relatively large,
    public schema to postgresql.
    I couldn't find anything either but ended up using a pretty simple approach:

    - table only dump from mysql (ie no data)
    - convert is using sed/perl/whatever takes your fancy
    - do a "csv" type dump from mysql (select into outfile) (1/3 of the way
    down on http://dev.mysql.com/doc/refman/5.0/en/select.html).
    - use "copy" to import the data into postgres
    (http://www.postgresql.org/docs/8.3/interactive/sql-copy.html)

    That of course assumes you don't have to do any data munging in the
    middle (eg different formats for date/time fields).

    --
    Postgresql & php tutorials
    http://www.designmagick.com/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedSep 30, '08 at 2:08p
activeSep 30, '08 at 11:16p
posts6
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase