Jens Krämer

Migrating from MySQL to Postgres with Rails Migrations

 |  ruby, rails

Here’s how I migrated the database of a rails project including all contents from MySQL to Postgres:

  1. Migrate the schema as described here. I had to remove the limit options from integer fields in the schema.rb dumped from the existing MySQL database, to make it compatible with Postgres.

  2. To transfer the data from the old database to the new one, I used this migration:

class CopyData < ActiveRecord::Migration def self.up with_classes_to_migrate do |klass| ActiveRecord::Base.establish_connection(old_db) begin rows = klass.find(:all) puts "#{rows.size} rows for class #{klass}" ActiveRecord::Base.establish_connection(new_db) rows.each do |row| begin row.create rescue puts "error migrating record #{row} of class #{klass}: #{$!}" end end rescue puts "error migrating class #{klass}: #{$!}" end # when migrating to postgresql, be sure to update your primary key sequences: ActiveRecord::Base.connection.reset_pk_sequence!(klass.table_name) end # make sure migrate updates the right schema_info table ActiveRecord::Base.establish_connection(new_db) end # clean up the database we migrated to by removing all records from all tables def self.down with_classes_to_migrate do |klass| begin klass.delete_all rescue puts "error migrating back class #{klass}: #{$!}" end end end protected # name of connection for db we migrate to def self.new_db RAILS_ENV end # name of connection for db we migrate from def self.old_db "#{RAILS_ENV}mysql" end # determines which tables we'll migrate def self.with_classes_to_migrate ObjectSpace.each_object(Class) do |klass| next unless klass.superclass == ActiveRecord::Base next if CGI::Session::ActiveRecordStore::Session == klass yield klass end end end

If you want to use this, you should modify the protected methods to suit your needs:

  • self.new_db returns the name of the configuration block in config/database.yml which describes the new database
  • self.new_db returns the name of the database connection we’re migrating from.
  • self.with_classes_to_migrate traverses the Ruby object space looking for classes inheriting from ActiveRecord::Base and can be used to decide which tables should or should not be migrated.