This post originated from an RSS feed registered with Ruby Buzz by Robby Russell. Original Post: Migrating from MySQL to PostgreSQL in 60 seconds (with Rails) Feed Title: Robby on Rails Feed URL: http://www.contegix.com/rss/feed.xml Feed Description: My reflections on programming with Ruby, Rails, PostgreSQL... and learning how to run a business... Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Robby Russell Latest Posts From Robby on Rails

I do quite a few MySQL to PostgreSQL migrations for clients of PLANET ARGON and over the years I have gone through different methods of handling this procedure. Typically, it involved exporting data, re-importing it through some shell script that does its best to take into consideration the slight differences between the two databases.

While working on a recent project that was to be migrated from MySQL to PostgreSQL, but after some Refactoring had been done to the code base. I had been meaning to spend a few hours coming up with some simplified process of taking data from MySQL and replicating it to a new PostgreSQL database, while maintaining referential integrity.

My original idea was to build a script that referenced two seperate databases and then copied data out and inserted it into the new db after performing a few data changes. This seemed like too much work and I knew that I should be able to harness the power of Ruby and Rails in my process. My next thought? RailsFS.

In a perfect world, I would mount one instance of the application with a mysql database and another with a postgresql database… and just copy the yaml files to the postgresql and bam, it’d work, right? Well, it doesn’t quite work that way. So, I ruled out that idea.

Then I had another idea. I would build a task in my Rakefile that… imported each of those YAML files into PostgreSQL!

We can now type in the following commands:

rake migrate VERSION=1

then…

rake mysql2pgsql

rake migrate VERSION=2

...and my SEQUENCES are ALTER’d.

Yes, I will post some code in the near future. But all I am doing is basically running through all the YAML files that are available from RailsFS and adding them into the new database. It automatically figures out the model name…and is fairly generic at the moment. However, I’d like to spend a bit more time stress-testing it before I post it. :-)

I want to try to work on a RailsFS-less version as well so that I can run this on my PowerBook.

Until next time… have fun!