Migrating from MySQL to PostgreSQL in 60 seconds (with 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:
This first creates my PostgreSQL database tables using Migrations.rake migrate VERSION=1then…
rake mysql2pgsqlmysql2pgsql runs…and approx 60 seconds later, I had an exact copy of the MySQL database in PostgreSQL.
But wait! PostgreSQL has SEQUENCES… so I then run:
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!
