Re: Enhancement request for pg_dump

On 04/17/2016 01:10 PM, Sergei Agalakov wrote:
> I don't see how these questions are related to the proposed pg_dump
> improvement.
> I suggest to improve pg_dump so it can be used instead of the third
> party tools like DBSteward and SQLWorkbench/J etc.
> to compare two different databases or existing dumps, and to identify
> the differences. The use cases will be exactly
> the same as for the third party tools. The positive difference will be
> that pg_dump is a very reliable, always available and supports all the
> latest PostgreSQL features.
> Do you imply that there shouldn't be any reasons to compare different
> databases to find the differences between them?

To follow up my previous post and to illustrate some of the
difficulties, from your original post:

"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;
"

 From the perspective of the database both of the above lead to the same
end result, so order is not important. Of course a diff is going to see
it differently. The solution is then to impose an order, but how would
that be determined? For instance what about:

GRANT SELECT ON TABLE contracttype TO mro;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers;

vs

GRANT SELECT ON TABLE contracttype TO mro;
GRANT INSERT, SELECT, DELETE,UPDATE ON TABLE contracttype TO musers;

Again diff will see them as not the same, but functionally they are the
same. So who decides order and how far do you reach down into the
statements?


As Bill wrote the issue is after the fact version control versus before
the fact version control. Trying to match things up after various people
have been turned loose at will on different instances of databases is
much more difficult then having them go through a structured version
control system first.


>
> Sergei
>
>> > On Apr 17, 2016, at 12:41 PM, Sergei Agalakov
>> <Sergei(dot)Agalakov(at)getmyle(dot)com> wrote:
>> >
>> > I know about DBSteward. I don't like to bring PHP infrastructure
>> only to be able to compare two dumps,
>> > and to deal with potential bugs in the third party tools. The
>> pg_dump in other hand is always here, and is always trusted.
>> > SQLWorkbench/J also can compare two schemas, and requires only Java.
>> Again, I trust pg_dump more.
>> >http://www.sql-workbench.net/
>> >
>> > May be pg_dump was never INTENDED to generate the dump files with
>> the determined order of the statements,
>> > but it CAN do it with the minor changes, and be more useful to
>> administrators. Why rely on the third party tools
>> > for the tasks that can be done with the native, trusted tools?
>> >
>> > Sergei
>> Does it matter if they differ if you cannot recreate the correct one
>> exactly from source-controllled DDL?  Or know how they are supposed to
>> differ if this is a migration point?
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com