Migrating Data From MySQL to PostgreSQL

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. :/

 

  • Chris

    What were your reasons for switching to PostgreSQL?

    • http://zigzo.com/ admin

      The main reasons:

      I use Heroku and want to conform to their system
      After some trivial tests i found pg to be more performant than mysql (for my needs)
      Full text search (but i’ve actually moved towards solr)
      Try something new :)

  • Ryan

    I am unable to install with your provided instructions:
    # rake install
    (in /usr/src/mysql2postgres)
    rake aborted!
    uninitialized constant Rake::DSL
    /usr/src/mysql2postgres/Rakefile:9
    (See full trace by running task with –trace)

    Any thoughts?

    • Anonymous

      Could you maybe post the entire error with the trace in a github gist (or pastie)?

    • http://alexbcoles.com Alex Coles

      The gem may be incompatible with Rake 1.9.x. Downgrade to Rake 1.8.7 as a workaround,

      • Anonymous

        It might be worth a shot to try that, but when i wrote the post i was definitely using
        ruby-1.9.2-p290. (Step 1 has the full terminal output including where i used RVM to swap over to ruby-1.9.2-p290)

        • http://alexbcoles.com Alex Coles

          Did you mean Ruby or Rake?

          • mariozig

            AH @myabc:disqus good catch. When i read your comment i read it as “ruby” not rake.  I actually did not pay attention to the version of rake when doing this so i cant really give any good input :/

  • Olivier

    thanks a lot for this helpful migration script !

    • Anonymous

      No problem, but just to be clear the author of the gem (aka the person who did all the work) is Max Lapshin — i just authored this post. :)

  • http://profile.yahoo.com/OPOG4CQ7FIS4ZGIST25DFCDWGE MarkF

    I think you meant “Run the ‘mysql2psql’ command [not mysql2postgres] and it will generate a default one for you.” 

    • mariozig

      Hey thanks a lot!  I’ve fixed the typo now.

  • Ed

    I’m using the current version available on GitHub. There’s no such thing as mysql2psql command after I installed it, it’s mysql-to-psql command instead. But this one raises an error every time I run it. When I checked it, it seems that the error is caused by the program calling itself in its require statement.

  • Andre Luiz Carvalho

    Thank you very much for posting this. I immediately found the solution when I googled for it. This sure saved me some serious time.