Mysql to postgres migration tool

2004 Aug 6 at 00:23 » Tagged as :

A couple of days ago I put up a simple script that can convert from mysql table structures to postgresql. Currently it does not work with the actual data but can give you a script that you can run on your postgres database to recreate the same set of tables that you find in your mysql db.

As for the importing the data itself, I am currently doing something about that as part of the Barbet project. Barbet is a database admin tool that I am working on for Rad Inks

It is far simpler to migrate the data using an updatable ResultSet than to use PHP or to use insert queries. The script that I have described above utilizes regular expressions (no doubt the purists are frowning) to convert the sql script from mysqldump to one that is compatible with postgres.

Doing the same with the actual data is more difficult because of it's much harder to predict what it may be. A s a result the insert statement would have to deal with quite a bit of type conversion and escaping special characters.

Updatable ResultSets on the other hand can be used to insert data without the use of an SQL query - well query does happen behind the scenes but you don't need to bother with the details yourself. The end result is that you do not need to bother with the type conversions or escaping special characters that we just talked about.

Even with PHP the so called improved mysql api (mysqli) let's you work with prepared statements and eliminates the need to escape special characters but the whole idea is to move away from mysql in the first place.