perlmeditation EvdB I am currently working on a project which is heavily database dependant. As it is a work in progress there are plenty of changes being made all the time - both to the code and to the structure of the database (ie its tables and their columns, and triggers, and indicies etc). <p>This would usually not be a problem as to make a change to the database I would alter the sql that creates it, drop the database, recreate it, load up the test data and continue work. This is good and dandy, however... <p>The project is about to go live (web-based shop) and my problem is that on the live system data will need to be kept over the changes. <p>My problem is how to go about making these changes to the database structure whilst making it possible to apply them to a production system. I have come up with the following possible methods but would like some feed back on them before commiting to one. <ol> <li><b>Transfer script</b> The old database is kept and a new one is created. Data is then transferred from the old one to the new one using a perl script which also takes into account any structural changes. <li><b>Make changes using SQL commands</b> All changes to the database are made using 'ALTER table ADD column' commands so that all the data remains in place. Essentially a series of patches are applied. <li><b>Store data and reload it</b> All the data is stored to file (possibly using XML or YAML), the database is recreated and the data is then reloaded into the new database. </ol> <p>There are benefits and problems to each one. All of them would require keeping track of which version of the structure is currently being used and then acting accordingly. <p>As I see it this is a general database based project problem but I have not seen much discussion on it here or elsewhere (please correct if wrong). For the record I am using Postgres 7.4. <p>I am personally tending towards using sql commands which are stored in files named something like 'alter-2.3-2.4.sql' which would change the database from version 2.3 to 2.4. <p>Another consideration: How can all this be tested? <div class="pmsig"> <div class="pmsig-139856"> <p>--<code>tidiness is the memory loss of environmental mnemonics</code> </div></div>