Skip site navigation (1) Skip section navigation (2)

Re: pg_upgrade if 'postgres' database is dropped

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_upgrade if 'postgres' database is dropped
Date: 2011-10-28 14:05:00
Message-ID: CA+TgmoYxrANXwsLV9HhyW4bbMHaXBF=S0x=LXjyLrDRpsDfA5A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2011-10-28 14:07:51
Subject: Re: pg_upgrade if 'postgres' database is dropped
Previous:From: Bruce MomjianDate: 2011-10-28 13:55:49
Subject: Re: pg_upgrade if 'postgres' database is dropped

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group