Re: duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

"Reiser, John J." <Reiser(at)rowan(dot)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