Re: "Re: Question about grant create on database and pg_dump/pg_dumpall

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: "Re: Question about grant create on database and pg_dump/pg_dumpall
Date: 2016-09-29 15:29:09
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Sep 29, 2016 at 4:04 AM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>> I am also not sure whether pg_dumpall -g and then individual pg_dump
>> is the more widely used approach or not?

> That's the approach I normally recommend.

The fundamental thing we have to do in order to move forward on this is
to rethink what's the division of labor between pg_dump and pg_dumpall.
I find the patch as presented quite unacceptable because it's made no
effort to do that (or even to touch the documentation).

What do people think of this sketch:

1. pg_dump without --create continues to do what it does today, ie it just
dumps objects within the database, assuming that database-level properties
will already be set correctly for the target database.

2. pg_dump with --create creates the target database and also sets all
database-level properties (ownership, ACLs, ALTER DATABASE SET, etc etc).

3. pg_dumpall loses all code relating to individual-database creation
and property setting and instead relies on pg_dump --create to do that.
This would leave only the code relating to "pg_dumpall -g" (ie, dump roles
and tablespaces) within pg_dumpall itself.

One thing that would still be messy is that presumably "pg_dumpall -g"
would issue ALTER ROLE SET commands, but it's unclear what to do with
ALTER ROLE IN DATABASE SET commands. Should those become part of
"pg_dump --create"'s charter? It seems like not, but I'm not certain.

Another thing that requires some thought is that pg_dumpall is currently
willing to dump ACLs and other properties for template1/template0, though
it does not invoke pg_dump on them. If we wanted to preserve that
behavior while still moving the code that does those things to pg_dump,
pg_dump would have to grow an option that would let it do that. But
I'm not sure how much of that behavior is actually sensible.

This would probably take a pg_dump archive version bump, since I think
we don't currently record enough information for --create to do this
(and we can't just cram the extra commands into the DATABASE entry,
since we don't know whether --create will be specified to pg_restore).
But we've done those before.

Thoughts? Is there a better way to look at this?

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2016-09-29 15:33:52 Re: pg_basebackup, pg_receivexlog and data durability (was: silent data loss with ext4 / all current versions)
Previous Message David Steele 2016-09-29 15:26:29 Re: Fix checkpoint skip logic on idle systems by tracking LSN progress