> Could somebody explain me, or point me to a resource where I can find > out what is the recommended practice when a live db needs to be replaced > with a new version of it that has a slightly different structure? Our development infrastructure includes a development data base cluster, with one data base per developer, and a staging data base with a largish deployment of the current production data base version. The developers are free to test whatever data base modifications they need on their private development data base. We have a "setup_db" script, which creates the data base structure + initial data. The script is based on an XML file which is processed by XSLT to generate the actual schema for Oracle/Postgres. So the developers usually recreate the DB from scratch on any modification of the db schema. We test both on postgres/oracle, the application must support both (it can even connect to both in parallel). When a release is approaching, we make a diff of the currently deployed production schema and the to be deployed new release schema. The differences go to a db migration script (separately for Oracle/Postgres), broken in a few steps: - init: create new DB structures (create new tables, add new db fields to existing tables, insert new data, etc.). After this step the DB can be used both by the old version and the new one of the application. After this step we restart the application with the new version deployed (actually we have usually another cluster of app-servers connecting to the same data base, running the new version); - cleanup: delete old structures, add the new constraints which could not be added because compatibility problems with the old version; All can be done using alter table and the like. When some field changes it's structure/meaning, we actually create a new field, and copy over the old one with the needed conversions. The new version will use the new field, old version the old field... we do make all possible to be able to run both versions after the init step. We actually have a middle step, because we have our systems (logically) partitioned in sub-systems, and we usually migrate them separately, so we have a "migrate sub-system" step too. So we actually can have 2 versions of the software connecting in parallel to the DB, each having a different set of sub-systems to run. After the migration scripts are created, we test them on the staging system (our QA does this, and they are more than happy to discover any mistakes in the process ;-) We also have a quite extensive unit/integration test suite which catches quite a lot of potential regression cases, this makes any change easier to prove not breaking existing functionality, and in particular data base changes are also easier to add with enough confidence it will work. HTH, Csaba.
