It can be done of course, but as you can see in my examples the statements in pg_dump generated scripts are grouped together by the objects.
It is easier to analyze the differences when all these differences for an object are clustered together, and aren't dispersed in the diff file.
It also will break the multi-line statements.
It is also possible to write a more complex Perl script, or sed/awk, but it would be so much easier to do it directly in pg_dump.
Sergei
> 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.txtpg_dump -D PROD ... | sort > broken-but-sorted-dump-2.txtdiff ... 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
