subject:"Re\: \[GENERAL\] Enhancement request for pg

Re: [GENERAL] Enhancement request for pg_dump

2016-04-22 Thread Adrian Klaver


On 04/22/2016 11:16 AM, Pierre Chevalier Géologue wrote:

Le 22/04/2016 19:11, Adrian Klaver a écrit :

Last time I had to do this kind of exercise, a few years ago, I was in a
remote place without Internet access, so I could not get any information
or ask any help.  I was kind of surprised/frustrated by the (apparent)
lack of order of the pg_dump output.  So I manually wrote scripts to
export the tables and views' definitions separately, one by one, (using
pg_dump, of course) and stack them in the order I wished into a large
file.  That was quite suboptimal, but it worked as expected, and I was
able to diff and patch correctly.


You realize there is pg_restore -l and pg_restore -L :

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html


Yes, thanks for the advice.  Now I remember that I had used it also: I
just found these notes in my numeric attic:

   # pierre@autan: ~< 2013_08_17__17_00_23 >
pg_restore -l database_2013_08_14_20h34.pg_dump -n pierre >
tt_schema_pierre


Yea, you can also use the filtering switches to create a filtered TOC, so:

pg_restore -l -s test.out > test_s.toc

Now the TOC has only a summary line of what is being done, but it is 
easy enough to feed it back to pg_restore and have it restore to a plain 
text file instead of a database:


pg_restore -L test_s.toc  -f test_s.sql




   # pierre@autan: ~< 2013_08_17__17_00_23 >
vi tt_schema_pierre

   # pierre@autan: ~< 2013_08_17__17_00_23 >
cat tt_schema_pierre
DROP VIEW IF EXISTS pierre.dh_collars;
DROP VIEW IF EXISTS pierre.dh_litho;
DROP VIEW IF EXISTS pierre.dh_sampling_grades;
DROP VIEW IF EXISTS pierre.dh_sampling;
DROP VIEW IF EXISTS pierre.topo_points;
DROP VIEW IF EXISTS pierre.baselines;
DROP VIEW IF EXISTS pierre.dh_devia;
...


I just cannot remember why it did not fulfill my needs, so that I rather
opted to pg_dump's.


...

It does not solve all problems but it does do some ordering and is
amenable to be
imported as space separated file for further ordering :

aklaver@panda:~> pg_restore -l test.out
;
; Archive created at Fri Apr 22 10:07:50 2016
; dbname: test
; TOC Entries: 67
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.4.6
; Dumped by pg_dump version: 9.4.6
;
;
; Selected TOC Entries:
;
2702; 1262 983301 DATABASE - test postgres
9; 2615 1298825 SCHEMA - MASTER_USER postgres
8; 2615 2200 SCHEMA - public postgres
2703; 0 0 COMMENT - SCHEMA public postgres
2704; 0 0 ACL - public postgres
2; 3079 12456 EXTENSION - plpgsql
2705; 0 0 COMMENT - EXTENSION plpgsql

...

Thanks, I'll dig a bit more in pg_restore, for my current issues.

Pierre



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-22 Thread Pierre Chevalier Géologue


Le 22/04/2016 19:11, Adrian Klaver a écrit :

Last time I had to do this kind of exercise, a few years ago, I was in a
remote place without Internet access, so I could not get any information
or ask any help.  I was kind of surprised/frustrated by the (apparent)
lack of order of the pg_dump output.  So I manually wrote scripts to
export the tables and views' definitions separately, one by one, (using
pg_dump, of course) and stack them in the order I wished into a large
file.  That was quite suboptimal, but it worked as expected, and I was
able to diff and patch correctly.


You realize there is pg_restore -l and pg_restore -L :

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html


Yes, thanks for the advice.  Now I remember that I had used it also: I 
just found these notes in my numeric attic:


  # pierre@autan: ~< 2013_08_17__17_00_23 >
pg_restore -l database_2013_08_14_20h34.pg_dump -n pierre > tt_schema_pierre

  # pierre@autan: ~< 2013_08_17__17_00_23 >
