Hi,
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.
Thank you,
Sergei Agalakov