Migrating Data From MySQL to PostgreSQL

by ·24 Comments

A Little Back Story…

Migrating from MySQL to PostgreSQL is not as straight forward as it sounds.  As you may or may not know, MySQL’s ‘mysqldump’ utility comes with a “compatible” option where you can specific “postgresql” but the catch is this:  the resulting SQL is not properly escaped and will need it’s data types augmented.

In other words… or in my words, the SQL generated from mysqldump that’s meant to be compatible with postgresql is basically worthless in it’s exported state.

 

How I Migrated

Thanks to people smarter than me, (specifically Max Lapshin) the process of dealing with the above is pretty straight forward with the help of mysql2postgres!

Here is what I did to migrate my MySQL data —  a few million rows clocking in just under 1 gig.

 

Step 1:
Install the mysql2postgres gem.

Before you just go and clone the gem to your local system, take a look @ the forks as they might have something interesting you want.  At the time of this writing, maxlapshin’s repo had like 5 or 6 outstanding pull requests.  My example code will just use his repo as the clone source.

 

Step 2:
Configuration

After installation, the ‘mysql2psql’ command should be available.  If it’s not, just close your terminal, open a new one and navigate back to where you cloned the gem.

By default you won’t have a configuration file.  Run the ‘mysql2psql’ command and it will generate a default one for you.  Once you do that, just open it up and configure it to your heart’s desires.

 

Once you open the configuration file, just read it top to bottom.  Everything is commented and it’s very straight forward stuff.  Fill in your MySQL details, then fill in your postgres details (or use the file: option).

Last part is to run the migration.

Step 3:
Run the Migration

This part will *probably* be as simple as doing this:

 

If that worked for you then enjoy the sweet sweet glory of being migrated.  Go check your data, utf8 encodings and all that fun stuff.

If that did not work out and you got an error like the following… then you might be using a mac, along with homebrew and can easily fix it!

Le Error

The Fix (aka How to Install mysql-connector-c on a mac)

  1. FIRST get rid of homebrew’s mysql-connector-c with: ‘brew remove mysql-connector-c’
  2. Go to MySQL’s download page and grab the Connector/C for your flavor of OSX
  3. Extract it and do the following:
    1. Copy the lib/ files to /usr/local/lib
    2. Copy the include/ files to /usr/local/include
    3. Copy the bin/ files to /usr/local/bin
  4. Profit.

Note: I’m still not fully sure why this fixed it for me.  The version i install from MySQL is the same version Homebrew uses. :/