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:
With the databases all set up, converting your data to the PostgreSQL database is as simple as:
This worked great using ruby 1.9.2 and rails 3.0.8.