mtdev

Port your database dump from MySQL to PostgreSQL easily with this PHP script, the article also explains how it works.

The need for portability

You probably have heard of PEAR::DB, you may have even used it, if not let me EXPLAIN - it is a library for database abstraction. PEAR::DB is made to help people port their code to another database backend. That's very good but RDBMS's tend to use different syntaxes and people need to convert the SQL code too.

How it all began

I have used PEAR::DB for a couple of months (7 Jul 2002), and it works perfectly with the MySQL server, so I decided I need to test it against other databases too. As PostgreSQL is a nice Open Source database, a lot more advanced than MySQL, it seemed like a good choice for me.

I installed PostgreSQL FROM sources and it worked, I was able to connect through the bundled client, but this was the moment my problems started. I had a tough time testing different DSN's until I found that I only had to add unix before the database host and separate it with a + FROM it. It seems that the PostgreSQL driver defaults to TCP/IP sockets and the MySQL to Unix as I've always used unix sockets for MySQL.

Converting the schema

The first problem I encountered was that PostgreSQL doesn't like MySQL dumps, so after studying the PostgreSQL docs I came to this translation TABLE that I need to use.

MySQL default dumps FROM version 3.23.49 on use the ANSI SQL comment style with a required space after --.

I also found that PostgreSQL doesn't accept dates like 0000-00-00, so I had to strip default values FROM date/datetime columns.

Note: There is no need to convert the defaultINSERT statements as they are compatible with PostgreSQL, only the DATA types are different.

The convertor code

The code is written to be used FROM the commandline not a webserver. I recompiled PHP several times with different configure lines but it always said that $argv is undefined so the filenames are hard-coded.

You'll have to redefine $enum if it doesn't match your needs for a substitution of enum are different.

#!/usr/local/bin/php -q
<?php

$source = "/home/shaggy/shaggy.sql";
$output = "/home/shaggy/pgtest.sql";

$enum = 'varchar(10)'; // convert enum to this

if ( !file_exists($source) ) {
 die("File not found: $sourcen");
}

$fd = fopen($source, "r");
$result = fread($fd, filesize($source));
fclose($fd);

$result = mysql2postgre($result);

$fd = fopen($output, "w");
if (fwrite($fd, $result)) {
 echo "OKn";
} else {
 echo "Failedn";
}
fclose($fd);




function mysql2postgre($source) {
 global $enum;

 $result = $source;
 $result = preg_replace('/Type=MyISAM/i', '', $result);

 // convert line comments
 $result = preg_replace("/#(.*)/", '--$1', $result);
 // and compress newlines
 $result = preg_replace("/n{2,}/", "nn", $result);

 // get rid of proprietary code
 $result = preg_replace("/DROP TABLE IF EXISTSW+.+/i", '', $result);

 // indices
 $result = preg_replace("/(.*)UNIQUE KEY.+((.+))/i",
 "$1UNIQUE ($2)", $result);

 // a little hack to save primary keys
 $result = preg_replace("/(.*)PRIMARY KEY.+((.+))/i",
 "$1PRIMARY ($2)", $result);
 $result = preg_replace("/,n.*KEYW.+((.+))/i",
 "n-- was KEY ($1)", $result);
 $result = preg_replace("/(.*)PRIMARY.+((.+))/i",
 "$1PRIMARY KEY (\2)", $result);

 $result = preg_replace("/(.*?)(w+).+auto_increment/i",
 '$1$2 SERIAL', $result);

 // Postgre doesn't support the binary modifier
 $result = preg_replace('/binary/i', '', $result);

 // type transformations
 $result = preg_replace('/enum(.+)/i', $enum, $result);

 $result = preg_replace('/tinyint(.+)/i', 'smallint', $result);
 $result = preg_replace('/smallint(.+)/i', 'smallint', $result);
 $result = preg_replace('/meduimint(.+)/i', 'int', $result);
 $result = preg_replace('/int(.+)/i', 'int', $result);

 // Most of my default dates are '0000-00-00'
 $result = preg_replace("/datetime(.*) default '.*'/i",
 'datetime$1', $result);
 $result = preg_replace("/date(.*) default '.*'/i",
 'date$1', $result);


 return $result;
}

?>

I am using the PCRE regular expressions instead of POSIX because they are generally faster.

The first regular expression removes the Type=MyISAM identifier that is used in MySQL to set the TABLE type, change it if you use InnoBD or BDB tables.

The code should process any input but it is recommended to use a dump from mysqldump or a similar application.