How to Deploy Database Changes to a Live Server?

I have a script that is part of my deployment process to pus开发者_如何学Ch DB changes to the production server. If the script corrupts my data for some reason (a bad update), it is tough to recover.

One way to solve this is to shut down the application to users while updating, so if a problem occurs, just go back to the backup I made before deploying.

But I have heard of others who deploy and keep their site live... how would you go about doing this, and if you failed, how could you recover the data that came in since you took your backup before deploying?

This is a tricky problem in general, like with many things in database administration. There are basically three ways to approach this:

1. Avoid failure at all costs.
2. Lock everything down (and make the upgrade really fast).
3. It's OK to lose data.

If you have a complex system, isolate your components according to these or similar categories.

Have a staging system to test upgrades. The staging system is more or less a copy of the production system; it's separate from the test system. Another thing is to have an audit or logging system that you can refer to if you need to replay data.

The real problem is if you notice much later that your upgrade was faulty. Then you're quite screwed.

How big is your database? Can you afford to lose data that was updated while the customer were using it and before you had to go to the backup? Every plan for deployment involves some compromises somewhere, and you've got to decide which compromises are the least painful for what you want to do.

For simple websites running just pgsql, you can disconnect clients, and run the entire update in one big transaction. If any part fails, the whole thing rolls back and it's like you never did anything. Sadly this doesn't work exactly the same for other dbs, but with flashback or whatever oracle calls it you can get something similar.

For bigger, complex websites running on top of a replicated db server set, things get much more complex much more quickly. Where I've worked we've used Slony, and it doesn't take nicely to playing with others when you're deploying DDL changes, and you pretty much HAVE to take all the customers offline while deploying DDL. However, the downtime is measured in minutes for us, even with databases approaching 1TB in size.