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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
