Here’s how I migrated the database of a rails project including all contents from MySQL to Postgres:
-
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.
-
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 databaseself.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.