vi tt_schema_pierre

  # pierre@autan: ~< 2013_08_17__17_00_23 >
cat tt_schema_pierre
DROP VIEW IF EXISTS pierre.dh_collars;
DROP VIEW IF EXISTS pierre.dh_litho;
DROP VIEW IF EXISTS pierre.dh_sampling_grades;
DROP VIEW IF EXISTS pierre.dh_sampling;
DROP VIEW IF EXISTS pierre.topo_points;
DROP VIEW IF EXISTS pierre.baselines;
DROP VIEW IF EXISTS pierre.dh_devia;
...


I just cannot remember why it did not fulfill my needs, so that I rather 
opted to pg_dump's.



...

It does not solve all problems but it does do some ordering and is amenable to 
be
imported as space separated file for further ordering :

aklaver@panda:~> pg_restore -l test.out
;
; Archive created at Fri Apr 22 10:07:50 2016
; dbname: test
; TOC Entries: 67
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.4.6
; Dumped by pg_dump version: 9.4.6
;
;
; Selected TOC Entries:
;
2702; 1262 983301 DATABASE - test postgres
9; 2615 1298825 SCHEMA - MASTER_USER postgres
8; 2615 2200 SCHEMA - public postgres
2703; 0 0 COMMENT - SCHEMA public postgres
2704; 0 0 ACL - public postgres
2; 3079 12456 EXTENSION - plpgsql
2705; 0 0 COMMENT - EXTENSION plpgsql

...

Thanks, I'll dig a bit more in pg_restore, for my current issues.

Pierre
--

Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
  Tél+fax  :09 75 27 45 62
06 37 80 33 64
  Émail  :   pierrechevaliergeolCHEZfree.fr
  icq#   :   10432285
  jabber: pierre.chevalier1...@jabber.fr
  http://pierremariechevalier.free.fr/pierre_chevalier_geologue



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-22 Thread Adrian Klaver

On 04/22/2016 09:44 AM, Pierre Chevalier Géologue wrote:
> Hi,
> Le 18/04/2016 02:26, Sergei Agalakov a écrit :
> 
>> If you never encountered a situation when in the dozens of
>> environments the databases has diverged because somebody has
>> done something manually - good for you, you are lucky guy then.
> 
> I'm definitely not a lucky guy at all! :-)
> And this is happening to me *right now*... My case is a little bit more
> complicated, but I'll come back to this later on, probably in another
> thread, in order to avoid confusion.
> 
> Last time I had to do this kind of exercise, a few years ago, I was in a
> remote place without Internet access, so I could not get any information
> or ask any help.  I was kind of surprised/frustrated by the (apparent)
> lack of order of the pg_dump output.  So I manually wrote scripts to
> export the tables and views' definitions separately, one by one, (using
> pg_dump, of course) and stack them in the order I wished into a large
> file.  That was quite suboptimal, but it worked as expected, and I was
> able to diff and patch correctly.

You realize there is pg_restore -l and pg_restore -L :

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html

-l
--list

List the contents of the archive. The output of this operation can be used 
as input to the -L option. Note that if filtering switches such as -n or -t are 
used with -l, they will restrict the items listed.

-L list-file
--use-list=list-file

Restore only those archive elements that are listed in list-file, and 
restore them in the order they appear in the file. Note that if filtering 
switches such as -n or -t are used with -L, they will further restrict the 
items restored.

list-file is normally created by editing the output of a previous -l 
operation. Lines can be moved or removed, and can also be commented out by 
placing a semicolon (;) at the start of the line. See below for examples.


It does not solve all problems but it does do some ordering and is amenable to 
be
imported as space separated file for further ordering :

