Thread: MySQL to Postgresql schema conversion
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
On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis <sdavis2@mail.nih.gov> 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
On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis <sdavis2@mail.nih.gov> 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
On Tue, Sep 30, 2008 at 12:48 PM, Sean Davis <sdavis2@mail.nih.gov> wrote: > On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis <sdavis2@mail.nih.gov> 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
On Tue, Sep 30, 2008 at 1:18 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Tue, Sep 30, 2008 at 12:48 PM, Sean Davis <sdavis2@mail.nih.gov> wrote: >> On Tue, Sep 30, 2008 at 10:08 AM, Sean Davis <sdavis2@mail.nih.gov> 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
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/