ducktyped

I was running a mysql 4.1 db that kept screwing up my unicode, so I decided why not double version numbers and move to postgresql 8.2. Great idea in theory, but a pain in the ass in practice. The sql dumps of the different DBMS are incompatible, especially booleans causing trouble. The scripts I found were pretty much useless, so I was stuck until I came across the idea of using rails to export the db to yaml and then re-import it into the new db.

This sounds very elegant in theory, but there are still a few pitfalls. Tobias Luetke posted how he did it and provided a very useful rake task that became the basis for my approach. For good measure I threw Dr. Nic’s magic model generator in the mix to allow for very powerful and flexible migrations, even if your application doesn’t define models for all tables, e.g. when you have several apps using the same db.

The following is my 10-step program for database migration bliss.

1. Set up the basics

   
     sudo gem install magic_model_generator
     rails db_migration_app
     cd db_migration_app
     

2. Download the backup.rake task and copy it to lib/tasks/
3. Connect to your production db in config/database.yml. This is also a good time back up your db before you ruin things with jittery fingers and then blame me.
4. With the db set up properly, get the schema and magic models ready

   
     RAILS_ENV=production rake db:schema:dump
     RAILS_ENV=production ./script/generate magic_model
     

   (Just for kicks, you can inspect your entire db through the rails console via RAILS_ENV=production ./script/console)
5. [OPTIONAL] Add any tables that should not be dumped/restored to the array in the rake task’s interesting_tables method. schema_info, sessions, and logged_exceptions are excluded by default.
6. Let’s do some dumping. This may take some time, depending on your db size.

   
     RAILS_ENV=production rake db:backup:write
     

   At this point you have all your tables’ data in yaml format in db/backup/*.yaml.
7. Now change your database.yml to the new, empty db (e.g. your brand new pg db). Don’t mess this up, because the next step will completely wipe whichever db is defined as the production db.
8. Import the yaml data into the new production db.

   
     RAILS_ENV=production rake db:backup:read
     

   This also runs rake db:schema:load, and restores the correct schema_info.
9. [EXTRA CREDIT] If you migrate to postgresql, fix your sequences in one easy step with the following rake task I created:

   
     RAILS_ENV=production rake db:backup:set_sequences
     

   If you want to check the sequence number in psql, run select last_value from table_name_id_seq; (where table_name is your table’s name)
10. Lastly, make sure you don’t forget to adjust your actual application’s database.yml to point to the new db.

Enjoy your newfound freedom from the stranglehold of your old database. :)