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

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.

>
> For reference, the pg_upgrade command as I'm running it is:
>
> /usr/lib/postgresql/9.4/bin/pg_upgrade -k -b /usr/lib/postgresql/9.3/bin
> -B /usr/lib/postgresql/9.4/bin -d /DB/postgres/data/9.3/main -D
> /DB/postgres/data/9.4/main -o '-c
> config_file=/etc/postgresql/9.3/main/postgresql.conf' -O '-c
> config_file=/etc/postgresql/9.4/main/postgresql.conf'
>
> Searching google for useful advice yields...little. (Mostly articles
> about problems upgrading from 8.4 to 9.2, whose solutions -- when
> they're described -- don't help here. Also, there are a number of
> sources saying I should just do a pg_dumpall and then a
> pg_restore...which would be fine, probably, if I could afford to take my
> production database offline for a week.)
>
> I do have a pg_dumpall backup done, and at the moment I'm doing all this
> on a test instance, so I can afford to experiment without risking data
> corruption or excessive downtime on the production server, but the whole
> point of the experimenting is to come up with a procedure that will work
> in production when it's time to do it there. Dump-and-restore definitely
> does not meet that requirement, even if it does eventually recreate a
> working database.
>
> Any ideas? I'm sure I'm missing something obvious, here, but I can't
> seem to find any reference that tells me what it would be.
>
>

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