Robert Haas <robertmh...@gmail.com> writes: > On Thu, Sep 29, 2016 at 4:04 AM, Haribabu Kommi > <kommi.harib...@gmail.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 -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers