Converting MySQL database to Postgresql
From MySQL to PostgreSQL
Articles and code to help porting MySQL software to PostgreSQL.
Filed Under: Programming · Date: Mon Sep 6 21:10:49 2004
The majority of Open Source applications utilizing a database backend are programmed only for the most common and easy to use RDBMS -- MySQL. Unfortunately, MySQL doesn't offer nearly any of the benefits of a serious database management system, and therefore it's often disgarded when selecting corporate database backends. Selecting something else than MySQL means you exclude many of the fine Open Source applications out there from your corporate (or other) infrastructure -- or then not.
Some time ago I wrote a patch to enable an Open Source wiki application to be used with PostgreSQL. Converting MySQL-only software to use some other backend is not hard -- it merely takes a bit of digging to find out how MySQL is not compatible with the SQL standard and how the "tweaks" in MySQL are achieved with SQL-conforming database management systems. This article is not about converting applications from MySQL to other RDBMS, but rather how to convert a MySQL database to Postgresql. I'm using WikkiTikkiTavi as an example.
The Database Schema
The first step in importing data from MySQL to Postgresql is to convert the database schema. This is often straightforward, but MySQL has some quirks which arent' compliant with other database management systems. These must be taking into account.
To determine the SQL schema, you can use the mysqldump utility to dump the database schema:
$ mysqldump -d -udbuser -pdbpasswd dbname >schema.sql
Once you have the schema on a file, you can start editing it to make the schema SQL-compliant and add features which are not present in MySQL. Note that you must remove all MySQL-specific schema options from the SQL file, like TYPE=MyISAM.
PostgreSQL doesn't support precision for simple types like integers. To make the dump work with PostgreSQL, remove precision from all simple types: e.g. int(n) turns into integer, and timestamp(n) into timestamp. Some MySQL developers like to make all attributes NOT NULL, but set the default to an empty value like an empty string('') or zero (0). This makes little sense, and, the software allowing, should be removed.
MySQL supports for defining domains for attributes with the SET pseudo-SQL. They can be safely converted into varchar or other compliant data type. However, if you wish to preserve the domain, you'll have to create it in a bit different way:
CREATE DOMAIN onoff AS VARCHAR CHECK VALUE IN ('on', 'off')The above SQL creates a domain called onoff which is of variable length string type. The domain can have two values, specified by the CHECK VALUE IN clause.
Finally, you can add foreign key checks to the schema: MySQL doesn't support them, so you don't find them in applications build on MySQL.
Export and import of data
When you have the schema in place, you can export the data using the mysqldump utility introduced earlier. Mysqldump uses simple inserts for the data, so we're not dealing with an application-specific utility. To dump and import your database, type:
$ mysqldump -tv -udbuser -pdbpass dbname >dump
$ psql -fdump pgdbnameBut don't hold your breath. The above commands will succeed only partially. Particularly importing the database contents. Postgresql emits alot of warnings for the dump, something like ERROR: column "time" is of type timestamp without time zone but expression is of type bigint. Looking at the dump, we see timestamps written in an alien form: 20040717234728 which is a date-time field without separators. We'll have to modify the dump a bit.
$ perl -pi~ -e "s/,2004(0\d)(\d{2})(\d{2})(\d{2})(\d{2})/,'2004-\$1-\$2 \$3:\$4:\$5'/g" dumb
$ psql -fdump pgdbnameThe simple Perl in-place replacement code looks for MySQL timestamp fields and converts them to SQL timestamps. The original dump is saved in dump~ before replacement -- so if something goes wrong, you'll have a backup of the original dump. Now you should have a working dump of WikkiTikkiTavi database and the import should work. To be sure of this, you should clear the Postgresql database you created if you imported the data before modifying the dump.
Conclusion
Converting from MySQL to Postgresql is not impossible -- it merely requires a bit of work, and knowledge of both database management systems. The largest challenge is locating and neutralizing MySQL's quirks.
When you have things running on PostgreSQL you can extend the database by using database functions, triggers, and views. The most valuable feature you can gain immediately from PostgreSQL is referential integrity and column constraints -- they move the logic of maintaining data integrity from applications to the database management system, where it belongs.
Comments
3
Strong words from you mr. Alonso. If my text is not what you like, I can't help it. And I'm not going to remove the line which you're referring to just because you find it offending. These are my opinions -- not lies -- derived from my experiences with relational database systems and alternative storage forms. With MySQL you just don't need to know how to store your data efficiently to be able to get high performance; but you still need to manage the data yourself in your applications -- a job for which relational databases were created for. Like Ian commented above, you need to use the InnoDB extension to MySQL to put it to any good use. Compared to standard MySQL, you could as well use SQLite. It's a standard part of PHP5, and offers higher performance, smaller disk and memory footprint, and is ACID.
4
Contrary to Ian's implications that InnoDB solves MySQL's faults with foreign keys, they are indeed broken. Applications may not take advantage of what is provided, but what is provided is substandard.
5
Please try our conversion script on gborg. It does a lot more than simply change the default timestamps, of course it does at least that much — http://pgfoundry.org/projects/mysql2pgsql/
6
Nice script Joesp! I was able to convert the schema of one large production system to PostgreSQL. However, the script segfaulted on a full MySQL dump. I’ll send you a mail about that, since the project site doesn’t have a tracker set up.
7
Joesp, the script mysql2pgsql has segfaulted me too. There is any update?
8
Hi, the segfault is because of PERL which can’t handle the large strings generated by MYSQL when it does the INSERT INTO DATABSENAME VALUES (SD,9,2),(2,3,9) .... 1000 times or so. I haven’t found a fix around it, but if you dump your DDL statements separately, you can just reload your data my piping it to mysql directly; the mysql2pgsql doesn’t reformat data anyway (that’s the DBA’s responsibility [ probably application-specific]) ....
thank you for this page, the project is a very good script, we are getting 450 downloads now per month, but we can go much, much higher if people can find it.
9
I’ve written tool for fast converting mysql dump into postgresql http://github.com/maxlapshin/mysql2postgres
10
No reason to be politically correct in your own blog. Great post. Many thanks for the info.
11
ESF Database Migration Toolkit, migrate data from MySQL to PostgreSQL directly in 3 steps, wizard-like, do not want any scripts. http://www.easyfrom.net/download/
Leave a comment
Use Textile to format you comment. Javascript must be enabled.
1
Contrary to the statement in this article, MySQL does support foreign key checking if the InnoDB engine is enabled and the relevant tables are configured correctly. That\'s not to say that many popular applications take advantage of this.
2
At least remove this line: "MySQL is fun as a first database management system. But start real work, and you'll notice it's no different from working with flat files." Every (SQL) database is very different from working with flat files. If you lie you can break your reputation and PostgreSQL reputation.