Re: Comparing two PostgreSQL databases -- order of pg_dump output

Hola Jaime,

On 08/30/2011 03:24 PM, Jaime Casanova wrote:
> what about using pg_dump -Fc -Osx and use pg_restore -l to list
> objects. then you can sort and compare objects and then a script that
> compare schema of objects extracting them with -P, -T or -t

That appears to be of limited use (i.e., it would only work for
functions, triggers and tables). pg_restore -L/--use_list is more
comprehensive. So the script would have to do something like the following:

$ pg_dump -Fc -Osx postgis > postgis.dump
$ pg_restore -l postgis.dump | sort -k4 > postgis.list
$ pg_restore -L postgis.list postgis.dump > postgis.sorted

Rinse and repeat on the second database and then diff the .sorted files.
Tried it and although it doesn't completely do the trick it's much
better than diffing the plain text pg_dump outputs (3000+ diff lines vs.
less than 200 and about half of that are actual differences).

Thanks,

Joe