aklaver@panda:~> pg_restore -l test.out 
;
; Archive created at Fri Apr 22 10:07:50 2016
; dbname: test
; TOC Entries: 67
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.4.6
; Dumped by pg_dump version: 9.4.6
;
;
; Selected TOC Entries:
;
2702; 1262 983301 DATABASE - test postgres
9; 2615 1298825 SCHEMA - MASTER_USER postgres
8; 2615 2200 SCHEMA - public postgres
2703; 0 0 COMMENT - SCHEMA public postgres
2704; 0 0 ACL - public postgres
2; 3079 12456 EXTENSION - plpgsql 
2705; 0 0 COMMENT - EXTENSION plpgsql 
1; 3079 1730602 EXTENSION - plpythonu 
2706; 0 0 COMMENT - EXTENSION plpythonu 
191; 1255 1057054 FUNCTION public fn_plpgsqltestmulti(character varying) aklaver
210; 1255 1730594 FUNCTION public measurement_insert_trigger() postgres
205; 1255 1065246 FUNCTION public myfunc(refcursor, refcursor) aklaver
206; 1255 1065247 FUNCTION public myfunc(refcursor, refcursor, integer) aklaver
209; 1255 1065248 FUNCTION public myfunc(refcursor, refcursor, character 
varying) aklaver
208; 1255 1730587 FUNCTION public pg_stat_allusers() postgres
211; 1255 1730607 FUNCTION public test() postgres
207; 1255 1730585 FUNCTION public trigger_test() aklaver
198; 1255 1299304 FUNCTION public user_update() postgres
180; 1259 1298826 TABLE MASTER_USER test_tbl postgres
175; 1259 1016073 TABLE public a aklaver
178; 1259 1057055 TABLE public cash_journal aklaver
2707; 0 0 COMMENT public COLUMN cash_journal.click aklaver
2708; 0 0 COMMENT public COLUMN cash_journal.cash_journal_id aklaver
2709; 0 0 COMMENT public COLUMN cash_journal.fairian_id aklaver 
 
2710; 0 0 COMMENT public COLUMN cash_journal.debit aklaver  
 
2711; 0 0 COMMENT public COLUMN cash_journal.credit aklaver 
 
2712; 0 0 COMMENT public COLUMN cash_journal.balance aklaver
 
2713; 0 0 COMMENT public COLUMN cash_journal.description aklaver
 
182; 1259 1299634 TABLE public company postgres 
 
2714; 0 0 ACL public company postgres   
 
183; 1259 1727447 TABLE public final hplc_admin 
 
189; 1259 1730617 TABLE public

Re: [GENERAL] Enhancement request for pg_dump

2016-04-22 Thread Pierre Chevalier Géologue


Le 18/04/2016 03:10, Sergei Agalakov a écrit :

I just wanted to check that my request will have the peoples support.
So far it doesn't.


Well, you can count on my support, for sure!



It looks like that or people never need to compare two PG databases
to find the differences in the schemas or security, or happy to use
the third party tools to do it, and don't want any native support.


I definitely share your opinion.  But I understand that pg_dump wasn't 
originally designed for this purpose, although such a feature would seem 
so natural, at first glance.
So, another idea would be to implement another utility, something very 
similar to pg_dump (probably sharing most of its code with it, or 
calling pg_dump like my scripts did), but giving an ordered output.

Implementing such a tool outside of postgres would bring a bit more chaos.
So, the tool should be preferably bundled with postgres, to avoid the 
need to get a third-party tool.

Hm. When I think about it twice, it sounds like overkilling.

Opinions?



If I see any support from other people for this idea then I shall
go to https://postgresql.uservoice.com/forums/21853-general,


I'd say +1, but the idea should be a bit more matured first, maybe?

À+
Pierre
--

Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1...@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-22 Thread Pierre Chevalier Géologue


Hi,
Le 18/04/2016 02:26, Sergei Agalakov a écrit :


If you never encountered a situation when in the dozens of
environments the databases has diverged because somebody has
done something manually - good for you, you are lucky guy then.


I'm definitely not a lucky guy at all! :-)
And this is happening to me *right now*... My case is a little bit more 
complicated, but I'll come back to this later on, probably in another 
thread, in order to avoid confusion.


