Re: new --maintenance-db options

On Mon, Jun 25, 2012 at 11:57:36AM -0400, Robert Haas wrote:
> In retrospect, it seems as though it might have been a good idea to
> make the postgres database read-only and undroppable, so that all
> client utilities could count on being able to connect to it and get a
> list of databases in the cluster without the need for all this
> complexity.  Or else having some other way for a client to
> authenticate and list out all the available databases.  In the absence
> of such a mechanism, I don't think we can turn around and say that not
> having a postgres database is an unsupported configuration, and
> therefore we need some way to cope with it when it happens.

Well, we certainly don't allow 'template1' to be dropped:
test=> DROP DATABASE template1;ERROR:  cannot drop a template database

so you could make the argument that making 'postgres' undroppable seem
reasonable.  I should point out that it was EnterpriseDB that complained
about this related to their Advanced Server product, that doesn't have a
'postgres' database, but an 'edb' one.  I said that was their problem,
but when community users said they also dropped the 'postgres' database,
it became a community problem too.

Where are we going on this for PG 9.2?  9.3?  I hate to ship options in
9.2 that will be gone in 9.3.

FYI, we do allow the 'template1' database to be renamed:
test=> ALTER DATABASE template1 RENAME TO template2;ALTER DATABASE

Oops.  TODO?

> I think the original report that prompted this change was a complaint
> that pg_upgrade failed when the postgres database had been dropped.
> Now, admittedly, pg_upgrade fails for all kinds of crazy stupid
> reasons and the chances of fixing that problem completely any time in
> the next 5 years do not seem good, but that's not a reason not to keep
> plugging the holes we can.  Anyhow, the same commit that introduced
> --maintenance-db "fixed" that problem by making arranging to try both
> postgres and template1 before giving up...  but have two hard-coded
> database names either of which can be dropped or renamed seems only
> marginally better than having one, hence the switch.  Really, I think

Actually, 'template1' can't be dropped like 'postgres', but can be
renamed (which I think needs fixing).  I think falling back to template1
for missing 'postgres' database was the goal there.

> pg_upgrade needs this option too, unless we're going to kill the
> problem at its root by providing a reliable way to enumerate database
> names without first knowing the name one that you can connect to.

pg_upgrade doesn't use --maintenance-db because the tools now fallback
to template1, which again brings up the question of the usefulness of
the --maintenance-db options.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +