paul doerwald

I've taken over a Rails project whose database is MySQL. While I've grudgingly come to respect MySQL, I still vastly prefer PostgreSQL, largely because I'm more familiar with it. I decided to take the plunge and migrate the project from MySQL to PostgreSQL.

A search for MySQL to Postgres conversion scripts turns up pretty empty, but then it struck me that I can do the conversion using ActiveRecord's schema dumping. The problem with this approach is that AR ignores constraints, foreign keys, etc., but in my case, the data I was migrating used none of those. Then to copy the data, I used MySQL's select into outfile feature and PostgreSQL's copy from file feature. The steps were:

1. I created a postgres database application_development and put it in the test section of config/database.yml.
2. I dumped the current schema by running rake db:schema:dump.
3. I loaded that schema by running RAILS_ENV=test rake db:schema:load.
4. Grant your MySQL user file privileges. Log on to MySQL as root: mysql -u root, and then do the grant: grant File on *.* to 'paul'@'localhost'; — bear in mind that this may introduce a significant security risk in a production system; be safe and revoke the privilege when you're done.
5. Create a /dumps directory and give it 777 (world write) permissions.
6. For each table (users, profiles, friendships, etc.) in MySQL, run: select * into outfile '/dumps/users.sql' from users;.
7. In Postgres, load the dumps. You'll have to do this as a postgres superuser, or you'll have to copy from STDIN. copy users from '/dumps/profiles.sql';.

From here, I updated my config/database.yml and restarted my mongrels, and everything worked perfectly! I'm going to start adding database constraints now...