Re: [HACKERS] pg_dumpall reccomendation in release notes

[Bruce Momjian]

On Thu, Aug 21, 2014 at 12:18:46PM -0400, Bruce Momjian wrote:
> I have developed the attached patch to address the issues raised above:
> 
> o  non-text output of pg_dump is mentioned
> o  mentions of using OID for keys is removed
> o  the necessity of pg_dumpall --globals-only is mentioned
> o  using pg_dump parallel mode rather than pg_dumpall for upgrades is 
> mentioned
> o  pg_upgrade is mentioned more prominently for upgrades
> o  replication upgrades are in their own section
> 
> I don't think we want to mention pg_upgrade as the _primary_
> major-version upgrade method.  While the pg_dump upgrade section is
> long, it is mostly about starting/stoping the server, moving
> directories, etc, the same things you have to do for pg_upgrade, so I
> just mentioned that int the pg_upgrade section.  Other ideas?
> 
> I plan to apply this to head and 9.4.

Updated patch attached and applied.

Any other suggestions?  Please let me know.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
new file mode 100644
index 06f064e..07ca0dc
*** a/doc/src/sgml/backup.sgml
--- b/doc/src/sgml/backup.sgml
***
*** 28,34 
SQL Dump
  

!The idea behind this dump method is to generate a text file with SQL
 commands that, when fed back to the server, will recreate the
 database in the same state as it was at the time of the dump.
 PostgreSQL provides the utility program
--- 28,34 
SQL Dump
  

!The idea behind this dump method is to generate a file with SQL
 commands that, when fed back to the server, will recreate the
 database in the same state as it was at the time of the dump.
 PostgreSQL provides the utility program
*** pg_dump d
*** 39,44 
--- 39,47 
  
 As you see, pg_dump writes its result to the
 standard output. We will see below how this can be useful.
+While the above command creates a text file, pg_dump
+can create files in other formats that allow for parallism and more
+fine-grained control of object restoration.

  

*** pg_dump d
*** 98,117 
 exclusive lock, such as most forms of ALTER TABLE.)

  
-   
-
- If your database schema relies on OIDs (for instance, as foreign
- keys) you must instruct pg_dump to dump the OIDs
- as well. To do this, use the -o command-line
- option.
-
-   
- 

 Restoring the Dump
  
 
! The text files created by pg_dump are intended to
  be read in by the psql program. The
  general command form to restore a dump is
  
--- 101,111 
 exclusive lock, such as most forms of ALTER TABLE.)

  

 Restoring the Dump
  
 
! Text files created by pg_dump are intended to
  be read in by the psql program. The
  general command form to restore a dump is
  
*** psql dbna
*** 127,132 
--- 121,128 
  supports options similar to pg_dump for specifying
  the database server to connect to and the user name to use. See
  the  reference page for more information.
+ Non-text file dumps are restored using the  utility.
 
  
 
*** psql -f i
*** 225,231 
  roles, tablespaces, and empty databases, then invoking
  pg_dump for each database.  This means that while
  each database will be internally consistent, the snapshots of
! different databases might not be exactly in-sync.
 

  
--- 221,234 
  roles, tablespaces, and empty databases, then invoking
  pg_dump for each database.  This means that while
  each database will be internally consistent, the snapshots of
