Re: pg_upgrade if 'postgres' database is dropped

On Fri, Oct 28, 2011 at 9:55 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> On Fri, Oct 28, 2011 at 8:12 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > Yes, that would work, but see my summarization email on this. ?Using
>> > template1 is not a problem for pg_upgrade, it is the modifications to
>> > pg_dumpall that are an issue.
>>
>> I just did a bit of testing on this.  It appears that pg_dumpall, if
>> given a cluster containing no postgres database, will happily try to
>> connect to template1 instead.  If template1 isn't available either,
>> you can use "-l SOMEDBNAME" to specify the name of another database to
>> connect to instead.  So there is infinite flexibility there.
>>
>> But regardless of which database it uses to *generate* the dump, the
>> dump itself will *always* contain this, right at the very beginning:
>>
>> \connect postgres
>>
>> That line is in fact hard-coded as a literal string in pg_dumpall.c.
>> It seems like the easiest fix here might be to just remove that line
>> from the dump, because AFAICS it's completely pointless.  During the
>> time for which that setting is in effect, we're just restoring
>> globals, so it shouldn't matter which database we're connected to;
>> only that we have a valid connection.  So trying to switch the
>> connection from whatever the user is connected to currently to
>> postgres doesn't accomplish anything useful, but it does make it
>> possible for dump restoration to unnecessarily fail.
>
> If you remove that line,

I'm happy to do that, unless someone can see a hole in my logic.

> I can modify pg_upgrade to use template1
> instead of postgres, and then the user should just remove the postgres
> database from the new cluster before the upgrade --- we can give them a
> clear error message on that.

What I would prefer is to have the upgrade succeed, and just ignore
the existence of a postgres database in the new cluster. Maybe give
the user a notice and let them decide whether they wish to take any
action. I understand that failing is probably less code, but IMHO one
of the biggest problems with pg_upgrade is that it's too fragile:
there are too many seemingly innocent things that can make it croak
(which isn't good, when you consider that anyone using pg_upgrade is
probably in a hurry to get the upgrade done and the database back
on-line). It seems like this is an opportunity to get rid of one of
those unnecessary failure cases.

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