Last time I had to do this kind of exercise, a few years ago, I was in a 
remote place without Internet access, so I could not get any information 
or ask any help.  I was kind of surprised/frustrated by the (apparent) 
lack of order of the pg_dump output.  So I manually wrote scripts to 
export the tables and views' definitions separately, one by one, (using 
pg_dump, of course) and stack them in the order I wished into a large 
file.  That was quite suboptimal, but it worked as expected, and I was 
able to diff and patch correctly.


And today, I thought: "time has passed, I'm sure that pg_dump must 
magically have an option to get the output in some kind of order, by 
now"... 'man pg_dump' didn't help.  And as I can read this discussion (I 
haven't finished yet, obviously), this is not the case.


À+
Pierre
--

Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1...@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-18 Thread Adrian Klaver


On 04/17/2016 05:50 PM, Sergei Agalakov wrote:

Nobody asks for pg_dump to be a schema comparison tool. As you tell
yourself
it is a most reliable schema capturing tool. All I am asking is that if
pg_dump is executed
on two databases with the identical schemas and security it should be
able to produce
the identical SQL dumps of these schemas and security. As you have
mentioned in other e-mail
pg_dump actually rewrites some statements for consistency. It just
doesn't do it consistently everywhere.


And there in lies the rub. Making that happen, I suspect, is going to be 
a lot of work. The goal of the tool is not to produce output that is 
diff friendly but that produces working schema when transferred to 
another database. I understand what you want and why I just think it is 
not as easy as you want to believe. See my other post for ways to try to 
make this happen.




I can't say anything about priorities of development for pg_dump. The
proposed change seems to be
a low hanging fruit, it isn't difficult to add ORDER BY in the
appropriate places. The other question is if
this is a useful enhancement. The existence of the third party tools
doesn't seem to be very relevant here.
Should be stopped the development of pgAdmin or psql because exist the
third party tools with the similar functionality?
:-)


FYI, pgAdmin is a third party tool, currently being completely rewritten:

http://pgsnake.blogspot.com/2016/04/pgadmin-4-elephant-nears-finish-line.html



Sergei





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-18 Thread Adrian Klaver


On 04/17/2016 06:10 PM, Sergei Agalakov wrote:

Thank you, I know this place.
I just wanted to check that my request will have the peoples support.
So far it doesn't. It looks like that or people never need to compare
two PG databases to find the differences in the schemas or security,
or happy to use the third party tools to do it, and don't want any
native support. If I see any support from other people for this idea
then I shall
go to https://postgresql.uservoice.com/forums/21853-general, but looking
on, say, "Partitions in Oracle style" that are marked as have been
started in 2010
(sure, INHERITANCE is so much Oracle style partitions!) I don't see it
to be very useful.


Honestly I did not know that site existed. If you want some traction on 
this I would suggest the traditional way.


If you think it is a bug:
http://www.postgresql.org/support/submitbug/

If you think it should be a new feature then make your case on --hackers:
http://www.postgresql.org/list/pgsql-hackers/

FYI, the place I look for requested features is the Todo list:

https://wiki.postgresql.org/wiki/Todo



Sergei


fyi, if you have a feature request or enhancement, then the proper
place for that is here ->
https://postgresql.uservoice.com/forums/21853-general






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread David Rowley

On 18 April 2016 at 13:10, Sergei Agalakov  wrote:
> Thank you, I know this place.
> I just wanted to check that my request will have the peoples support.
> So far it doesn't. It looks like that or people never need to compare two PG
> databases to find the differences in the schemas or security,
> or happy to use the third party tools to do it, and don't want any native
> support. If I see any support from other people for this idea then I shall
> go to https://postgresql.uservoice.com/forums/21853-general, but looking on,
> say, "Partitions in Oracle style" that are marked as have been started in
> 2010
> (sure, INHERITANCE is so much Oracle style partitions!) I don't see it to be
> very useful.

