Actually, I haven't done such thing, yet. However the intended method or a possible way is this:
Manage schema-snapshots and put them in VCS
Keep a file of changes with version stamps (also in VCS). Document each change to the db (so SQL commands you issue to change its struct). So one can upgrade from any schema version to any other higher one.
If you happen to radically change the structure (i.e. Make 2 tables of one): Keep the old table. Create the new ones, fill them with current data, truncate the old table and remove all indexes etc. Make it become a view by adding a _RETURN rule. Add insert+delete+update rules. So any old application is able to operate on the new schema like it did on the old one.
Even stored procedures keep working.
If your apps just use stored procs (which is recommended) you can also change them to use the new tables or what-o-ever.
It depends, but this is the principe
-----Original Message-----
From: Leander Gillard [mailto:leander@vianet.ca]
Sent: Mittwoch, 29. Juni 2005 17:05
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Postrgre Integrated App Development
We are currently moving a new large web application live and but are concerned that the structure of the tables may change during this process. Since we will have a temendous amount of data flowing in and out and there will be errors it would be nice to understand how others handle this issue. As far as incrementing the move live, backing up data and then if/when something happens how to correct this or combine the data without breaking the system or causing data corruption.
-- Leander