Pgify: automated sytax conversion to Postgres from MySQL
12/11/2010Check out pgify (the web version) and migrate from MySQL to PostgreSQL .
Pgify is able to completely convert all of my MySQL databases to Postgres in a single step, including those from Wordpress, Joomla and Serendipity. It converts KEY declarations on tables to indexes and even parses AUTO_INCREMENT values correctly to Postgres sequences.
Issues during rewriting
There are a few issues that you should be aware of:
** String escaping ** : By default MySQL uses some weird non-standard string escapes . Fortunately, Postgres can handle these by prefixing strings with an 'E', as in E'a new line\n'. By default, Pgify will do this automatically.
** ON UPDATE ** : MySQL tables can have ON UPDATE values. Pgify will convert these to triggers.
** SQL_CALC_FOUND_ROWS/FOUND_ROWS ** : This is not supported in Postgres. Currently FOUND_ROWS can be emulated using the Pgify API by manually setting WalkerState->found_rows, but in stand-alone mode it always returns 1.
** Backtick (`) quotes ** : These are a bit of a problem, since Postgres doesn't have case-insensitive (but case-preserving) quoting. To get similar functionality, Pgify must lower-case all identifiers since a double-quoted string is case-sensitive. This works fine for most stuff.
Except for bleedin' PHP's result objects. They're case-sensitive for some crazy reason. PHP apps that rely on case-sensitive column names (freakin' Wordpress) may have a few issues. For best results, just lower-case everything.
** Encoding and collations ** : Confusingly, each database, table and column can have different encoding settings. Postgres only supports encoding on the database, which in my mind is much easier. Still, if your schema has different settings, then you may have some issues. Pgify has to drop all CHARSET options. For best results, make everything UTF-8.
** Enum values ** : Pgify will convert these to CHECK statements.
** MyISAM tables ** : In MySQL, you can specify foreign keys and they will be stored with the table definition. But if you happen to be using MyISAM storage, those constraints will be silently ignored. Then, when Pgify will converts those tables, Postgres will enforce integrity and the data load may fail. If this is you, drop those unused constraints before converting to Postgres.
** SHOW FIELDS/SHOW TABLES/SHOW DATABASES ** : Pgify will convert these to Postgres equivalents using information_schema.
** Databases ** : In Postgres, the client can't just change databases like in MySQL. Databases are more equivalent to Postgres schemas. Pgify will convert all CREATE/DROP/USE database statements to use schema equivalents. The catch is, your user must have the search_path option set correctly to use these schemas. For example, "SET search_path TO test,public", or it can be made permanent using ALTER USER.
That's everything I can think of now. Download the source at Github.