March 23rd, 2008, 11:52
I was wondering if anyone could recommend a good solution to rather converting MySQL data from the server, into another server running PostGreSQL, or an application to convert a MySQL SQL file to PostGreSQL. Even something to pull the data out of a MySQL database into a PostGreSQL SQL file would be sufficient.

March 24th, 2008, 08:59
I'd take one of two tacks depending on the complexity of the db. If there's a lot of data arranged in only a few tables, I'd probably dump the MySQL tables in a simple format (eg CSV), then manually recreate the tables in PostgreSQL and import the data using psql ... \copy ... csv.

But it would be well worth looking into dumping the MySQL db as SQL statements, possibly massaging those a bit with a Perl or awk script, then feeding that to PostgreSQL. MySQL's SQL syntax is largely a subset of PostgreSQL, so that should work pretty well I'd think. Should be able to use pg_restore.

(I don't know specifically how to dump MySQL in SQL, but I know it's done regularly by MySQL admins. mysqldump maybe?)

This sounds promising ...

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

March 24th, 2008, 10:29
Sounds great. I tried using msyql2pgsql, and it seems that the new maintainer changed it so much that it doesn't just change a dump file to a pgsql dump file. One dump file I was able to change manually to work, and another I tried a Windows app, DBConvert in parallels which worked out flawlessly. I'll give a few others a shot, since I really don't want to purchase a converter. Thanks man.