duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Hello.

I’ve trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this issue…

I’ve found a similar thread, but going from 8.4 to 9.2:

https://www.postgresql.org/message-id/flat/52C44FA9.2080500%40gmail.com#14ab24f6c94287dd4c435652cb2d77f8

This ended up without really pinpointing the problem as the poster did not follow up.

Hopefully I’ll have more luck…

I’m posting below more output, here I’ll crop the interesting bits.

(don’t know if this is pertinent for this discussion but Pg_upgrade complains with 2 errors but still executes, and –check turns out ok.

These are:

pg_upgrade: could not start process for command ""D:\Program Files\PostgreSQL\9.

5\bin\pg_upgrade"  -U postgres --jobs=6 -d "D:\Program Files\PostgreSQL\9.3\data

" -b "D:\Program Files\PostgreSQL\9.3\bin" -D "D:\Program Files\PostgreSQL\9.5\d

ata" -B "D:\Program Files\PostgreSQL\9.5\bin"": error code 193

pg_upgrade: could not re-execute with restricted token: error code 3

)

What I’m seeing from pg_upgrade:

Restoring database schemas in the new cluster

  template1

*failure*

Consult the last few lines of "pg_upgrade_dump_16393.log" for

the probable cause of the failure.

Failure, exiting

So, looking at the log file, it’s not very long, at the end where it blows up:

pg_restore: creating TYPE "sde.se_coord"

pg_restore: creating SHELL TYPE "sde.se_extent"

pg_restore: creating FUNCTION "sde.st_extent_in("cstring")"

pg_restore: creating FUNCTION "sde.st_extent_out("sde"."se_extent")"

pg_restore: creating FUNCTION "sde.st_extent_recv("internal")"

pg_restore: creating FUNCTION "sde.st_extent_send("sde"."se_extent")"

pg_restore: creating TYPE "sde.se_extent"

pg_restore: creating FUNCTION "sde.st_envelope_in("cstring")"

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 4712; 1255 1141726 FUNCTION st_envelope_in("cstring") sde

pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate key value violates unique constraint "pg_type_oid_index"

DETAIL:  Key (oid)=(1142573) already exists.

    Command was: CREATE FUNCTION "sde"."st_envelope_in"("cstring") RETURNS "sde"."st_envelope"

    LANGUAGE "c" IMMUTABLE STRICT

    AS 'st_g...

command: "D:\Program Files\PostgreSQL\9.5\bin/pg_restore" --port 50432 --username ^"postgres^" --exit-on-error --verbose --dbname ^"dbname^=postgis^" "pg_upgrade_dump_16393.custom" >> "pg_upgrade_dump_16393.log" 2>&1

It says it’s trying to create function sde.st_envelope_in a 2nd time.

It seems to me that while creating TYPE sde.se_coord (or is it SHELL TYPE sde.se_extent?) this function was created.

Right afterwards, when creating TYPE sde.se_extent, the same function is also being created, causing the duplicate error…

At least that’s my limited understanding…

Can anyone please take a look? I can send any file from the pg_upgrade process.

Many thanks,

Duarte

------------------------------------------full outputs-------------------------------------------------

Pg_upgrade output:

…

C:\work\upgrade_postgresql\testes>"D:\Program Files\PostgreSQL\9.5\bin\pg_upgrad

e" -U postgres --jobs=6 -d "D:\Program Files\PostgreSQL\9.3\data" -b "D:\Program

Files\PostgreSQL\9.3\bin" -D "D:\Program Files\PostgreSQL\9.5\data" -B "D:\Prog

ram Files\PostgreSQL\9.5\bin"

pg_upgrade: could not start process for command ""D:\Program Files\PostgreSQL\9.

5\bin\pg_upgrade"  -U postgres --jobs=6 -d "D:\Program Files\PostgreSQL\9.3\data

" -b "D:\Program Files\PostgreSQL\9.3\bin" -D "D:\Program Files\PostgreSQL\9.5\d

ata" -B "D:\Program Files\PostgreSQL\9.5\bin"": error code 193

pg_upgrade: could not re-execute with restricted token: error code 3

Performing Consistency Checks

-----------------------------

Checking cluster versions                                   ok

Checking database user is the install user                  ok

Checking database connection settings                       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 invalid "line" user columns                    ok

Creating dump of global objects                             ok

Creating dump of database schemas

                                                            ok

Checking for presence of required libraries                 ok

Checking database user is the install user                  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 and epoch for new cluster       ok

Deleting files from new pg_multixact/offsets                ok

Copying old pg_multixact/offsets to new server              ok

Deleting files from new pg_multixact/members                ok

Copying old pg_multixact/members to new server              ok

Setting next multixact ID and offset for new cluster        ok

Resetting WAL archives                                      ok

Setting frozenxid and minmxid counters in new cluster       ok

Restoring global objects in the new cluster                 ok

Restoring database schemas in the new cluster

  template1

*failure*

Consult the last few lines of "pg_upgrade_dump_16393.log" for

the probable cause of the failure.

Failure, exiting

+++++++++++++++++++++++++++++++++++++++++++++

Log contents:

