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

The --link argument doesn't work, either:

bash-4.1$ export LD_LIBRARY_PATH=/usr/pgsql-9.2/lib
bash-4.1$ /usr/pgsql-9.2/bin/pg_upgrade --old-datadir=/var/lib/pgsql/data --new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/bin --new-bindir=/usr/pgsql-9.2/bin --check
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for large objects ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

*Clusters are compatible*

bash-4.1$ /usr/pgsql-9.2/bin/pg_upgrade --old-datadir=/var/lib/pgsql/data --new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/bin --new-bindir=/usr/pgsql-9.2/bin --link
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for large objects ok
Creating catalog dump ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster *failure*

Consult the last few lines of "pg_upgrade_restore.log" for
the probable cause of the failure.
Failure, exiting
bash-4.1$ tail -n 20 pg_upgrade_restore.log

(1 row)

CREATE TYPE "spheroid" (
INTERNALLENGTH = 65,
INPUT = spheroid_in,
OUTPUT = spheroid_out,
ALIGNMENT = double,
STORAGE = plain
);
CREATE TYPE
ALTER TYPE "public"."spheroid" OWNER TO "reiser";
ALTER TYPE
SET search_path = "sde", pg_catalog;
SET
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.

Again, any help that you could provide would be greatly appreciated.
John

From: <Reiser>, John Reiser <reiser(at)rowan(dot)edu<mailto:reiser(at)rowan(dot)edu>>
Date: Wednesday, January 1, 2014 at 11:53 AM
To: "pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>" <pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>>
Subject: [GENERAL] duplicate OID issue when using pg_upgrade to move from 8.4 to 9.2

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>