[PostgreSQL] MySQL to Postgresql schema conversion

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

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

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

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

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).