I recently decided to move one of my projects from MySQL to PostgreSQL for a number of reasons. I was looking for the easiest and quickest way to migrate the data. However, after playing with a number of solutions and tools, nothing was working well. Then I found this lovely rake task. The task uses rails to convert the data.

To convert your data, you will need to set up two databases. The rake task is designed to move data from the production database to the development database. To kick start things, download a backup of your production database and move it to your local computer. Next, establish a production MySQL database and then build the schema. rake db:schema:load RAILS_ENV=production. After the database is built, import the data to the local production database. mysql -u USER -p -D data_prod < backup.sql

Now, because the rake task moves data from a production database to a development database, you must configure your project’s database.yml file. This is what it could look like:

production:
  adapter: mysql2
  encoding: utf8
  username: rails_dev
  password: password
  host: localhost
  database: db_prod

development:
  adapter: postgresql
  encoding: utf8
  username: rails_dev
  password: password
  host: localhost
  database: db_dev

Note that the development database is set up for PostgreSQL. Therefore, before running the rake task you must establish this database as well. Build the database in PostgreSQL and then load the schema as above: rake db:schema:load RAILS_ENV=development. Also, do not forget to add a PostgreSQL adapter to your project’s Gemfile. I prefer the pg gem: gem "pg"

With the databases all set up, converting your data to the PostgreSQL database is as simple as:

rake db:convert:prod2dev

This worked great using ruby 1.9.2 and rails 3.0.8.