"Reiser, John J." <Reiser@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
