Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Canvas MySQL -> Postgres Migration
#!/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
}
#!/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
@christopher-b

This comment has been minimized.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.