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>
