David B. Harrison

This morning, I took a snapshot of a production, Ruby on Rails database (via rake db:dump), and then applied the production / schema data to other environments.

System information:
• Database is Oracle 11
• Application server runs Redhat Enterprise Linux

Assumptions and challenges:
• The Development and QA (Quality Assurance) instances are probably not at the same software revisions or migration version numbers. So… keep that in mind.
• A firewall prevents the Dev and QA applications servers from accessing the Production database, and application server directly. So, a different method must be employed.
• As with most enterprise type environments, developers do not have access to the production systems.

This methodology uses the gem yaml_db

Steps

1. Due to security and access limitations, a DBA copies Applications production schema and data (in Oracle), to a developer’s schema (also in Oracle)
2. On the Production RoR Server (or in this case… A developer’s schema with the recent copy, made by the DBA)
   Take a snapshot of the database.
   • rake db:dump
   • The files produced are data.yml and schema.rb
3. Copy the data and schema files to revision control and then to the target servers/environments . (Or if you’re feeling adventurous, just copy it right to the target server. 😛 )
4. On the Target server
   Delete sessions, Load the fresh data, and then re-apply any migrations that haven’t made it to production, yet.
   1. rake db:sessions:clear
   2. rake db:load
   3. rake db:migrate
   4. Restart the RoR app
   5. Optional: Recreate the non-production user accounts. I have two rake tasks for that:
      • rake db:create_devl_users
      • rake db:create_pprd_users

Potential Gotchas

• Make sure to load the fresh snapshot, before applying migrations. Otherwise, the results are unreliable.
• You may need to restart the Application service (Apache / Fusion Passenger) and delete any prior sessions

Notes on Migrations and Schema.rb

At the top of the schema.rb file, it lists the most recent migration run on that snapshot of the database
for example:

  ActiveRecord::Schema.define(:version => 20110602132504)

When you run rake db:migrate, it apparently compares the version number from schema.rb with the migration table, to determine the latest migration that has been applied to that instance of the database.

From the rails console, you can get the latest migration applied, with the following command

  echo "ActiveRecord::Migrator.current_version" | rails c

Want to make sure that you don’t have any later migrations that will get in the way of a rake db:load?

  echo "ActiveRecord::Base.connection.execute(\"DELETE FROM schema_migrations WHERE version>'20110602132504'\")" | rails c