command: "D:\Program Files\PostgreSQL\9.5\bin/pg_dump" --port 50432 --username ^"postgres^" --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_16393.custom" ^"dbname^=postgis^" >> "pg_upgrade_dump_16393.log" 2>&1

pg_restore: connecting to database for restore

pg_restore: creating pg_largeobject "pg_largeobject"

pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"

pg_restore: creating SCHEMA "agdalentejo"

pg_restore: creating COMMENT "SCHEMA "agdalentejo""

pg_restore: creating SCHEMA "agdsandre"

pg_restore: creating COMMENT "SCHEMA "agdsandre""

pg_restore: creating SCHEMA "ags_rw"

pg_restore: creating SCHEMA "algas"

pg_restore: creating SCHEMA "always"

pg_restore: creating SCHEMA "ciefma"

pg_restore: creating SCHEMA "dc"

pg_restore: creating SCHEMA "dcom"

pg_restore: creating SCHEMA "dgaf"

pg_restore: creating SCHEMA "diap"

pg_restore: creating COMMENT "SCHEMA "diap""

pg_restore: creating SCHEMA "dpci"

pg_restore: creating SCHEMA "dpea"

pg_restore: creating COMMENT "SCHEMA "dpea""

pg_restore: creating SCHEMA "dsi"

pg_restore: creating SCHEMA "edia"

pg_restore: creating SCHEMA "gdbman"

pg_restore: creating COMMENT "SCHEMA "gdbman""

pg_restore: creating SCHEMA "glc"

pg_restore: creating SCHEMA "navia"

pg_restore: creating SCHEMA "portaldados"

pg_restore: creating SCHEMA "public"

pg_restore: creating COMMENT "SCHEMA "public""

pg_restore: creating SCHEMA "qgis"

pg_restore: creating SCHEMA "regante"

pg_restore: creating SCHEMA "sde"

pg_restore: creating SCHEMA "sgp"

pg_restore: creating COMMENT "SCHEMA "sgp""

pg_restore: creating SCHEMA "sice"

pg_restore: creating SCHEMA "sig_owner"

pg_restore: creating SCHEMA "sig_owner2"

pg_restore: creating SCHEMA "simarsul"

pg_restore: creating SCHEMA "sisap"

pg_restore: creating SCHEMA "smg"

pg_restore: creating COMMENT "SCHEMA "smg""

pg_restore: creating SCHEMA "telegestao"

pg_restore: creating SCHEMA "tiger"

pg_restore: creating SCHEMA "tiger_data"

pg_restore: creating SCHEMA "topology"

pg_restore: creating EXTENSION "address_standardizer"

pg_restore: creating COMMENT "EXTENSION "address_standardizer""

pg_restore: creating EXTENSION "file_fdw"

pg_restore: creating COMMENT "EXTENSION "file_fdw""

pg_restore: creating EXTENSION "fuzzystrmatch"

pg_restore: creating COMMENT "EXTENSION "fuzzystrmatch""

pg_restore: creating EXTENSION "ogr_fdw"

pg_restore: creating COMMENT "EXTENSION "ogr_fdw""

pg_restore: creating EXTENSION "postgis"

pg_restore: creating COMMENT "EXTENSION "postgis""

pg_restore: creating EXTENSION "postgis_tiger_geocoder"

pg_restore: creating COMMENT "EXTENSION "postgis_tiger_geocoder""

pg_restore: creating EXTENSION "postgis_topology"

pg_restore: creating COMMENT "EXTENSION "postgis_topology""

pg_restore: creating EXTENSION "postgres_fdw"

pg_restore: creating COMMENT "EXTENSION "postgres_fdw""

pg_restore: creating EXTENSION "tablefunc"

pg_restore: creating COMMENT "EXTENSION "tablefunc""

pg_restore: creating TYPE "public.addbandarg"

pg_restore: creating COMMENT "public.TYPE "addbandarg""

pg_restore: creating TYPE "public.agg_count"

pg_restore: creating SHELL TYPE "public.raster"

pg_restore: creating FUNCTION "public.raster_in("cstring")"

pg_restore: creating FUNCTION "public.raster_out("public"."raster")"

pg_restore: creating TYPE "public.raster"

pg_restore: creating COMMENT "public.TYPE "raster""

pg_restore: creating TYPE "public.agg_samealignment"

pg_restore: creating SHELL TYPE "public.box2d"

pg_restore: creating FUNCTION "public.box2d_in("cstring")"

pg_restore: creating FUNCTION "public.box2d_out("public"."box2d")"

pg_restore: creating TYPE "public.box2d"

pg_restore: creating COMMENT "public.TYPE "box2d""

pg_restore: creating SHELL TYPE "public.box2df"

pg_restore: creating FUNCTION "public.box2df_in("cstring")"

pg_restore: creating FUNCTION "public.box2df_out("public"."box2df")"

pg_restore: creating TYPE "public.box2df"

pg_restore: creating SHELL TYPE "public.box3d"

pg_restore: creating FUNCTION "public.box3d_in("cstring")"

pg_restore: creating FUNCTION "public.box3d_out("public"."box3d")"

pg_restore: creating TYPE "public.box3d"

pg_restore: creating COMMENT "public.TYPE "box3d""

pg_restore: creating SHELL TYPE "public.geography"

pg_restore: creating FUNCTION "public.geography_analyze("internal")"

pg_restore: creating FUNCTION "public.geography_in("cstring", "oid", integer)"

pg_restore: creating FUNCTION "public.geography_out("public"."geography")"

pg_restore: creating FUNCTION "public.geography_recv("internal", "oid", integer)"

pg_restore: creating FUNCTION "public.geography_send("public"."geography")"

pg_restore: creating FUNCTION "public.geography_typmod_in("cstring"[])"

pg_restore: creating FUNCTION "public.geography_typmod_out(integer)"

pg_restore: creating TYPE "public.geography"

pg_restore: creating COMMENT "public.TYPE "geography""

pg_restore: creating SHELL TYPE "public.geometry"

pg_restore: creating FUNCTION "public.geometry_analyze("internal")"

pg_restore: creating FUNCTION "public.geometry_in("cstring")"

pg_restore: creating FUNCTION "public.geometry_out("public"."geometry")"

pg_restore: creating FUNCTION "public.geometry_recv("internal")"

pg_restore: creating FUNCTION "public.geometry_send("public"."geometry")"

pg_restore: creating FUNCTION "public.geometry_typmod_in("cstring"[])"

pg_restore: creating FUNCTION "public.geometry_typmod_out(integer)"

pg_restore: creating TYPE "public.geometry"

pg_restore: creating COMMENT "public.TYPE "geometry""

pg_restore: creating TYPE "public.geometry_dump"

pg_restore: creating COMMENT "public.TYPE "geometry_dump""

pg_restore: creating TYPE "public.geomval"

pg_restore: creating COMMENT "public.TYPE "geomval""

pg_restore: creating SHELL TYPE "public.gidx"

pg_restore: creating FUNCTION "public.gidx_in("cstring")"

pg_restore: creating FUNCTION "public.gidx_out("public"."gidx")"

pg_restore: creating TYPE "public.gidx"

pg_restore: creating SHELL TYPE "public.pgis_abs"

pg_restore: creating FUNCTION "public.pgis_abs_in("cstring")"

pg_restore: creating FUNCTION "public.pgis_abs_out("public"."pgis_abs")"

pg_restore: creating TYPE "public.pgis_abs"

pg_restore: creating TYPE "public.rastbandarg"

pg_restore: creating COMMENT "public.TYPE "rastbandarg""

pg_restore: creating TYPE "public.reclassarg"

pg_restore: creating COMMENT "public.TYPE "reclassarg""

pg_restore: creating SHELL TYPE "public.spheroid"

pg_restore: creating FUNCTION "public.spheroid_in("cstring")"

pg_restore: creating FUNCTION "public.spheroid_out("public"."spheroid")"

pg_restore: creating TYPE "public.spheroid"

pg_restore: creating TYPE "public.summarystats"

pg_restore: creating COMMENT "public.TYPE "summarystats""

pg_restore: creating TYPE "public.tablefunc_crosstab_2"

pg_restore: creating TYPE "public.tablefunc_crosstab_3"

pg_restore: creating TYPE "public.tablefunc_crosstab_4"

pg_restore: creating TYPE "public.unionarg"

pg_restore: creating COMMENT "public.TYPE "unionarg""

pg_restore: creating TYPE "public.valid_detail"

pg_restore: creating SHELL TYPE "sde.se_coord"

pg_restore: creating FUNCTION "sde.st_coord_in("cstring")"

pg_restore: creating FUNCTION "sde.st_coord_out("sde"."se_coord")"

pg_restore: creating FUNCTION "sde.st_coord_recv("internal")"

pg_restore: creating FUNCTION "sde.st_coord_send("sde"."se_coord")"

pg_restore: creating TYPE "sde.se_coord"

pg_restore: creating SHELL TYPE "sde.se_extent"

pg_restore: creating FUNCTION "sde.st_extent_in("cstring")"

pg_restore: creating FUNCTION "sde.st_extent_out("sde"."se_extent")"

pg_restore: creating FUNCTION "sde.st_extent_recv("internal")"

pg_restore: creating FUNCTION "sde.st_extent_send("sde"."se_extent")"

pg_restore: creating TYPE "sde.se_extent"

pg_restore: creating FUNCTION "sde.st_envelope_in("cstring")"

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 4712; 1255 1141726 FUNCTION st_envelope_in("cstring") sde

pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate key value violates unique constraint "pg_type_oid_index"

DETAIL:  Key (oid)=(1142573) already exists.

    Command was: CREATE FUNCTION "sde"."st_envelope_in"("cstring") RETURNS "sde"."st_envelope"

    LANGUAGE "c" IMMUTABLE STRICT

    AS 'st_g...

command: "D:\Program Files\PostgreSQL\9.5\bin/pg_restore" --port 50432 --username ^"postgres^" --exit-on-error --verbose --dbname ^"dbname^=postgis^" "pg_upgrade_dump_16393.custom" >> "pg_upgrade_dump_16393.log" 2>&1