Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have some questions on db schema changes in production environment without taking the database server and the webapp offline.

Q1- I want to be able to make changes to database schema (adding, removing columns, normalizing tables etc) in my production environment without taking the db server and my webapp offline. What are some of the best practices to achieve this? Also are there any free tools that deal with this problem. I am using PostgresSQL.

Q2- While the db is being upgraded, will the app servers in production work against the old db or the new db. Or is there usually some sort of a hybrid using perhaps a strategy pattern in the app? What are some of the best practises for this? When would it be safe to point the appserver to the upgraded db. Will there be some sort of downtime involved or can that be avoided altogether somehow?

Thanks.

share|improve this question

migrated from stackoverflow.com Mar 25 at 13:43

This question came from our site for professional and enthusiast programmers.

    
@Alexandros you could have waited a bit more... The question is still less than a day old, no need to hurry. –  dezso Mar 26 at 10:52
1  
We do it all the time. Google up the following article "Developing low maintenance databases", it describes refactoring tables without downtime. –  A-K Mar 27 at 12:44

2 Answers 2

It really depends how big, complicated and active your database is. Bear in mind that while you are making schema changes, many of the tables will be read-only or completely inaccessible until the changes are completed. Also bear in mind that the reverse is true - your schema changes will need to acquire exclusive locks on entire tables and possibly all their relations, which may take a long time or even be impossible to acquire if the application gets in the way. Theoretically this can result in circular locks which will freeze the whole DB. Basically, if there are a lot of reads and writes going on and changes are going to cause significant disruption to your application, then you should schedule downtime for it instead.

Then you have to account for the changes your application will require. Will changing the schema require changes in the application too? Is your application small enough or modular enough that you can make those changes in increments as you change the database? Ultimately, making changes in production is really hard. Unless your app is designed for it from the ground up, it's almost universally not worth the extra effort required for the sake of some small downtime.

The other option to minimise downtime is to build a new database with an improved schema and a new app server connected to it, then build a widget to sync the data between the new database and the old one. When you are ready to deploy you simply direct traffic to the new app server. Again this is a tradeoff between how hard it is to create the system and how hard it is to accept some downtime. There are very few applications that cannot tolerate an hour or two with a "Scheduled Maintenance" message starting 2am on a tuesday night.

share|improve this answer

What I do is to build and test a script with the changes in a development server. It must upgrade the db in a totally automated and reproducible way. That includes running it with the same user with the same permissions with which it will be run in the production server. That said this question is too broad and should be asked in http://dba.stackexchange.com/

share|improve this answer
    
Thanks Clodoaldo. I was mostly interested in avoiding any sort of application downtime at all while doing major upgrades to production db. What would be good practises for that if that is possible at all? –  Abdul Rahman Mar 25 at 12:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.