! different databases are not sychronized.
!
! 
!
! Cluster-wide data can be dumped alone using the
! pg_dumpall --globals-only option.
! This is necessary to fully backup the cluster if running the
! pg_dump command on individual databases.
 

  
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
new file mode 100644
index 1d91d92..f337485
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
*** $ kill -INT `head -1 /usr/loc
*** 1517,1524 
 For major releases of PostgreSQL, the
 internal data storage format is subject to change, thus complicating
 upgrades.  The traditional method for moving data to a new major version
!is to dump and reload the database.  Other methods are available,
!as discussed below.

  

--- 1517,1525 
 For major releases of PostgreSQL, the
 internal data storage format is subject to change, thus complicating
 upgrades.  The traditional method for moving data to a new major version
!is to dump and reload the database, though this can be slow.  A
!faster method is .  Replication methods are
!also available, as discuss

Re: [HACKERS] pg_dumpall reccomendation in release notes

[Bruce Momjian]

On Tue, Feb 25, 2014 at 05:05:09PM -0800, Josh Berkus wrote:
> On 02/25/2014 04:42 PM, Bruce Momjian wrote:
> > On Tue, Feb 25, 2014 at 06:41:26PM -0500, Tom Lane wrote:
> >> I'm not sure what "many limitations" you think pg_dumpall has that pg_dump
> >> doesn't.
> >>
> >> I do think that it might be time to reword this to recommend pg_upgrade
> >> first, though.  ISTM that the current wording dates from when pg_upgrade
> >> could charitably be described as experimental.
> > 
> > Wow, so pg_upgrade takes the lead!  And from Tom too!  :-)
> > 
> > I agree with Tom that mentioning pg_dump/restore is going to lead to
> > global object data loss, and throwing the users to a URL with no
> > explanation isn't going to help either.  What we could do is to
> > restructure the existing text and add a link to the upgrade URL for more
> > details.
> 
> What I was suggesting was something like:
> 
> "Users upgrading from earlier versions will need to go through the
> entire upgrade procedure, as described on our upgrade page: "
> 
> The problem is that anything we say about "how to upgrade" in one short
> sentence is going to confuse some people.  BTW, the reason I got that
> question about pg_dump was that 9.2's release notes say "pg_dump" and
> 9.3's say "pg_dumpall", causing users to think there's been some kind of
> change.
> 
> Of course, this means I need to fix the upgrade page, and I need to
> write backported versions of that fix for at least 9.1 and 9.2.

I have developed the attached patch to address the issues raised above:

o  non-text output of pg_dump is mentioned
o  mentions of using OID for keys is removed
o  the necessity of pg_dumpall --globals-only is mentioned
o  using pg_dump parallel mode rather than pg_dumpall for upgrades is mentioned
o  pg_upgrade is mentioned more prominently for upgrades
o  replication upgrades are in their own section

I don't think we want to mention pg_upgrade as the _primary_
major-version upgrade method.  While the pg_dump upgrade section is
long, it is mostly about starting/stoping the server, moving
directories, etc, the same things you have to do for pg_upgrade, so I
just mentioned that int the pg_upgrade section.  Other ideas?

I plan to apply this to head and 9.4.


-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
new file mode 100644
index 06f064e..07ca0dc
*** a/doc/src/sgml/backup.sgml
--- b/doc/src/sgml/backup.sgml
***
*** 28,34 
SQL Dump
  

!The idea behind this dump method is to generate a text file with SQL
 commands that, when fed back to the server, will recreate the
 database in the same state as it was at the time of the dump.
 PostgreSQL provides the utility program
--- 28,34 
SQL Dump
  

!The idea behind this dump method is to generate a file with SQL
 commands that, when fed back to the server, will recreate the
 database in the same state as it was at the time of the dump.
 PostgreSQL provides the utility program
*** pg_dump d
*** 39,44 
--- 39,47 
  
 As you see, pg_dump writes its result to the
 standard output. We will see below how this can be useful.
+While the above command creates a text file, pg_dump
+can create files in other formats that allow for parallism and more
+fine-grained control of object restoration.

  

*** pg_dump d
*** 98,117 
 exclusive lock, such as most forms of ALTER TABLE.)

  
-   
-
- If your database schema relies on OIDs (for instance, as foreign
- keys) you must instruct pg_dump to dump the OIDs
- as well. To do this, use the -o command-line
- option.
-
-   
- 

 Restoring the Dump
  
 
! The text files created by pg_dump are intended to
  be read in by the psql program. The
  general command form to restore a dump is
  
--- 101,111 
 exclusive lock, such as most forms of ALTER TABLE.)

  

 Restoring the Dump
  
 
! Text files created by pg_dump are intended to
  be read in by the psql program. The
  general command form to restore a dump is
  
*** psql dbna
*** 127,132 
--- 121,128 
  supports options similar to pg_dump for specifying
  the database server to connect to and the user name to use. See
  the  reference page for more information.
+ Non-text file dumps are restored using the  utility.
 
  
 
*** psql -f i
*** 225,231 
  roles, tablespaces, and empty databases, then invoking
  pg_dump for each database.  This means that while
  each database will be internally consistent, the snapshots of
! different databases might not be exactly in-sync.
 

  
--- 221,234 
  roles, tablespaces, and empty databases, then invoking
  pg_dump fo

Re: [HACKERS] pg_dumpall reccomendation in release notes

[Josh Berkus]

On 02/25/2014 04:42 PM, Bruce Momjian wrote:
> On Tue, Feb 25, 2014 at 06:41:26PM -0500, Tom Lane wrote:
>> I'm not sure what "many limitations" you think pg_dumpall has that pg_dump
>> doesn't.
>>
>> I do think that it might be time to reword this to recommend pg_upgrade
>> first, though.  ISTM that the current wording dates from when pg_upgrade
>> could charitably be described as experimental.
> 
> Wow, so pg_upgrade takes the lead!  And from Tom too!  :-)
> 
> I agree with Tom that mentioning pg_dump/restore is going to lead to
> global object data loss, and throwing the users to a URL with no
> explaination isn't going to help either.  What we could do is to
> restructure the existing text and add a link to the upgrade URL for more
> details.

What I was suggesting was something like:

"Users upgrading from earlier versions will need to go through the
entire upgrade procedure, as described on our upgrade page: "

The problem is that anything we say about "how to upgrade" in one short
sentence is going to confuse some people.  BTW, the reason I got that
question about pg_dump was that 9.2's release notes say "pg_dump" and
9.3's say "pg_dumpall", causing users to think there's been some kind of
change.

Of course, this means I need to fix the upgrade page, and I need to
write backported versions of that fix for at least 9.1 and 9.2.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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

Re: [HACKERS] pg_dumpall reccomendation in release notes

[Bruce Momjian]

On Tue, Feb 25, 2014 at 06:41:26PM -0500, Tom Lane wrote:
> I'm not sure what "many limitations" you think pg_dumpall has that pg_dump
> doesn't.
> 
> I do think that it might be time to reword this to recommend pg_upgrade
> first, though.  ISTM that the current wording dates from when pg_upgrade
> could charitably be described as experimental.

Wow, so pg_upgrade takes the lead!  And from Tom too!  :-)

I agree with Tom that mentioning pg_dump/restore is going to lead to
global object data loss, and throwing the users to a URL with no
explaination isn't going to help either.  What we could do is to
restructure the existing text and add a link to the upgrade URL for more
details.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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

Re: [HACKERS] pg_dumpall reccomendation in release notes

[Josh Berkus]

On 02/25/2014 03:59 PM, Tom Lane wrote:
> If we had a page discussing the pros and cons of different upgrade
> methods, yeah, I'd be in favor of reducing the release-note text to a
> pointer to that page.  I don't see such a page in a quick skim of the
> fine manual's table of contents though?

I owe an update of
http://www.postgresql.org/docs/9.3/static/upgrading.html; I think I can
easily include a discussion of the various options there.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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

Re: [HACKERS] pg_dumpall reccomendation in release notes

[Tom Lane]

Josh Berkus  writes:
> On 02/25/2014 03:41 PM, Tom Lane wrote:
>> There's a very good reason for not recommending pg_dump in this context:
>> it won't dump everything.  Yeah, if you know what you're doing, you might
>> use per-database pg_dump runs plus pg_dumpall -g to catch the roles etc,
>> but we're not going to try to wedge all that info into one or two
>> sentences of release-note boilerplate.  If you can get that right then
>> you don't need the release notes to remind you anyway.  If you aren't
>> likely to get it right then the release notes would do you no service
>> by suggesting it.

> Right. But the fact that we don't mention it *at all* has caused some
> users to ask me if regular pg_dump doesn't work for upgrades anymore.

TBH, anybody who's asking that kind of question probably falls in the
category of "wouldn't get it right".  I've heard enough bitching from
novices who thought that pg_dump would be enough to get everything out
of their now-gone database that I have no desire to encourage use of
bare pg_dump here.

(Whether we shouldn't redesign the functionality of these programs
is a different discussion.  The release notes have to reflect what is,
though, not what might ideally be.)

> It does make me wonder if we should direct users to the upgrade page
> though, instead of the individual command pages.

If we had a page discussing the pros and cons of different upgrade
methods, yeah, I'd be in favor of reducing the release-note text to a
pointer to that page.  I don't see such a page in a quick skim of the
fine manual's table of contents though?

regards, tom lane


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

Re: [HACKERS] pg_dumpall reccomendation in release notes

[Josh Berkus]

On 02/25/2014 03:41 PM, Tom Lane wrote:
> Josh Berkus  writes:
>> Can we change this text in the template release notes?
> 
>> A dump/restore using
>> pg_dumpall,
>> or use of
>> pg_upgrade, is
>> required for those wishing to migrate data from any previous release.
> 
>> Again, here we're recommending pg_dumpall with its many limitations, and
>> not mentioning pg_dump/pg_restore at all.  This has caused several
>> people to ask me if pg_dump is not supported for upgrading anymore.  Fix?
> 
> There's a very good reason for not recommending pg_dump in this context:
> it won't dump everything.  Yeah, if you know what you're doing, you might
> use per-database pg_dump runs plus pg_dumpall -g to catch the roles etc,
> but we're not going to try to wedge all that info into one or two
> sentences of release-note boilerplate.  If you can get that right then
> you don't need the release notes to remind you anyway.  If you aren't
> likely to get it right then the release notes would do you no service
> by suggesting it.

Right. But the fact that we don't mention it *at all* has caused some
users to ask me if regular pg_dump doesn't work for upgrades anymore.
Which reminds me, I need to get the doc patch for the upgrade page in
this week.

It does make me wonder if we should direct users to the upgrade page
though, instead of the individual command pages.

> I'm not sure what "many limitations" you think pg_dumpall has that pg_dump
> doesn't.

Lack of parallel dump and restore is the biggest one.

> I do think that it might be time to reword this to recommend pg_upgrade
> first, though.  ISTM that the current wording dates from when pg_upgrade
> could charitably be described as experimental.

Yah.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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

Re: [HACKERS] pg_dumpall reccomendation in release notes

[Tom Lane]

Josh Berkus  writes:
> Can we change this text in the template release notes?

> A dump/restore using
> pg_dumpall,
> or use of
> pg_upgrade, is
> required for those wishing to migrate data from any previous release.

> Again, here we're recommending pg_dumpall with its many limitations, and
> not mentioning pg_dump/pg_restore at all.  This has caused several
> people to ask me if pg_dump is not supported for upgrading anymore.  Fix?

There's a very good reason for not recommending pg_dump in this context:
it won't dump everything.  Yeah, if you know what you're doing, you might
use per-database pg_dump runs plus pg_dumpall -g to catch the roles etc,
but we're not going to try to wedge all that info into one or two
sentences of release-note boilerplate.  If you can get that right then
you don't need the release notes to remind you anyway.  If you aren't
likely to get it right then the release notes would do you no service
by suggesting it.

I'm not sure what "many limitations" you think pg_dumpall has that pg_dump
doesn't.

I do think that it might be time to reword this to recommend pg_upgrade
first, though.  ISTM that the current wording dates from when pg_upgrade
could charitably be described as experimental.

regards, tom lane


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

[HACKERS] pg_dumpall reccomendation in release notes

[Josh Berkus]

Bruce, Tom:

Can we change this text in the template release notes?


A dump/restore using
pg_dumpall,
or use of
pg_upgrade, is
required for those wishing to migrate data from any previous release.


Again, here we're recommending pg_dumpall with its many limitations, and
not mentioning pg_dump/pg_restore at all.  This has caused several
people to ask me if pg_dump is not supported for upgrading anymore.  Fix?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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

Re: [HACKERS] pg_dumpall and temp_tablespaces dependency problem

[Tom Lane]

Heikki Linnakangas  writes:
> Barring objections, I'll write a patch to relax the checking on 
> default_text_search_config and temp_tablespaces to match search_path.

This seems like something that's going to come back again and again.
What do you think of changing things so that ALTER ROLE/DATABASE SET
*never* throw hard errors for bogus-seeming values?

regards, tom lane

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

[HACKERS] pg_dumpall and temp_tablespaces dependency problem

[Heikki Linnakangas]

create user foouser;
create tablespace temptblspc location '/tmp/tmptblspc';
alter user foouser set temp_tablespaces='temptblspc';

Run pg_dumpall. It will produce a dump like:

...
CREATE ROLE foouser;
ALTER ROLE foouser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB 
LOGIN NOREPLICATION;

ALTER ROLE foouser SET temp_tablespaces TO 'temptblspc';
...
CREATE TABLESPACE temptblspc OWNER heikki LOCATION '/tmp/tmptblspc';

That latter ALTER ROLE statement fails at restore:

ERROR:  tablespace "temptblspc" does not exist

The problem here is that the ALTER ROLE statement refers to the 
tablespace, which is created afterwards. There's two possible solutions 
to this that I can see:


1. Teach pg_dumpall to dump the ALTER ROLE statement after creating 
tablespaces.


2. Relax the check on ALTER ROLE to not throw an error when you set 
temp_tablespaces to a non-existent tablespace.


There's another GUC that has the same problem: 
default_text_search_config. Only that is worse, because text search 
configurations are local to a database, so reordering the statements in 
the pg_dumpall output won't help. So I'm leaning towards option 2, also 
because moving the ALTER ROLE statement in the dump would make it less 
readable. Relaxing the check would be consistent with setting 
search_path, where you get a NOTICE rather than an ERROR if you refer to 
a non-existent schema in the ALTER ROLE statement.


Barring objections, I'll write a patch to relax the checking on 
default_text_search_config and temp_tablespaces to match search_path.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Bruce Momjian]

Florian Pflug wrote:
> On Oct27, 2011, at 23:02 , Bruce Momjian wrote:
> > Florian Pflug wrote:
> >> On Oct21, 2011, at 16:42 , Phil Sorber wrote:
> >>> If you did want to make them immutable, I also like Florian's idea of
> >>> a dependency graph. This would make the dumps less readable though.
> >> 
> >> Hm, I kinda reversed my opinion on that, though - i.e., I no longer think
> >> that the dependency graph idea has much merit. For two reasons
> >> 
> >> First, dependencies work on OIDs, not on names. Thus, for the dependency
> >> machinery to work for GUCs, they'd also need to store OIDs instead of
> >> names of referenced schema objects. (Otherwise you get into trouble if
> >> objects are renamed)
> >> 
> >> Which of course doesn't work, at least for roles, because roles are
> >> shared objects, but referenced objects might be database-local.
> >> (search_path, for example).
> > 
> > Is this a TODO?
> 
> The idea quoted above, no. But
> 
>  Downgrade non-immutable (i.e., dependent on database state) checks during
>  "ALTER ROLE/DATABASE SET" to WARNINGs to avoid breakage during restore
> 
> makes for a fine TODO, I'd say.

Well, psql currently ignored restore errors too, so I am not sure what
the value of this is, except that pg_upgrade will not error exit, but I
am not sure that is a good idea here either.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Florian Pflug]

On Oct27, 2011, at 23:02 , Bruce Momjian wrote:
> Florian Pflug wrote:
>> On Oct21, 2011, at 16:42 , Phil Sorber wrote:
>>> If you did want to make them immutable, I also like Florian's idea of
>>> a dependency graph. This would make the dumps less readable though.
>> 
>> Hm, I kinda reversed my opinion on that, though - i.e., I no longer think
>> that the dependency graph idea has much merit. For two reasons
>> 
>> First, dependencies work on OIDs, not on names. Thus, for the dependency
>> machinery to work for GUCs, they'd also need to store OIDs instead of
>> names of referenced schema objects. (Otherwise you get into trouble if
>> objects are renamed)
>> 
>> Which of course doesn't work, at least for roles, because roles are
>> shared objects, but referenced objects might be database-local.
>> (search_path, for example).
> 
> Is this a TODO?

The idea quoted above, no. But

 Downgrade non-immutable (i.e., dependent on database state) checks during
 "ALTER ROLE/DATABASE SET" to WARNINGs to avoid breakage during restore

makes for a fine TODO, I'd say.

best regards,
Florian Pflug


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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Bruce Momjian]

Florian Pflug wrote:
> On Oct21, 2011, at 16:42 , Phil Sorber wrote:
> > If you did want to make them immutable, I also like Florian's idea of
> > a dependency graph. This would make the dumps less readable though.
> 
> Hm, I kinda reversed my opinion on that, though - i.e., I no longer think
> that the dependency graph idea has much merit. For two reasons
> 
> First, dependencies work on OIDs, not on names. Thus, for the dependency
> machinery to work for GUCs, they'd also need to store OIDs instead of
> names of referenced schema objects. (Otherwise you get into trouble if
> objects are renamed)
> 
> Which of course doesn't work, at least for roles, because roles are
> shared objects, but referenced objects might be database-local.
> (search_path, for example).

Is this a TODO?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Florian Pflug]

On Oct21, 2011, at 16:42 , Phil Sorber wrote:
> If you did want to make them immutable, I also like Florian's idea of
> a dependency graph. This would make the dumps less readable though.

Hm, I kinda reversed my opinion on that, though - i.e., I no longer think
that the dependency graph idea has much merit. For two reasons

First, dependencies work on OIDs, not on names. Thus, for the dependency
machinery to work for GUCs, they'd also need to store OIDs instead of
names of referenced schema objects. (Otherwise you get into trouble if
objects are renamed)

Which of course doesn't work, at least for roles, because roles are
shared objects, but referenced objects might be database-local.
(search_path, for example).

best regards,
Florian Pflug


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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Phil Sorber]

On Wed, Oct 19, 2011 at 7:46 PM, Florian Pflug  wrote:
> On Oct20, 2011, at 01:19 , Tom Lane wrote:
>> Florian Pflug  writes:
>>> Taking this even further, why do we bother with non-immutable (i.e.,
>>> depending on the database's contents) checks during ALTER ROLE/DATABASET SET
>>> at all?
>>
>> Yeah, I was wondering about that one too.  It would not solve all the
>> problems here, but skipping validity checks would fix some of them.
>> The trouble of course is what happens if the value is found to be bad
>> when you try to use it ...
>
> Presumably we'd detect that during logon, because the GUC assignment
> hook will quite probably complain. I'd vote for emitting a warning in
> that case. This is also what we due currently if we fail to set the
> GUC to the desired value due to permission issues
>
> postgres=# create role r1 login;
> CREATE ROLE
> postgres=# create role r2;
> CREATE ROLE
> postgres=# alter role r1 set role = r2;
> ALTER ROLE
> postgres=# \connect - r1
> WARNING:  permission denied to set role "r2"
> WARNING:  permission denied to set role "r2"
> You are now connected to database "postgres" as user "r1".
>
> (Dunno why that WARNING appears twice)
>
> Since an ALTER DATABASE/ROLE SET doesn't prevent the user from overriding
> the value, ignoring invalid settings shouldn't be a security risk.

I didn't realize these dependencies weren't immutable. If that is the
desired behavior, then I agree a warning should be sufficient to catch
typo's and oversights.

If you did want to make them immutable, I also like Florian's idea of
a dependency graph. This would make the dumps less readable though.

>
> best regards,
> Florian Pflug
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Florian Pflug]

On Oct20, 2011, at 01:19 , Tom Lane wrote:
> Florian Pflug  writes:
>> Taking this even further, why do we bother with non-immutable (i.e.,
>> depending on the database's contents) checks during ALTER ROLE/DATABASET SET
>> at all?
> 
> Yeah, I was wondering about that one too.  It would not solve all the
> problems here, but skipping validity checks would fix some of them.
> The trouble of course is what happens if the value is found to be bad
> when you try to use it ...

Presumably we'd detect that during logon, because the GUC assignment
hook will quite probably complain. I'd vote for emitting a warning in
that case. This is also what we due currently if we fail to set the
GUC to the desired value due to permission issues

postgres=# create role r1 login;
CREATE ROLE
postgres=# create role r2;
CREATE ROLE
postgres=# alter role r1 set role = r2;
ALTER ROLE
postgres=# \connect - r1
WARNING:  permission denied to set role "r2"
WARNING:  permission denied to set role "r2"
You are now connected to database "postgres" as user "r1".

(Dunno why that WARNING appears twice)

Since an ALTER DATABASE/ROLE SET doesn't prevent the user from overriding
the value, ignoring invalid settings shouldn't be a security risk.

best regards,
Florian Pflug


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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Tom Lane]

Florian Pflug  writes:
> Taking this even further, why do we bother with non-immutable (i.e.,
> depending on the database's contents) checks during ALTER ROLE/DATABASET SET
> at all?

Yeah, I was wondering about that one too.  It would not solve all the
problems here, but skipping validity checks would fix some of them.
The trouble of course is what happens if the value is found to be bad
when you try to use it ...

regards, tom lane

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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Florian Pflug]

On Oct20, 2011, at 00:09 , Tom Lane wrote:
> Robert Haas  writes:
>> On Wed, Oct 19, 2011 at 5:13 PM, Tom Lane  wrote:
>>> I'm beginning to think that the correct solution to these problems is to
>>> greatly restrict what you can set in ALTER ROLE/DATABASE SET.  Or at
>>> least to document that if you use it, you get to keep both pieces after
>>> you break pg_dump.
> 
>> This is another instance of the general principle that we need to
>> create all the objects first, and then set their properties.  I
>> believe you came up with one counterexample where we needed to set the
>> GUC first in order to be able to create the object, but ISTM most of
>> them are going the other way.
> 
> Well, a "general principle" for which we already know one counterexample
> isn't general enough for me.  The problem that I'm having here is that
> it's not clear that there is any general solution, short of pg_dumpall
> having variable-by-variable knowledge of which GUCs to set when, and
> maybe even that wouldn't be good enough.

This whole issue reminds me of the situation we had before pg_dump
had the smarts to traverse the object dependency graph and emit schema
objects in the correct order. (pg_dump gained that ability somewhere
around 7.3 or 7.4 if memory serves correctly)

So here's a wild idea. Could we somehow make use of the dependency
machinery to solve this once and for all? Maybe we could record the
dependency between per role and/or database GUC settings and the
referenced objects.

Or we could add a flag "FORCE" to ALTER ROLE/DATABASE SET for pg_dump's
benefit which would skip all validity checks on the value (except it being
of the correct type, maybe).

Taking this even further, why do we bother with non-immutable (i.e.,
depending on the database's contents) checks during ALTER ROLE/DATABASET SET
at all? If we don't record a dependency on referenced schema objects,
nothing prevents that object from being dropped *after* the ALTER ROLE/DATABASE
SET occurred...

If we're trying to protect against typos in settings such as default_tablespace,
a WARNING ought to be sufficient.

best regards,
Florian Pflug


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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Tom Lane]

Robert Haas  writes:
> On Wed, Oct 19, 2011 at 5:13 PM, Tom Lane  wrote:
>> I'm beginning to think that the correct solution to these problems is to
>> greatly restrict what you can set in ALTER ROLE/DATABASE SET.  Or at
>> least to document that if you use it, you get to keep both pieces after
>> you break pg_dump.

> This is another instance of the general principle that we need to
> create all the objects first, and then set their properties.  I
> believe you came up with one counterexample where we needed to set the
> GUC first in order to be able to create the object, but ISTM most of
> them are going the other way.

Well, a "general principle" for which we already know one counterexample
isn't general enough for me.  The problem that I'm having here is that
it's not clear that there is any general solution, short of pg_dumpall
having variable-by-variable knowledge of which GUCs to set when, and
maybe even that wouldn't be good enough.

regards, tom lane

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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Robert Haas]

On Wed, Oct 19, 2011 at 5:13 PM, Tom Lane  wrote:
> "David E. Wheeler"  writes:
>> We've just found an issue with pg_dumpall in 9.1.1 where a dump starts with 
>> lines like these:
>
>>     ALTER ROLE dude WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
>> PASSWORD 'md5bdd7f8e73a214981b1519212b02a5530' VALID UNTIL 'infinity';
>>     ALTER ROLE dude SET default_tablespace TO 'users';
>
> I'm beginning to think that the correct solution to these problems is to
> greatly restrict what you can set in ALTER ROLE/DATABASE SET.  Or at
> least to document that if you use it, you get to keep both pieces after
> you break pg_dump.

This is another instance of the general principle that we need to
create all the objects first, and then set their properties.  I
believe you came up with one counterexample where we needed to set the
GUC first in order to be able to create the object, but ISTM most of
them are going the other way.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[David E. Wheeler]

On Oct 19, 2011, at 2:13 PM, Tom Lane wrote:

>>ALTER ROLE dude WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
>> PASSWORD 'md5bdd7f8e73a214981b1519212b02a5530' VALID UNTIL 'infinity';
>>ALTER ROLE dude SET default_tablespace TO 'users';
> 
> I'm beginning to think that the correct solution to these problems is to
> greatly restrict what you can set in ALTER ROLE/DATABASE SET.  Or at
> least to document that if you use it, you get to keep both pieces after
> you break pg_dump.

Sorry, get to keep what two pieces?

Best,

David


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

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[Tom Lane]

"David E. Wheeler"  writes:
> We've just found an issue with pg_dumpall in 9.1.1 where a dump starts with 
> lines like these:

> ALTER ROLE dude WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
> PASSWORD 'md5bdd7f8e73a214981b1519212b02a5530' VALID UNTIL 'infinity';
> ALTER ROLE dude SET default_tablespace TO 'users';

I'm beginning to think that the correct solution to these problems is to
greatly restrict what you can set in ALTER ROLE/DATABASE SET.  Or at
least to document that if you use it, you get to keep both pieces after
you break pg_dump.

regards, tom lane

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

[HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

[David E. Wheeler]

Hackers,

We've just found an issue with pg_dumpall in 9.1.1 where a dump starts with 
lines like these:

ALTER ROLE dude WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
PASSWORD 'md5bdd7f8e73a214981b1519212b02a5530' VALID UNTIL 'infinity';
ALTER ROLE dude SET default_tablespace TO 'users';

And later in the file has lines like this:

CREATE TABLESPACE users OWNER postgres LOCATION 
'/data/postgres/pg_tblspc/users';

Unsurprisingly, perhaps, this results in errors such as:

ERROR:  invalid value for parameter "default_tablespace": "users"

Seems to me that default_tablespace should only be set after tablespaces are 
created, no?

This is wreaking havoc with our ability to run pg_upgrade, FWIW.

Best,

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

Re: [HACKERS] pg_dumpall

[Josh Berkus]

Aaron,

> I have been having problems using the pg_dumpall function. I am trying to
> dump all the information on my database onto a file or view it. Actually
> what am trying to achieve is by using the pg_dumpall i can view dead rows
> once the db has been dumped into a file. I had a look online and tried to
> run the pg_dumpall command from dos using this code:

Please take your questions to the pgsql-novice mailing list.  This list,
pgsql-hackers is for working on the PostgreSQL code.

http://archives.postgresql.org/pgsql-novice/

Thanks!

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

[HACKERS] pg_dumpall

[aaronenabs]

Hi there,

I have been having problems using the pg_dumpall function. I am trying to
dump all the information on my database onto a file or view it. Actually
what am trying to achieve is by using the pg_dumpall i can view dead rows
once the db has been dumped into a file. I had a look online and tried to
run the pg_dumpall command from dos using this code:

pg_dump Test > aaron.db

but came up with this error:

C:\Program Files\PostgreSQL\9.0\bin>pg_dump Test > aaron.db
Password:
pg_dump: [archiver (db)] connection to database "Test" failed: FATAL: 
password
authentication failed for user "Enabs"

test being the name of the database and aaron.db being the output file
wanted it to view
again am sure you might think the password authentication failed due to
password, but changed the password, tried again and still no success..

If anyone can link me to a video or example on how the pgdump works or how
to use it, i would be very grateful. Thanks

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-dumpall-tp4270539p4270539.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

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

Re: [HACKERS] pg_dumpall --clean is completely broken

[Tom Lane]

Bruce Momjian  writes:
> Does your recently-applied patch address any of these TODO items?

>   Stop dumping CASCADE on DROP TYPE commands in clean mode

That has nothing to do with pg_dumpall.

>   Allow pg_dump --clean to drop roles that own objects or
>   have privileges 

Hmm ... pg_dump never drops roles at all, and shouldn't.  Is this
a garbled reference to pg_dumpall?  If so I might've fixed it.
It's not entirely clear what the item is about though.

regards, tom lane

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

Re: [HACKERS] pg_dumpall --clean is completely broken

[Bruce Momjian]

Tom Lane wrote:
> A thread over in -admin has made me realize the truth of $SUBJECT.
> With --clean, pg_dumpall does indeed emit a DROP command for each
> role, tablespace, or database ... just before recreating it.  This
> takes no account of dependencies and so the role and tablespace
> drops are pretty much guaranteed to fail due to databases still
> depending on them.
> 
> I'm not sure if we need any real dependency analysis.  It seems
> like it would be sufficient to issue the drops in a separate
> pass:
>   - drop all the databases
>   - drop all the tablespaces
>   - drop all the roles
>   - go on with creation
> 
> The roles might still have references to each other in step 3,
> but the DROP ROLE docs claim that's okay (I haven't tested).

Does your recently-applied patch address any of these TODO items?

Stop dumping CASCADE on DROP TYPE commands in clean mode

Allow pg_dump --clean to drop roles that own objects or
have privileges 

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

[HACKERS] pg_dumpall --clean is completely broken

[Tom Lane]

A thread over in -admin has made me realize the truth of $SUBJECT.
With --clean, pg_dumpall does indeed emit a DROP command for each
role, tablespace, or database ... just before recreating it.  This
takes no account of dependencies and so the role and tablespace
drops are pretty much guaranteed to fail due to databases still
depending on them.

I'm not sure if we need any real dependency analysis.  It seems
like it would be sufficient to issue the drops in a separate
pass:
- drop all the databases
- drop all the tablespaces
- drop all the roles
- go on with creation

The roles might still have references to each other in step 3,
but the DROP ROLE docs claim that's okay (I haven't tested).

Comments?

regards, tom lane

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

Re: [HACKERS] pg_dumpall --clean versus roles and shared dependencies

[Tom Lane]

Some time ago I wrote:
> I've been trying to figure out what to do about pg_dumpall's --clean
> option in view of our recent changes.

(for the rest, see
http://archives.postgresql.org/pgsql-hackers/2005-07/msg01143.php

Since we haven't come up with any bright ideas, and it seems far too
late in the cycle to invent new features like "revoke  from *",
I propose that we just punt and make "pg_dumpall --clean" do the
straightforward thing of emitting a "DROP ROLE" command for each role
(user or group) it intends to re-create.

This eliminates the previous behavior of attempting to drop every user
and group in the destination installation.  I'm of the opinion that
that's a good thing; there was no such "scorched earth" policy for other
object types, and indeed one would wonder what's the point of having a
--clean option to do that.  You might as well just initdb before loading
the dump file.

There will not be a need to special-case the superuser, either, since
any attempt to drop the owner of template0 will of course fail.

The principal problem with this approach is that since we lack "DROP
ROLE CASCADE", the drops are entirely likely to fail (they will fail
if the roles hold any privileges or own any objects).  This makes the
whole feature rather pointless.  However, that's a problem we will not
be able to solve in the 8.1 time frame, so I propose we live with it.
It's not clear to me that pg_dumpall --clean is a sufficiently widely
used feature to be worth agonizing over.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] pg_dumpall --clean versus roles and shared dependencies

[Tom Lane]

I've been trying to figure out what to do about pg_dumpall's --clean
option in view of our recent changes.  The problem is that pg_dumpall
tries to delete existing users and groups by putting this in its
output script:

DELETE FROM pg_shadow
  WHERE usesysid <> (SELECT datdba FROM pg_database
 WHERE datname = 'template0');
DELETE FROM pg_group;

CVS tip of course will just respond to these with
ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.

So we have two problems: what do we want 8.1 pg_dumpall to do instead,
and what are we going to do about legacy pg_dump scripts that already
contain these commands?

A couple of relevant points:

* Nowhere else do pg_dump and pg_dumpall interpret --clean as a license
for a scorched-earth policy; rather, it means "drop the specific objects
you are going to re-create".  One could argue that these commands are
therefore wrong by design, and what we should emit instead is DROP ROLE
commands for just the individual roles we are going to create.

* In view of the shared-dependencies patch, it is *highly* likely that
some or all of the deletions would fail anyway, due to the users owning
objects or permissions that haven't been deleted (yet).  We could reduce
the risk of this by emitting DROP DATABASE commands before the DROP ROLE
commands, but of course this doesn't fix things if there are additional
databases in the target installation.

I am strongly tempted to propose that --clean is wrongheaded when it
comes to roles, and that pg_dumpall should just always emit all role
information in the style
CREATE ROLE foo;
ALTER ROLE foo WITH ... options ... ;
which will have the effect of ensuring that the role exists with all the
desired settings whether it pre-existed or not.

Role membership data is a different story.  Part of the effect of
DELETE FROM pg_group;
was to eliminate group membership data as well as the groups themselves.
As of CVS tip, I have the code doing this instead:
DELETE FROM pg_auth_members;
followed by GRANT commands to restore membership links.  I do not like
this solution though.  In the first place, it is still following a
scorched-earth policy, which will completely mess up any pre-existing
groups in the destination installation, even (or especially) if they
are unrelated to what the dump script is loading.  In the second place,
this is certainly failing to learn from experience: we should not have
the output scripts presuming such familiarity with system catalogs of
future Postgres releases.

One possibility is to invent a "REVOKE role FROM *" kind of command
and have --clean mode issue that for each role being reloaded.

This still leaves us with the question of "what about the DELETE
commands in existing scripts?".  I thought about adding rules and
triggers to try to make those operations do approximately what is
intended, but I fear it's a hopeless cause: because of shared
dependencies there is little or no likelihood that *all* of the
individual user drops will succeed, and since they would necessarily
be happening in a single transaction, that means none of them will.
So my current idea is to just ignore the problem: those commands
won't do what is intended but they should be relatively harmless.

Thoughts?

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[HACKERS] pg_dumpall

[Christopher Kings-Lynne]

Hi,

In postgresql 7.3.1, if I do pg_dumpall -c, at the top of the dump file is
this:


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] pg_dumpall problem in 7.1 and cvs

[Tom Lane]

"Oliver Elphick" <[EMAIL PROTECTED]> writes:
> If there is a comment on a view, pg_dumpall can put them in the wrong order:

I've committed a fix for this in both CVS tip and REL7_1_STABLE.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[HACKERS] pg_dumpall problem in 7.1 and cvs

[Oliver Elphick]

If there is a comment on a view, pg_dumpall can put them in the wrong order:

--
-- pg_dumpall (7.2devel)
--
...
--
-- TOC Entry ID 363 (OID 31291)
--
-- Name: VIEW "all_persons" Type: COMMENT Owner:
--

COMMENT ON VIEW "all_persons" IS 'All persons - individuals or not';

--
-- TOC Entry ID 362 (OID 31308)
--
-- Name: all_persons Type: VIEW Owner: olly
--

CREATE VIEW "all_persons" as SELECT person.ptype, person.id, person.name, 
person.address, person.salutation, person.envelope, person.email, person.www 
FROM person;



This seems to have happened with every view in this dump.  I haven't managed
to work out why it happens.

-- 
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight  http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
 
 "All scripture is given by inspiration of God, and is 
  profitable for doctrine, for reproof, for correction, 
  for instruction in righteousness;"  
 II Timothy 3:16 



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [HACKERS] pg_dumpall problem in 7.1 and cvs

[Tom Lane]

"Oliver Elphick" <[EMAIL PROTECTED]> writes:
> If there is a comment on a view, pg_dumpall can put them in the wrong order:

Drat.  I fixed the identical problem for permissions a little while ago,
but didn't realize that it extended to comments too.  Thanks for the
report!

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] pg_dumpall (7.1beta1, current CVS)

[Larry Rosenman]

* Tom Lane <[EMAIL PROTECTED]> [010101 13:16]:
> Larry Rosenman <[EMAIL PROTECTED]> writes:
> > I noticed today that pg_dumpall from current CVS does *NOT*
> > dump a password assiged to the postgres user.  
> 
> > I consider this BAD, since if one has to restore from
> > a pg_dumpall, one may forget to reset the password. 
> 
> I'm unconvinced.  The pg_dumpall script is clearly intended to allow
> restoration into a new database installation with a different superuser;
> you will note that the script is careful not to assume that the old and
> new superuser names are the same (an assumption your proposed patch
> does make).
> 
> In any case the new installation certainly already *has* a superuser.
> I'm not sure it's the job of the restore script to change his password
> for him.
> 
> This does point up that there is some state that is not saved/restored
> by pg_dumpall --- the pg_hba.conf file and other config files that
> live in $PGDATA come to mind.  Perhaps there should be a separate
> utility that saves/restores these.  (pg_dump can't do it because there's
> no way to retrieve these files through a database connection.)
How would you suggest doing this?  A shell script that makes a SHAR or
somesuch?  Or what?  Should it save the SuperUser password?  

I agree that this is a shortcoming.  

As to the SuperUser password thing, how do we remind the user that
they had one set?  Can we at least put out a comment in the pg_dumpall
output that mentions it? 


> 
>   regards, tom lane
-- 
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: [HACKERS] pg_dumpall (7.1beta1, current CVS)

[Tom Lane]

Larry Rosenman <[EMAIL PROTECTED]> writes:
> I noticed today that pg_dumpall from current CVS does *NOT*
> dump a password assiged to the postgres user.  

> I consider this BAD, since if one has to restore from
> a pg_dumpall, one may forget to reset the password. 

I'm unconvinced.  The pg_dumpall script is clearly intended to allow
restoration into a new database installation with a different superuser;
you will note that the script is careful not to assume that the old and
new superuser names are the same (an assumption your proposed patch
does make).

In any case the new installation certainly already *has* a superuser.
I'm not sure it's the job of the restore script to change his password
for him.

This does point up that there is some state that is not saved/restored
by pg_dumpall --- the pg_hba.conf file and other config files that
live in $PGDATA come to mind.  Perhaps there should be a separate
utility that saves/restores these.  (pg_dump can't do it because there's
no way to retrieve these files through a database connection.)

regards, tom lane

Re: [HACKERS] pg_dumpall (7.1beta1, current CVS)

[Larry Rosenman]

* Larry Rosenman <[EMAIL PROTECTED]> [001231 17:59]:
> I noticed today that pg_dumpall from current CVS does *NOT*
> dump a password assiged to the postgres user.  
> 
> I consider this BAD, since if one has to restore from
> a pg_dumpall, one may forget to reset the password. 
> 
> LER
And, it was easier than I thought to fix it

Here is a patch:

Index: src/bin/pg_dump/pg_dumpall.sh
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.sh,v
retrieving revision 1.9
diff -c -r1.9 pg_dumpall.sh
*** src/bin/pg_dump/pg_dumpall.sh   2000/12/19 22:12:47 1.9
--- src/bin/pg_dump/pg_dumpall.sh   2001/01/01 01:33:58
***
*** 164,169 
--- 164,174 
  || CAST(valuntil AS TIMESTAMP) ||  ELSE '' END || ';'
  FROM pg_shadow
  WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');
+ SELECT 'ALTER USER "' || usename || '"' 
+   || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd ||  ELSE  ''
+ END || ';' 
+ FROM pg_shadow 
+ WHERE usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0');
  __END__
  echo
  
-- 
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

[HACKERS] pg_dumpall (7.1beta1, current CVS)

[Larry Rosenman]

I noticed today that pg_dumpall from current CVS does *NOT*
dump a password assiged to the postgres user.  

I consider this BAD, since if one has to restore from
a pg_dumpall, one may forget to reset the password. 

LER

-- 
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: [HACKERS] pg_dumpall --accounts-only

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> To make a long story short, a more correct name for this option would be
> "--globals-only".  Is it okay to change this?

Okay with me...

regards, tom lane

[HACKERS] pg_dumpall --accounts-only

[Peter Eisentraut]

I've been sneaking a peek at the Great Bridge documentation regarding the
pg_dumpaccounts utility.  You may recall that I added the pg_dumpall
--accounts-only option to provide the same functionality.  But it occurred
to me that the name is wrong.

The way it reads in the GB docs is that pg_dumpaccounts will dump the
global structures, whereas pg_dump dumps the local structures, and
pg_dumpall is sort of a wrapper that does both.  (Not a bad idea really;
if they would have discussed it we might even have known about it.)

But equating "accounts" and "global" is wrong (leaving aside the fact that
there's no such thing as an "account" is SQL or Postgres).  If/when table
spaces are implemented, a table space would probably be a global object.
If/when roles are implemented, then groups will cease to be a global
object.

To make a long story short, a more correct name for this option would be
"--globals-only".  Is it okay to change this?

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/