How do people do schema upgrades on running databases?
1. By dumping the table values, dropping the databases, installing
new schemas, and re-importing the databases?
2. By applying "patch files" containing SQL commands to drop tables,
add tables, drop columns and add columns?
Right now, I use alternative 1. but I'm not really happy with it.
It's error prone, and therefore hard to automate. We have to shutdown
the system using PostgreSQL while doing the upgrade, because otherwise
it would block the dropdb commands.
Alternative 2. feels like it could be used to do upgrades in a much
less intrusive way. Ie. that the system could potentially be left
running during the schema upgrade.
The problem is how to create the "patch files". Is there a way to
calculate the differences between the schemas of two databases, and
create a set of SQL commands to transform one into the other?
Thanx!
- Steinar