Re: Schema comparison tool

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