Re: Enhancement request for pg_dump - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Enhancement request for pg_dump |
Date | |
Msg-id | 5713FFB2.5040103@aklaver.com Whole thread Raw |
In response to | Re: Enhancement request for pg_dump (Adrian Klaver <adrian.klaver@aklaver.com>) |
List | pgsql-general |
On 04/17/2016 01:58 PM, Adrian Klaver wrote: > 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? Bad example. Some testing shows Postgres will reorder the GRANTS as: SELECT,INSERT,DELETE,UPDATE from whatever order they where entered as. > > > 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
pgsql-general by date: