Re: compare two databases

You might consider comparing the schemas with pg_dump -s and diff (as
was already suggested), then comparing the data with a (e.g.) perl DBI
script connected to two databases simultaneously.

If the two databases are truly descended from the same design (e.g.,
restored from the same dump), then I'd expect the dump order to be the
same and the diff probably won't be corrupted with false differences.
I'd strip lines matching /^--/ to facilitate the diff.

A simple-minded implementation (I excel here) of the perl-DBI part
doesn't require table-specific code. I'd do it like this: 1) connect to
both databases; 2) for each (common) table, create a statement which
selects rows in some canonical order (e.g., sorted by primary key or
some non-degenerate multi-column tuple), and then fetch into a hash
one-by-one (cursors would be better, but IIRC the DBI code doesn't
support them). Because you're fetching into a hash, you can write a
single routine to compare all elements of the common hash keys (you'll
already know of differences between keys/columns themselves from the
schema diff). This is not the speed-optimal solution, but it shouldn't
be too bad in total time and you'd probably spend even more time
implementing the optimal solution.

Obviously, this will miss certain diabolical changes like column or
table renames, but it should suffice for most mutations of two instances
from a common schema and content.

-Reece

--
Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0