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

On Fri, Sep 30, 2016 at 12:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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).

Patch marked as returned with feedback for now.

> 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.

I saw a couple of people willing to have that on those mailling lists
over the years... So +1 for the idea.

> 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.

I guess that we need to think about simplifying the restore flow if we
have the occasion:
1) First restore the result of pg_dumpall -g
2) Restore the state of each database's dump --create
pg_dump --create assigns the created database to its rightful owner,
so it assumes that the role has been already created. If we do not
include those commands in pg_dump --create we make the whole restore
scenario more complicated.

> 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.

An extra option looks like the way to go. There are people that
willingly set up ACLs and we could just document that something like
pg_dump --template needs to be run to generate the template's dump
values.

> 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.

Yep.

(By the way, last time I saw dump/restore integration into an internal
system we have finished by using pg_dumpall -g and then pg_dump on a
given DB for simplicity)
--
Michael