24.1 Migrating from MySQL to PostgreSQL

MySQL databases are in widespread use among Web developers. Although MySQL lacks many functions provided by sophisticated database programs such as DB2, Oracle, PostgreSQL, or many others, people like MySQL because of its simplicity.

In this section, you will see how to migrate data from MySQL to PostgreSQL.

24.1.1 A Brief Installation Guide

The first thing is to get started with MySQL. We recommend downloading a binary version of MySQL. In this section version 4.0.1 has been used. You can easily install the package using the instructions in the manual.

After installing the binaries and starting the daemon, you can use the interactive shell to connect to the database:

 [root@duron mysql]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 to server version: 4.0.1 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 

MySQL offers an interactive shell. It also provides some basic commands such as \h:

 mysql> \ h MySQL commands: Note that all text commands must be first on line and end with ';' help    (\h)    Display this help. ?       (\?)    Synonym for `help'. clear   (\c)    Clear command. connect (\r)    Reconnect to the server. Optional arguments are db and host. edit    (\e)    Edit command with $EDITOR. ego     (\G)    Send command to mysql server, display result vertically. exit    (\q)    Exit mysql. Same as quit. go      (\g)    Send command to mysql server. nopager (\n)    Disable pager, print to stdout. notee   (\t)    Don't write into outfile. pager   (\P)    Set PAGER [to_pager]. Print the query results via PAGER. print   (\p)    Print current command. quit    (\q)    Quit mysql. rehash  (\#)    Rebuild completion hash. source  (\.)    Execute a SQL script file. Takes a file name as an argument. status  (\s)    Get status information from the server. system  (\!)    Execute a system shell command. tee     (\T)    Set outfile [to_outfile]. Append everything into given outfile. use     (\u)    Use another database. Takes database name as argument. Connection id: 3  (Can be used with mysqladmin kill) 

As you can see, the terminal offers many functions you can use to work with the database.

In the next step, it is time to create a database. In this example the database will be called phpbook:

 mysql> CREATE DATABASE phpbook; Query OK, 1 row affected (0.07 sec) 

After that the database is ready for action.

24.1.2 Migrating Data to PostgreSQL

In this section you will take a closer look at how data can be migrated from MySQL to PostgreSQL. You will need a MySQL database containing some data:

 USE phpbook; CREATE TABLE product (         product_id integer NOT NULL AUTO_INCREMENT PRIMARY KEY,         product_name varchar(200),         description text,         price double,         currency char(3) ); INSERT INTO product (product_name, description, price, currency) VALUES         ('Hamburger', 'burger filled with meat', '1.59', 'USD'),         ('Cheeseburger', 'burger filled with meat and cheese', '1.69', 'USD'),         ('French Fries', 'fatty potatoes', '0.99', 'EUR'),         ('Donut', 'somthing which makes you fat', '1.49', 'EUR'); 

The script contains one table containing four records. The goal is to migrate this database to PostgreSQL. Before doing, this you have to insert it into your MySQL database:

 [root@duron mysql]# mysql phpbook < data.sql 

The next listing shows that the data has been added to the database successfully:

 mysql> SELECT * FROM product; +------------+--------------+------------------------------------+-------+- ---------+ | product_id | product_name | description                        | price | currency | +------------+--------------+------------------------------------+-------+- ---------+ |          1 | Hamburger    | burger filled with meat            |  1.59 | USD      | |          2 | Cheeseburger | burger filled with meat and cheese |  1.69 | USD      | |          3 | French Fries | fatty potatoes                     |  0.99 | EUR      | |          4 | Donut        | somthing which makes you fat       |  1.49 | EUR      | +------------+--------------+------------------------------------+-------+- ---------+ 4 rows in set (0.02 sec) 

Just likePostgreSQL, MySQL is able to dump databases to ASCII files. This is a comfortable feature because ASCII dumps are a portable file format. To dump the database, you can use the mysqldump command.

Let's look at the dump:

 -- MySQL dump 8.19 -- -- Host: localhost    Database: phpbook --------------------------------------------------------- -- Server version       4.0.1 -- -- Table structure for table 'product' -- CREATE TABLE product (   product_id int(11) NOT NULL auto_increment,   product_name varchar(200) default NULL,   description text,   price double default NULL,   currency char(3) default NULL,   PRIMARY KEY  (product_id) ) TYPE=MyISAM; /*!40000 ALTER TABLE product DISABLE KEYS */; -- -- Dumping data for table 'product' -- INSERT INTO product VALUES (1,'Hamburger','burger filled with meat',1.59,'USD'); INSERT INTO product VALUES (2,'Cheeseburger','burger filled with meat and cheese',1.69,'USD'); INSERT INTO product VALUES (3,'French Fries','fatty potatoes',0.99,'EUR'); INSERT INTO product VALUES (4,'Donut','somthing which makes you fat',1.49, 'EUR'); /*!40000 ALTER TABLE product ENABLE KEYS */; 

Now it is time to convert the MySQL database to PostgreSQL. You can use the Perl scripts that can be found in the contributed directory of your PostgreSQL source code. In this example, the following command is used to perform the conversion:

 mysqldump phpbook | ./my2pg.pl 

Simply pipe the output of the dump to my2pg.pl. my2pg.pl will take care of the result and PostgreSQL-compliant SQL code will be generated. Do not pipe the result to PostgreSQL directly because there could be minor errors in the PostgreSQL code. When running this demo application, blanks were missing that led to syntax errors. Things like that must be checked by the user migrating the data. After checking the SQL file, you should have something like this:

 -- My2Pg $Revision: 1.6 translated dump -- ------------------------------------------------------------------ BEGIN; -- -- Sequences for table PRODUCT -- CREATE SEQUENCE product_product_id_seq; -- MySQL dump 8.19 -- -- Host: localhost    Database: phpbook --------------------------------------------------------- -- Server version       4.0.1-alpha -- -- Table structure for table 'product' -- CREATE TABLE product (   product_id INT4 DEFAULT nextval('product_product_id_seq'),   product_name varchar(200) DEFAULT NULL,   description text,   price FLOAT8 DEFAULT NULL,   currency char(3) DEFAULT NULL,   PRIMARY KEY  (product_id) ); /*!40000 ALTER TABLE product DISABLE KEYS */; -- -- Dumping data for table 'product' -- INSERT INTO product VALUES (1,'Hamburger','burger filled with meat',1.59,'USD'); INSERT INTO product VALUES (2,'Cheeseburger','burger filled with meat and cheese',1.69,'USD'); INSERT INTO product VALUES (3,'French Fries','fatty potatoes',0.99,'EUR'); INSERT INTO product VALUES (4,'Donut','somthing which makes you fat',1.49, 'EUR'); /*!40000 ALTER TABLE product ENABLE KEYS */; -- -- Sequences for table PRODUCT -- SELECT SETVAL('product_product_id_seq',(select case when max(product_id)>0 then max(product_id)+1 else 1 end from product)); COMMIT; 

This code can be sent to PostgreSQL now:

 [root@duron mysql]# psql phpbook < /tmp/pgcode.sql BEGIN CREATE NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'product_pkey' for table 'product' CREATE INSERT 49615 1 INSERT 49616 1 INSERT 49617 1 INSERT 49618 1  setval --------       5 (1 row) COMMIT 

No errors have occurred and the entire database has been converted successfully. Internally my2pg.pl does nothing except reformat the MySQL code to code that, in most cases, complies with both ANSI SQL and PostgreSQL.

Migrating data from PostgreSQL is much harder if not impossible. Because of PostgreSQL's additional features, it is impossible to port a complex application back to MySQL. The reason is that functions provided by PostgreSQL cannot be modeled with the help of MySQL because no sophisticated embedded languages are available.

24.1.3 Migrating Functions

After migrating the data, it is necessary to port the SQL code of your application to PostgreSQL. This can be a painful task because MySQL is not as ANSI-SQL compliant as PostgreSQL, so porting SQL can be painful. Just take a look at the following example:

 mysql> SELECT CONCAT("a", "b"); +------------------+ | CONCAT("a", "b") | +------------------+ | ab               | +------------------+ 1 row in set (0.03 sec) 

The CONCAT function is not ANSI-SQL compliant. Because MySQL does not support the || operator for connecting strings, the CONCAT function was invented. When porting your application to PostgreSQL, you must make sure that functions like that are removed and replaced by proper ANSI SQL code or functions written in an embedded language.