"Reiser, John J." <rei...@rowan.edu> writes:
> I'm working on an upgrade to our database cluster, attempting to move from
> 8.4 to 9.2. I'm encountering the following error when I attempt the upgrade
> (in pg_upgrade_restore.log):
> CREATE FUNCTION "st_envelope_in"(cstring) RETURNS st_envelope
> LANGUAGE "c" IMMUTABLE STRICT
> AS 'st_geometry', 'ST_ENVELOPE_In';
> psql:pg_upgrade_dump_db.sql:371910: ERROR: duplicate key value violates
> unique constraint "pg_type_oid_index"
> DETAIL: Key (oid)=(1407909) already exists.
What this smells like is a bug in the pg_dump --binary_upgrade logic that
tries to preserve type OIDs from the old installation to the new one.
Is there a preceding CREATE TYPE command for st_envelope in the dump
script? Look for calls to binary_upgrade.set_next_pg_type_oid() and
binary_upgrade.set_next_array_pg_type_oid() in the dump script --- are
there conflicting entries? Also, exactly what is type 1407909 in the
old installation (try "select * from pg_type where oid = 1407909")?
> I'm running this on CentOS 6; both 8.4 and 9.2 are installed from the Yum
> repository. PostgreSQL is primarily used for GIS data and has ESRI
> st_geometry and PostGIS installed in several of the databases. (ESRI's
> support is only up to 9.2, which is why I'm not attempting a move to 9.3.)
> The interesting thing with this error is that when I wipe out the 9.2 data
> directory, re-initdb, and run the upgrade again, I now get a different error:
> CREATE TABLESPACE "sde1" OWNER "sde" LOCATION '/disk2/pgsql/data/sde';
> psql:pg_upgrade_dump_globals.sql:294: ERROR: directory
> "/disk2/pgsql/data/sde/PG_9.2_201204301" already in use as a tablespace
I think that's pilot error: you forgot to clean out tablespace directories
along with the main data directory.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
