Re: pg_upgrade if 'postgres' database is dropped

On Fri, Oct 28, 2011 at 9:22 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> OK, the attached, applied patch removes the pg_upgrade dependency on the
> 'postgres' database existing in the new cluster.  However, vacuumdb,
> used by pg_upgrade, still has this dependency:
>
>            conn = connectDatabase("postgres", host, port, username,
>                prompt_password, progname);
>
> In fact, all the /scripts binaries use the postgres database, except for
> createdb/dropdb, which has this heuristic:
>
>    /*
>     * Connect to the 'postgres' database by default, except have the
>     * 'postgres' user use 'template1' so he can create the 'postgres'
>     * database.
>     */
>    conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
>                           host, port, username, prompt_password, progname);
>
> This makes sense because you might be creating or dropping the postgres
> database.  Do we want these to have smarter database selection code?

Well, I suppose as long as we're cleaning this up, we might as well be
thorough, so, sure, why not? I think the algorithm pg_dumpall uses is
pretty sensible: let the user specify the database to use if they so
desire; if not, try postgres first and then template1. I think we
could stick some logic for that in common.c which could be shared by
clusterdb, createdb, dropdb, dropuser, reindexdb, and vacuumdb.

However, we need to rethink the flag to be used for this: pg_dumpall
uses -l, but many of the other utilities already use that for some
other purpose, and it's not exactly mnemonic anyway. "-d" for
database could work, but that's also in use in some places, and
furthermore somewhat confusing since many if not all of these
utilities have an option to operate on a single database only, and you
might think that -d would specify the database to operate on, rather
than the one to be used to get the list of databases. pgAdmin uses
the term "maintenance database" to refer to a database to be used when
none is explicitly specified, and I think that's fairly clear
terminology. So I propose that we add a --maintenance-db option (with
no short form, since this is a relatively obscure need) to the tools
listed above. The tools will pass the associated value (or NULL if
the option is not specified) to the above-mentioned routine in
common.c, which will do the rest.

If nobody objects, I'll go do that. Hopefully that should be enough
to put this problem to bed more or less permanently.

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