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@xxxxxxxxx> Date: Wednesday, January 1, 2014 at 11:53 AM To: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> Subject: 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):
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:
(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 201 Mullica Hill Road Glassboro, NJ 08028 phone: 856-256-4817 cell: 856-347-0047 twitter: @rowangeolab |
