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.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
