Migrating legacy data

I need to migrate data from my company’s legacy MySQL database to my new PostgreSQL (pg) database.

Of course, many table and column names, data types, non-null contstraints, indexes, etc., all need to change.

But the real challenge is that the MySQL database is not well normalized. A common example: in the old MySQL database, each company could have five contacts, held in five columns: contact_name_1 through contact_name_5. In the new pg db, these are going into a separate contacts table, with another join table to join them to the companies table.

I think it will be easier to transform the data to fit the new schema using Ruby instead of a raw SQL script. So I made a new rails app called db_prep specially for this purpose. The centerpiece of the db_prep rails app will be a custom rake task called rake db:prepare.

Here is my general plan for how to proceed:

  • dump the MySQL db into the db_prep_development db
  • run an SQL script to make the MySQL primary key columns and tablenames more “Rails-y”
  • dump the data out of the MySQL db
  • rake db:schema:dump
  • Remove all the MySQL not-null constraints, indexes, etc., from schema.rb
  • Append all the tables from my main app’s schema.rb onto db_prep’s schema.rb
  • rake db:schema:load
  • load the data back into db_prep_development (the rake db:schema load erased it)
  • create model files for all the db tables
  • rake db:prepare (using the custom “prepare” task I wrote)
  • dump out just the data, then load it into the postgres database

Here is a simplified excerpt of the kind of code that is in lib/tasks/prepare.rake:

namespace :db do 
  desc "Prepare data to go into new PostgreSQL database"
  task prepare: :environment do
    OldClient.all.each do |r|
        english_name: r.client_name,
        chinese_name: (r.client_name_chinese unless r.client_name_chinese == "")

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s