Re: pgsql: Move handling of database properties from pg_dumpall into pg_dum

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jan 22, 2018 at 2:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> pg_dumpall with --clean will now drop and recreate the "postgres" and
>> "template1" databases in the target cluster, allowing their locale and
>> encoding settings to be changed if necessary, and providing a cleaner
>> way to set nondefault tablespaces for them than we had before. This
>> means that such a script must now always be started in the "postgres"
>> database; the order of drops and reconnects will not work otherwise.
>> Without --clean, the script will not adjust any database-level properties
>> of those two databases (including their comments, ACLs, and security
>> labels, which it formerly would try to set).

> Unless we insert some guard that causes a hard error or at least warns
> the user if they do the wrong thing, this seems extremely likely to
> have people (1) try to dump and restore using pg_dumpall and (2)
> complain when it doesn't work. Actually, it'll work fine if your OS
> username happens to be "postgres", but otherwise not so much.

Not entirely following your concern. The initial DB's name is "postgres"
regardless of the bootstrap superuser's name. If you're thinking of the
case where you do "initdb -U joe" and then try to "psql <pgdumpallscript"
without specifying a target DB name, that would have failed before and
still will, because there's no DB named "joe". Or if there is, the
DROP DATABASE on it would have failed because you're connected to it,
so it doesn't seem like a habit people would've got into.

The only practice that worked before and now will not is
"psql template1 <pgdumpallscript", and even there, the only thing that
really happens is that template1's locale/encoding/tablespace don't
get adjusted from the way initdb left them. Which was true anyway
for the locale/encoding.

regards, tom lane