I can't particularly vouch for that site, as I've personally never
seen it before, but I'd like to say that you'll probably get along
better if you appeared to have a more optimistic view. If you bothered
to consider the "parallel query option" item listed on that site, and
compared that to the current status of 9.6, you might feel
differently. EDB and others have put lots of work in to parallel query
for 9.6. If your intentions here are to gather support for your cause
then I highly recommend not appearing negative. Keep in mind that
you've not paid some company for a license for PostgreSQL and the
people reading your emails here are most likely not at your beckon
call, and are not here to fulfill all your PostgreSQL wishes.

To me your proposal does seem quite half thought through. Do you
really suppose we just sort the GRANT output and call it done. pg_dump
now has stable output? I think that would barely scratch the surface.
What about COPY output, we'd have to sort that too, and that could be
rather expensive. Now, you could say that we'd just limit this to
schema-only related stuff, and that might be ok, but you'll need to
ensure that everything is addressed and that your now matching output
didn't just occur because all of the planets happened to line up on
the day you ran pg_dump.  You might propose that we could get around
the performance hit of generating a stable output by having an
optional flag to enable this. That would appear to sound ok at my
first thought.   If C is your thing then you could open up pg_dump.c
and have a look around, if not then remaining positive and
constructive, and doing your best not to upset people who's C *is*
their thing is probably a good tactical move here.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Sergei Agalakov


Thank you, I know this place.
I just wanted to check that my request will have the peoples support.
So far it doesn't. It looks like that or people never need to compare 
two PG databases to find the differences in the schemas or security,
or happy to use the third party tools to do it, and don't want any 
native support. If I see any support from other people for this idea 
then I shall
go to https://postgresql.uservoice.com/forums/21853-general, but looking 
on, say, "Partitions in Oracle style" that are marked as have been 
started in 2010
(sure, INHERITANCE is so much Oracle style partitions!) I don't see it 
to be very useful.


Sergei

fyi, if you have a feature request or enhancement, then the proper 
place for that is here -> 
https://postgresql.uservoice.com/forums/21853-general



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Sergei Agalakov

Nobody asks for pg_dump to be a schema comparison tool. As you tell 
yourself
it is a most reliable schema capturing tool. All I am asking is that if 
pg_dump is executed
on two databases with the identical schemas and security it should be 
able to produce
the identical SQL dumps of these schemas and security. As you have 
mentioned in other e-mail
pg_dump actually rewrites some statements for consistency. It just 
doesn't do it consistently everywhere.

I can't say anything about priorities of development for pg_dump. The 
proposed change seems to be
a low hanging fruit, it isn't difficult to add ORDER BY in the 
appropriate places. The other question is if
this is a useful enhancement. The existence of the third party tools 
doesn't seem to be very relevant here.
Should be stopped the development of pgAdmin or psql because exist the 
third party tools with the similar functionality?

:-)

Sergei

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?

I don't think that is what is being said, more the right tool for the
right job. pg_dump --> pg_restore/psql are for capturing some or all of
the information(including possibly data) in a database at a point in
time so that information can be recreated at another point in time.
While by necessity that includes capturing schema data (or not in the
case of -a) it is not really a schema comparison tool. While that is in
the realm of doable it means developer time to replicate something that
other tools do. Given the ever lengthening list of requested features in
general, this request might have difficulty reaching a sufficient level
of priority, esp. in light of the presence of existing tools.

>
> Sergei
>
>> > On Apr 17, 2016, at 12:41 PM, Sergei Agalakov
>>  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(dot)klaver(at)aklaver(dot)com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Melvin Davidson

fyi, if you have a feature request or enhancement, then the proper place
for that is here -> https://postgresql.uservoice.com/forums/21853-general


On Sun, Apr 17, 2016 at 8:26 PM, Sergei Agalakov <
sergei.agala...@getmyle.com> wrote:

