Canvas MySQL -> Postgres Migration

check.rb

#!/usr/bin/env ruby

# Do sanity check on imported data by comparing the number of rows in each table

require 'pg'
require 'mysql2'

tables = %w( { list the tables to compare here  })

p = PG.connect(dbname:'canvas', host: 'postgres.ocad.ca', user: 'canvas_user')
m = Mysql2::Client.new(host: 'mysql.ocad.ca', username:'canvas_user', password:'', database: 'canvas')

tables.each { |table|
  p_rows, m_rows = 0
  output = "#{table}: \t"
  sql = "SELECT COUNT(*) FROM #{table}";
  p.exec(sql) do |result|
    result.each do |row|
      p_rows = row['count'].to_i
    end
  end

  m.query(sql).each do |row|
    m_rows = row['COUNT(*)'].to_i
  end

  output.concat(p_rows.to_s)
  output << "\t"
  output.concat(m_rows.to_s)
  output << "\t"
  output << (p_rows-m_rows).to_s

  puts output
}

migrate.sh

#!/usr/bin/env bash

# Migrate data from MySQL to Postgres

# Timing
T="$(date +%s)"

export PGPASSFILE="/home/cbennell/migrate/pgpass"

# Check for root user
if [[ $EUID -ne 0 ]]; then
   echo "This script must be run as root" 1>&2
   exit 1
fi

# Drop tables
echo "DROPPING TABLES"
psql -f drop_tables.sql       -h postgres.ocad.ca -U canvas_user canvas
psql -f drop_tables_queue.sql -h postgres.ocad.ca -U canvas_user canvas_queue

# Create schema
cd /var/www/canvas/current && RAILS_ENV=production bundle exec rake db:migrate

# Drop constraints
cd /home/cbennell/migrate
psql -f drop_contstraints.sql -h postgres.ocad.ca -U canvas_user canvas

# Load data
source /home/cbennell/envs/py-mysql2pgsql/bin/activate
py-mysql2pgsql -v --file config-PROD.yml

# Create constraints
psql -f create_constraints.sql -h postgres.ocad.ca -U canvas_user canvas

# Timing
T="$(($(date +%s)-T))"
printf "Time: %02d:%02d:%02d:%02d\n" "$((T/86400))" "$((T/3600%24))" "$((T/60%60))" "$((T%60))"

# Validate
checker/check.rb

Notes here: https://groups.google.com/forum/#!topic/canvas-lms-users/wTIe2un_WqM