Hi,
In order to compare the schema of two presumably identical databases,
I've been diffing the output of pg_dump -Osx. However, I've found that
the order of the output is not very reliable. For example, after
recreating the Pagila sample database, I find the following:
--- pagila.dmp 2011-08-26 14:34:48.000000000 -0400
+++ pagila.dev-dmp 2011-08-26 14:34:47.000000000 -0400
@@ -1140,7 +1140,7 @@ --
CREATE TRIGGER last_updated
- BEFORE UPDATE ON city
+ BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated();
@@ -1160,7 +1160,7 @@ --
CREATE TRIGGER last_updated
- BEFORE UPDATE ON customer
+ BEFORE UPDATE ON category FOR EACH ROW EXECUTE PROCEDURE last_updated();
...
The same triggers exist on both databases, it's just that the order is
different (apparently they're output in creation order). This even more
crucial with PostGIS databases, which have several hundred function and
operator pairs where the only difference is one takes arguments of type
geometry and the other uses type geography. There the pg_dump diff
approach is nearly useless.
I thought that comparing database schemas would be quite desirable,
e.g., between development/test and production databases. Is there
perhaps some mechanism or tool that people use for this purpose, or is
this not a requirement?
Incidentally, these comparisons are for the Pyrseas tools I'm
developing. The output of dbtoyaml is predictable (not because of
anything I wrote, but because pyyaml outputs everything in alphabetical
order), and I can compare the YAML outputs quite nicely (however, it
doesn't show me things I haven't implemented yet, e.g., OPERATOR CLASSes
in the case of PostGIS).
Joe