Converting a Database Schema from MySQL to PostgreSQL
Converting a Database Schema from MySQL to PostgreSQL
============================================
Database dump from MySQL to PostgreSQL easily with this PHP script
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 PostgreSQL
# MySQL comment –ANSI SQL comment
UNIQUE KEY name (cols) UNIQUE (cols)
PRIMARY KEY (cols) the same
KEY name (cols) CREATE INDEX name ON TABLE (cols)
id int NOT NULL auto_increment id SERIAL
binary doesn’t exist
enum doesn’t exist, convert to varchar
tinyint(n) or smallint(n) smallint
mediumint(n) or int(n) int
I also found that PostgreSQL doesn’t accept dates like 0000-00-00, so I had to strip default values FROM date/datetime columns.
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: $source\n”);
}
$fd = fopen($source, “r”);
$result = fread($fd, filesize($source));
fclose($fd);
$result = mysql2postgre($result);
$fd = fopen($output, “w”);
if (fwrite($fd, $result)) {
echo “OK\n”;
} else {
echo “Failed\n”;
}
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,}/”, “\n\n”, $result);
// get rid of proprietary code
$result = preg_replace(”/DROP TABLE IF EXISTS\W+.+/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.*KEY\W.+\((.+)\)/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.
