Migrate from MySQL to PostgreSQL on Linux (Kubuntu)

A long time ago on a system far, far away...

Trying to migrate a database from MySQL to PostgreSQL. All the documentation I have read covers, in great detail, how to migrate the structure. I have found very little documentation on migrating the data. The schema has 13 tables (which have been migrated successfully) and 9 GB of data.

MySQL version: 5.1.x

PostgreSQL version: 8.4.x

I want to use the R programming language to analyze the data using SQL select statements; PostgreSQL has PL/R, but MySQL has nothing (as far as I can tell).

A New Hope

Create the database location (

/var

has insufficient space; also dislike having the PostgreSQL version number everywhere -- upgrading would break scripts!):

Extract the whole database definition from MySQL and adapt it to PostgreSQL syntax.

Go over the database definition and transform it to take advantage of functionality in PostgreSQL that doesn't exist in MySQL.

Then do the conversion, and write a program in whatever language you are most comfortable with that accomplishes the following:

Reads the data from the MySQL database.

Performs whatever transformation is necessary on the data to be stored in the PostgreSQL database.

Saves the now-transformed data in the PostgreSQL database.

Redesign the tables for PostgreSQL to take advantage of its features.

If you just do something like use a sed script to convert the SQL dump from one format to the next, all you are doing is putting a MySQL database in a PostgreSQL server. You can do that, and there will still be some benefit from doing so, but if you're going to migrate, migrate fully.

It will involve a little bit more up-front time spent, but I have yet to come across a situation where it isn't worth it.