> I hardly can see that a sorting of the grants by users will create a
> measurable impact on the pg_dump performance in a real database.
> One can imaging a database with tens of thousands of objects and tens of
> thousands of users and almost no data, but it would be quite unusual.
> Anyway, if a sorting behavior is initiated by a command line parameter,
> and isn't a default behavior of pg_dump then this argument doesn't work.
> After all pg_dump isn't the tool to do _just_ reliable backup. It can be
> used for migration, it can be used for schema cloning, to initiate a
> standby...
> There are many flags for pg_dump that are absolutely unnecessary for full
> database backup. So they do
> "... might also overcomplicate it, making it more difficult to maintain
> reliably" but they do exists, and serve a purpose.
>
> I don't understand why people have started to create the theories about
> our development process? Had I requested a tool to magically synchronize
> DEV and PROD? No, I asked about a tool to _find_ the unexpected
> differences between databases. If you never encountered a situation when in
> the
> dozens of environments the databases has diverged because somebody has
> done something manually - good for you, you are lucky guy then.
> I did.
>
> Sergei
>
>> On Sun, 17 Apr 2016 14:10:50 -0600
>> 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?
>>
>> Nobody has weighed in on this, but I have a theory ...
>>
>> I (personally) worry that adding features like you suggest to pg_dump
>> would interfere with its ability to perform complete dump of a large
>> database in a _rapid_ manner. Using pg_dump as a backup tool has an
>> inherent desire for the tool to be as fast and low-impact on the
>> operation of the database as possible.
>>
>> Features that would force pg_dump to care about ordering that isn't
>> necessary to its core functionality of providing a reliable backup
>> are liable to slow it down. They might also overcomplicate it, making
>> it more difficult to maintain reliably.
>>
>> When you consider that possibility, and the fact that pg_dump isn't
>> _supposed_ to be a tool to help you with schema maintenance, it's easy
>> to see why someone would look for different approach to the problem.
>>
>> And I feel that's what all the answers have attempted to do: suggest
>> ways to get what you want without asking them to be implemented in a
>> tool that isn't really the right place for them anyway. While your
>> arguments toward making this change are valid, I'm not sure that
>> they are compelling enough to justify adding a feature where it
>> doesn't really belong.
>>
>> Another side to this, is that your request suggests that your
>> development process is suboptimal. Of course, I can't be 100% sure
>> since you haven't explained your process ... but my experience is
>> that people who feel the need to automagically sync prod and dev
>> databases have a suboptimal development process. Thus, the suggestions
>> are also biased toward helping you improve your process instead of
>> adjusting a tool to better support a suboptimal process.
>>
>> Of course, if the people actually doing the work on the code disagree
>> with me, then they'll make the change. I'm just expressing an opinion.
>>
>> > Sergei
>> >
>> > > > On Apr 17, 2016, at 12:41 PM, Sergei Agalakov
>>  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

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Sergei Agalakov

I hardly can see that a sorting of the grants by users will create a 
measurable impact on the pg_dump performance in a real database.
One can imaging a database with tens of thousands of objects and tens of 
thousands of users and almost no data, but it would be quite unusual.
Anyway, if a sorting behavior is initiated by a command line parameter, 
and isn't a default behavior of pg_dump then this argument doesn't work.
After all pg_dump isn't the tool to do _just_ reliable backup. It can be 
used for migration, it can be used for schema cloning, to initiate a 
standby...
There are many flags for pg_dump that are absolutely unnecessary for 
full database backup. So they do
"... might also overcomplicate it, making it more difficult to maintain 
reliably" but they do exists, and serve a purpose.

I don't understand why people have started to create the theories about 
our development process? Had I requested a tool to magically synchronize
DEV and PROD? No, I asked about a tool to _find_ the unexpected 
differences between databases. If you never encountered a situation when 
in the
dozens of environments the databases has diverged because somebody has 
done something manually - good for you, you are lucky guy then.

I did.

Sergei

On Sun, 17 Apr 2016 14:10:50 -0600
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?

Nobody has weighed in on this, but I have a theory ...

I (personally) worry that adding features like you suggest to pg_dump
would interfere with its ability to perform complete dump of a large
database in a _rapid_ manner. Using pg_dump as a backup tool has an
inherent desire for the tool to be as fast and low-impact on the
operation of the database as possible.

Features that would force pg_dump to care about ordering that isn't
necessary to its core functionality of providing a reliable backup
are liable to slow it down. They might also overcomplicate it, making
it more difficult to maintain reliably.

