Обсуждение: 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