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

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...).

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