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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
