Rails: Best way to make changes to a production database

Question

I need to make changes to an in-use production database. Just adding a few columns. I've made the changes to the dev database with migrations. What is the best way to update the production database while preserving the existing data and not disrupting operation too much?

It's MYSQL and I will be needing to add data to the columns as well for already existing records. One column can have a default value (it's boolean) but the other is a timestamp and should have an arbitrary backdated value. The row counts are not huge.

So if I use migrations how do I add data and how do I get it to just do the two (or three - I add data -latest migrations on the production db when it wasn't initially built via migrations (I believe they used the schema instead)?

Otter

Answer 1

It sounds like you're in a state where the production db schema doesn't exactly match what you're using in dev (although it's not totally clear). I would draw a line in the sand, and get that prod db in a better state. Essentially what you want to do is make sure that the prod db has a "schema_info" table that lists any migrations that you >don't< ever want to run in production. Then you can add migrations to your hearts content and they'll work against the production db.

Once you've done that you can write migrations that add schema changes or add data, but one thing you need to be really careful about is that if you add data using a migration, you must define the model within the migration itself, like this:

class AddSomeColumnsToUserTable < ActiveRecord::Migration
  class User < ActiveRecord::Base; end
  def self.up
    add_column :users, :super_cool, :boolean, :default => :false
    u = User.find_by_login('cameron')
    u.super_cool = true
    u.save
  end

  def self.down
    remove_column :users, :super_cool
  end
end

The reason for this is that in the future, you might remove the model altogether, during some refactoring or other. If you don't define the user class on line "User.find_by_login..." the migration will throw an exception which is a big pain.

Greeley

Answer 2

I always follow this procedure:

• Dump prod database with mysqldump command
• Populate dev / test database with dump using mysql command
• Run migrations in dev / test
• Check migration worked
• Dump prod database with mysqldump command (as it may have changed) keeping backup on server
• Run migrations on prod (using capristano)
• Test migration has worked on prod
• Drink beer (while watching error logs)

Aardvark

Are you doing all non capistrano and non beer steps manually? Seems like a lot of place for good ol' automation/scripting? – Weathercock

The first three steps are scripted. The checks are a combination of automated tests and manual testing. The manual tests focus especially on areas I know have changed. I only push to prod once every few weeks. – Aardvark

Answer 3

Is there a reason you are not using the same migrations you used in your dev environment?

Loreeloreen

The database is already in use so I don't want to lose the existing data and would prefer not to have to take the time to rebuild and repopulate if I can avoid it. – Otter

So, the migration is destructive? Or are you currently using migrations, but never used them on production before, and so it would attempt to do all of the migrations, some of which are older and no longer relevant and therefore might cause problems? – Loreeloreen

Can you describe, in your question, what about your migrations makes them destructive? This might help in getting a more precise answer to your problem. – Loreeloreen

It's that the migrations were never run on production. So I'd need a way to just run the specific non-destructive migrations. – Otter

Answer 4

Adding a column with add_column in a migration should be non-destructive: it will generate a "ALTER TABLE" statement. If you know what you're going to put into the columns once created, you can fill in the values within the migration (you may choose a less time-consuming alternative if the row counts are large).

Removing or altering the definition of a column is, I think, platform-dependent: some will allow deletion of a column in place, others will perform a rename-create-select-drop sequence of commands.

To get more specific, we need more information: what kind of migration are you looking at, what platform are you running on, do you need to set values as part of the migration? Stuff like that would help a lot - just edit the question, which will push it back up the list.

Iwo