I have just encountered an apparent bug in pg_upgrade (or possibly pg_dump).
To recreate, install the cranges extension - which can be obtained via
"git clone https://bitbucket.org/adunstan/pg-closed-ranges.git" - for
both 9.4 and 9.5. Create a fresh 9.4 instance, create a database and in
it run "create extension cranges schema pg_catalog". Then create a fresh
9.5 instance and try to pg_upgrade from the 9.4 instance to the 9.5
instance. Here's the tail of the log:
pg_restore: creating SCHEMA "public" pg_restore: creating COMMENT "SCHEMA "public"" pg_restore: creating
EXTENSION"cranges" pg_restore: creating COMMENT "EXTENSION "cranges"" pg_restore: creating SHELL TYPE
"pg_catalog.cdaterange" pg_restore: creating FUNCTION "pg_catalog.cdaterange_canonical("cdaterange")" pg_restore:
creatingTYPE "pg_catalog.cdaterange" pg_restore: creating SHELL TYPE "pg_catalog.cint4range" pg_restore: creating
FUNCTION "pg_catalog.cint4range_canonical("cint4range")" pg_restore: creating TYPE "pg_catalog.cint4range"
pg_restore:creating SHELL TYPE "pg_catalog.cint8range" pg_restore: creating FUNCTION
"pg_catalog.cint8range_canonical("cint8range")" pg_restore: creating TYPE "pg_catalog.cint8range" pg_restore:
creatingFUNCTION "pg_catalog.cdaterange("date", "date")" pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore:[archiver (db)] Error from TOC entry 191; 1255 16389 FUNCTION cdaterange("date", "date") andrew
pg_restore:[archiver (db)] could not execute query: ERROR: function "cdaterange" already exists with same argument
types Command was: CREATE FUNCTION "cdaterange"("date", "date") RETURNS "cdaterange" LANGUAGE "internal"
IMMUTABLE AS $$range_construct...
The end result is that I currently can't upgrade a database using this
extension, which is rather ugly.
Similar things happen if I put the extension in public instead of
pg_catalog.
cheers
andrew