Обсуждение: Enhancement request for pg_dump
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
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
On Sat, 16 Apr 2016 13:33:21 -0600 Sergei Agalakov <Sergei.Agalakov@getmyle.com> wrote: > 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. I don't think pg_dump was ever intended to serve that purpose. dbsteward, on the other hand, does what you want: https://github.com/nkiraly/DBSteward/wiki -- Bill Moran