Hello, 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. 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 have several of our ESRI SDE databases in their own tablespace.) Before starting this process, I made a complete file-based backup of the 8.4 data directory. When I restore the backup to /var/lib/pgsql and run pg_upgrade again, I receive the first error again, with the same exact OID value. I will admit I don't know much about Postgres internals and I'm not sure how to proceed with this duplicate OID issue. I'm going to try running pg_upgrade with the link option now, but I don't know if that will help. Any assistance provided would be greatly appreciated. Thanks, John John Reiser Geospatial Research Lab<http://gis.rowan.edu/> Rowan University<http://rowan.edu/geography> 201 Mullica Hill Road Glassboro, NJ 08028 phone: 856-256-4817 cell: 856-347-0047 twitter: @rowangeolab<http://twitter.com/rowangeolab>