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

On 01/01/2014 08:53 AM, Reiser, John J. wrote:
> 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)=() already exists.

Well this means an OID is being used twice in the system catalog
pg_type. You could look up that oid(1407909) in the 8.4 pg_type and see
what it is. Also look it up in the fresh pg_type when you init the 9.2
cluster.

>
> 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

When you ran the upgrade above it probably got as far as creating the
9.2 tablespaces in /disk2/pgsql/data/sde. You now have two versions of
the tablespaces, one labeled PG_8.4_* and the other PG_9.2_201204301.
Along with wiping out the 9.2 data directory you need to wipe out the
9.2 tablespace directory.

>
>
> (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>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com