Best practice for updating production database?

Question

I am new to programming. I am using Rails 4 and Postgres as database in production. When I change my database structure, what is the best practice to update production database when deploying using Capistrano? I want to keep all the data in production intact.

I noticed sometimes when I changed schema and deploy to production, some of the existing data records are lost.

Answer 1

Normally when making alterations to your schema you'll use rails g migration to produce new migrations and then do things like this:

class AddUsersDiscountToken < ActiveRecord::Migration
  def change
    add_column :users, :discount_token, :string
  end
end

This migration will add a discount_token column to the users table and can be applied with:

rake db:migrate

Within Capistrano there's a task that will do this for you once the deploy is successful. No data should be lost, no records altered apart from introducing this new field. If anything else happens you've got something very odd going on in your migrations.

Remember, a few rules:

• Always back-up your data before applying any migrations using the proper tool. mysqldump is a good place to start. Copying the binary MySQL data files is not adequate and will not work reliably, if at all.
• Always test your back-ups and ensure everything is there. The backup process may have terminated early for some reason and failed to properly back up all the tables and data.
• Never deploy migrations on your live production database without testing on a copy first. This is where the backup comes in handy, you get a chance to restore it, run the migrations, and test the results.

This is why having a staging server is often handy, even if it's just a temporary one, or not as powerful as your production server. It allows you to test your migrations on actual, production data without running the risk of interrupting service. Run your new production code with your newly migrated production database and verify that the new features you've added are functioning correctly and also check you haven't broken any old code with regressions.

Remember, migrations that alter the schema of large tables, such as those with millions of rows, may take some time to complete, especially on servers with non-SSD backed databases. When testing on your staging system make a note of how long it takes to complete as you may need to give your users advance notice for scheduled maintenance or make alterations to your plans to be less disruptive in terms of migrations.