Re: pg_upgrade failing from 9.3 to 9.4 because "template0" already exists

On 03/06/2015 10:35 AM, Stephen Frost wrote:
> Adrian,
>
> * Adrian Klaver (adrian(dot)klaver(at)aklaver(dot)com) wrote:
>> On 03/06/2015 10:11 AM, Matt Landry wrote:
>>> Attempting to upgrade a large (>3TB) postgressql database from 9.3 to
>>> 9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The
>>> error message instructs me to look at the last few lines of
>>> pg_upgrade_utility.log for more info, and the last two lines there (the
>>> only ones that don't succeed) are:
>>>
>>> CREATE DATABASE "template0" WITH TEMPLATE = template0 OWNER = "postgres";
>>> psql:pg_upgrade_dump_globals.sql:44: ERROR: database "template0"
>>> already exists
>>
>> Looks to me like someone created their own template0 database in the
>> original cluster. You might do in psql a \l in the original cluster
>> to see if there is more than one template0.
>
> template0 is created during initdb and it shouldn't be possible to have
> more than one database named 'template0' in a given cluster (though if
> there is, that could certainly be a problem...).

Agreed, I am just trying to figure out how you get:

CREATE DATABASE "template0" WITH TEMPLATE = template0 ..

Seems to be a snake eating its tail:)

>
> Now, if someone renamed the original template0 and created a new one,
> that could be an issue but I think that's more because the newer
> template0 would be dumped by pg_dumpall as it'd be set to allow
> connections (which is the default).
>
> If I'm reading the code correctly, I think we have an entirely different
> issue here which is that databases with 'datallowconn' set to false
> won't be included in the upgrade and, while all the files will be there,
> the catalog won't be. That's a serious problem as people do actually
> modify that flag in some environments and if it happens to be set
> incorrectly when the pg_dumpall from pg_upgrade runs then those
> databases will disappear across the upgrade.. I've not checked to see
> if something else in the pg_upgrade process will catch this, but if not,
> we should definitely add something.
>
> Thanks,
>
> Stephen
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com