When you consider that possibility, and the fact that pg_dump isn't
_supposed_ to be a tool to help you with schema maintenance, it's easy
to see why someone would look for different approach to the problem.

And I feel that's what all the answers have attempted to do: suggest
ways to get what you want without asking them to be implemented in a
tool that isn't really the right place for them anyway. While your
arguments toward making this change are valid, I'm not sure that
they are compelling enough to justify adding a feature where it
doesn't really belong.

Another side to this, is that your request suggests that your
development process is suboptimal. Of course, I can't be 100% sure
since you haven't explained your process ... but my experience is
that people who feel the need to automagically sync prod and dev
databases have a suboptimal development process. Thus, the suggestions
are also biased toward helping you improve your process instead of
adjusting a tool to better support a suboptimal process.

Of course, if the people actually doing the work on the code disagree
with me, then they'll make the change. I'm just expressing an opinion.

> Sergei
>
> > > On Apr 17, 2016, at 12:41 PM, Sergei Agalakov 
 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?

>
>
> --
> Sent via pgsql-general mailing list 
(pgsql-general(at)postgresql(dot)org)

> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bill Moran

--
Sent via pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Adrian Klaver

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
 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.kla...@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Adrian Klaver

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?

I don't think that is what is being said, more the right tool for the 
right job. pg_dump --> pg_restore/psql are for capturing some or all of 
the information(including possibly data) in a database at a point in 
time so that information can be recreated at another point in time. 
While by necessity that includes capturing schema data (or not in the 
case of -a) it is not really a schema comparison tool. While that is in 
the realm of doable it means developer time to replicate something that 
other tools do. Given the ever lengthening list of requested features in 
general, this request might have difficulty reaching a sufficient level 
of priority, esp. in light of the presence of existing tools.

Sergei

> On Apr 17, 2016, at 12:41 PM, Sergei Agalakov
 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.kla...@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Bill Moran

On Sun, 17 Apr 2016 14:10:50 -0600
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?

Nobody has weighed in on this, but I have a theory ...

I (personally) worry that adding features like you suggest to pg_dump
would interfere with its ability to perform complete dump of a large
database in a _rapid_ manner. Using pg_dump as a backup tool has an
inherent desire for the tool to be as fast and low-impact on the
operation of the database as possible.

Features that would force pg_dump to care about ordering that isn't
necessary to its core functionality of providing a reliable backup
are liable to slow it down. They might also overcomplicate it, making
it more difficult to maintain reliably.

When you consider that possibility, and the fact that pg_dump isn't
_supposed_ to be a tool to help you with schema maintenance, it's easy
to see why someone would look for different approach to the problem.

And I feel that's what all the answers have attempted to do: suggest
ways to get what you want without asking them to be implemented in a
tool that isn't really the right place for them anyway. While your
arguments toward making this change are valid, I'm not sure that
they are compelling enough to justify adding a feature where it
doesn't really belong.

Another side to this, is that your request suggests that your
development process is suboptimal. Of course, I can't be 100% sure
since you haven't explained your process ... but my experience is
that people who feel the need to automagically sync prod and dev
databases have a suboptimal development process. Thus, the suggestions
are also biased toward helping you improve your process instead of
adjusting a tool to better support a suboptimal process.

Of course, if the people actually doing the work on the code disagree
with me, then they'll make the change. I'm just expressing an opinion.

> Sergei
> 
> > > On Apr 17, 2016, at 12:41 PM, Sergei Agalakov 
> > >  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?
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
Bill Moran

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Sergei Agalakov

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?

Sergei

> On Apr 17, 2016, at 12:41 PM, Sergei Agalakov 
 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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Bill Moran

On Sat, 16 Apr 2016 13:33:21 -0600
Sergei Agalakov  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

2016-04-17 Thread Karsten Hilbert

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

Re: [GENERAL] Enhancement request for pg_dump

18 matches

Site Navigation

Mail list logo

Footer information