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 Duarte Carreira Diretor | Dep. Informa??o Geogr?fica e Cartografia www.edia.pt<http://www.edia.pt> www.alqueva.com.pt<http://www.alqueva.com.pt> Tel. +351 284315100 [http://www.edia.pt/edia/images/rodape.jpg]<http://www.edia.pt>
