On Tue, 2005-02-15 at 15:01 +0000, Jeff Amiel wrote: > I'm looking specifically for a tool to help compare 2 database schemas > (nominally, production and development) and generate the appropriate SQL > (that can be later executed) to bring the to schema's into sync. [snip] > What does the rest of the postgreSQL admin community do in order to > "bring-live" database schema changes from their development environment > to production? The project I'm working on at the moment has a very dynamic schema. Most of our software releases have some requirement to tweak columns or add new tables, etc. We nominally use Power Designer for managing our schema but in reality, the main thing we use it for is drawing the pretty schema diagrams (and it's not even very good at that). Obviously, before any schema change can be rolled out to production, the precise method which will be used to apply the change in production must be tested in development and staging. Therefore, each software release includes some number of schema patch files which must be applied in order. We have a Perl script for applying patches which basically just feeds each .sql file to psql (we find the psql \set macro functionality useful in the patches) in order. If a patch is applied successfully, we update a row in a config table in the database to indicate the last patch applied. When we re-run apply_patches.pl, it looks at the config item to see what patch-level the database is up to and then apply each of the newer patches. Another part of the puzzle is that our regression test suite includes a schema 'test'. This basically just uses the first technique you outlined - the test creates one database from the Power Designer generated model and uses pg_dump -s on it; then creates another database from the original unpatched schema, applies all the patches and dumps it too. If a diff on the dumps reveals no differences then the test passes, otherwise the test fails - which usually means we need to update the Power Designer model. (The test script has to do a bit of reordering and whitespace normalising to avoid false positives). Although GUI design tools can be seductive, our developers seem to be more comfortable with using vi on a .sql file. Usually what they want to do is "exactly like this other thing but with this minor difference" which will always be easier in vi than in a GUI tool. So in summary, we build our schema patches manually. And we use our manual processes to drive the use of our GUI designer tool. If you want to try our apply patches script, I've dropped a copy here: http://wellington.pm.org/archive/postgresql/apply_patches.tar.gz Cheers Grant ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
