gistfile1.rb

namespace :db do

  desc "Generates ownership queries"
  task :chown => :environment do
    ActiveRecord::Base.connection.tables.sort.each do |t|
      puts "ALTER TABLE #{t} OWNER TO radius;"
    end
  end

  desc "Migrate database from MySQL to PostgreSQL"
  task :translate => :environment do    

    # how many are we going to do at a time (ie, how many per file?)
    BATCH_SIZE = 10_000

    # what tables are we dumping?
    $tables_to_ignore = ['schema_migrations', 'sessions', 'customers_users']
    $tables_to_dump = ActiveRecord::Base.connection.tables.sort - $tables_to_ignore

    # mwuahahah.
    module PostgresModels
      $tables_to_dump.each do |table|
        class_eval "class #{table.classify} < ActiveRecord::Base ; establish_connection({ :adapter => 'postgresql', :database => 'fullcircle_development', :username => 'rails', :password => 'rails', :host => 'localhost' }) ; end"
      end
    end

    # right, let's go through them all...
    $tables_to_dump.each do |table|

      # set up our source class
      klass = table.classify.constantize
      postgress_klass = eval("PostgresModels::#{klass}")
      
      # empty our target table
      postgress_klass.connection.execute "TRUNCATE #{table};"

      # start tracking (nice ui!)
      batch_index                 = 1
      count_for_klass             = klass.count
      estimated_batches_for_klass = (count_for_klass / BATCH_SIZE) + 1

      klass.find_in_batches(:batch_size => BATCH_SIZE) do |batch|
        puts "Writing #{table} batch #{batch_index} of #{estimated_batches_for_klass}"
      
        # the magic!
        batch.each do |r|
          attrs = r.attributes_before_type_cast
          
          n = postgress_klass.new(attrs)
          n.id = r.id
          n.save
        end
            
        batch_index = batch_index + 1
      end

      puts "Count for #{klass} (old -- new): #{klass.count} -- #{postgress_klass.count}"
      puts "MaxId for #{klass} (old -- new): #{klass.last.id} -- #{postgress_klass.last.id}" unless klass.count.zero?
      puts "Updating sequence for #{klass}"
      
      postgress_klass.connection.execute("SELECT setval('#{table}_id_seq', (SELECT MAX(id) FROM #{table}) + 1);")
      
    end
    
    puts "All imported. Don't forget to check the record count on the tables above, re-build the sequences and manually port across the customers_users table."
  end
end