Skip to content

Instantly share code, notes, and snippets.

@christopher-b
Last active August 26, 2018 21:14
Show Gist options
  • Save christopher-b/aaef119956e2e24ab4f4 to your computer and use it in GitHub Desktop.
Save christopher-b/aaef119956e2e24ab4f4 to your computer and use it in GitHub Desktop.
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
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment