Re: Enhancement request for pg_dump

On Sat, Apr 16, 2016 at 01:33:21PM -0600, Sergei Agalakov wrote:

> Currently as in PG 9.4, 9.5 the order of the statements in the script
> produced by pg_dump is uncertain even for the same versions of the databases
> and pg_dump.
> One database may script grants like
>
> REVOKE ALL ON TABLE contracttype FROM PUBLIC;
> REVOKE ALL ON TABLE contracttype FROM madmin;
> GRANT ALL ON TABLE contracttype TO madmin;
> GRANT SELECT ON TABLE contracttype TO mro;
> GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers;
>
> and the other may change the order of grants like
>
> REVOKE ALL ON TABLE contracttype FROM PUBLIC;
> REVOKE ALL ON TABLE contracttype FROM madmin;
> GRANT ALL ON TABLE contracttype TO madmin;
> GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers;
> GRANT SELECT ON TABLE contracttype TO mro;
>
> It complicates the usage of pg_dump to compare the structures of the two
> similar databases like DEV and PROD, two development branches etc.
> If the order of the statements generated by pg_dump would be guaranteed then
> it will be very easy to compare the structures and
> security rights of the two databases using only pg_dump and a diff/merge
> tool. Currently we encounter a lot of false differences.
> A sorted order of the DDL and DCL statements in a dump can be implemented as
> a flag to pg_dump or even better as a default behavior.

Since the actual order of statements inside the text mode
dump file does not matter (no restore is being attempted) --
rather only that the order is predictable -- would it not
suffice to run the two dumps through a generic text sort
program ?

pg_dump -D DEV ... | sort > broken-but-sorted-dump-1.txt
pg_dump -D PROD ... | sort > broken-but-sorted-dump-2.txt
diff ... broken-but-sorted-dump-1.txt broken-but-sorted-dump-2.txt

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346