Chomping At The Bits

Quickly Migrate a DB From MySQL to Postgres

The more I’m around people building apps in Ruby or ruining their quality of life with PHP, the more I’m seeing them actually use MySQL for something other than punch lines. As someone dedicated to trying to make the world a better place, I began looking for some easy ways to migrate from MySQL to Postgres. This post will will not even pretend to be an exhaustive list for how to do this, but more based around me having 15 minutes of free time and some internet.

The first place I went looking was Railscasts – They have a free intro episode to Postgres and one of the tools they show is Taps. I gem’ed taps real quick and gave it a shot, but I couldn’t get it to run due to port binding dependencies on my machine. No worries though, I still had plenty of tabs with search results so I moved on.

Valkyrie was the next option I tried. I gem’ed it up, pointed it at the DB’s and then it barfed on a ‘mysql’ dependency (not to be confused with mysql2 gem dependency). I installed the ‘mysql’ gem, ran it again and voilĂ ! Migration complete! The command issued was very simple:

valkyrie mysql://datachomp@localhost/seppuku?password=QuickAndPainless postgres://datachomp@

A couple things I like about Valkyrie:

  • It is simple. I just provide a mysql source and postgres destination.
  • It uses Sequel.
  • I can keep re-running it without it duplicating source data.

If you are needing something more hardcore than a 1 time migration, the ever resourceful Craig Kerstiens suggested I check out Tungsten-Replicator. Tungsten will essentially set up a read slave from MySQL to Postgres. Obviously, setup/configuration is going to be a bit more involved with Tungsten than a ~Fire-and-Forget~ migration tool like Valkyrie but remember, you are doing it for a good cause.

Now that you have seen an easy way to migrate away from MySQL, I encourage you to scan your network and start making the world a better place.