Re: pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

On 01/08/2016 03:45 AM, Karsten Hilbert wrote:
> Hi,
>
> I have attempted a pg_upgrade on Debian using the Debian
> wrapper scripts like so:
>
>     pg_upgradecluster -v 9.5 9.4 main
>
> (meaning to upgrade a cluster named "main" from 9.4 to 9.5)
>
> which resulted in this:
>
>     -----------------------------------------------------------------
>       pg_upgrade run on Fri Jan  8 11:47:32 2016
>     -----------------------------------------------------------------
>
>     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
>     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
>
>     *failure*
>     Consult the last few lines of "pg_upgrade_dump_512600.log" for
>     the probable cause of the failure.
>
>     -----------------------------------------------------------------
>
> The pg_upgrade_dump_512600.log shows:
>
>     command: "/usr/lib/postgresql/9.5/bin/pg_dump" --host "/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n"
--port5432 --username "postgres" --schema-only --quote-all-identifiers --binary-upgrade --format=custom
--file="pg_upgrade_dump_512600.custom""gnumed_v20" >> "pg_upgrade_dump_512600.log" 2>&1 
>
>     command: "/usr/lib/postgresql/9.5/bin/pg_restore" --host
"/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n"--port 5433 --username "postgres" --exit-on-error --verbose
--dbname"gnumed_v20" "pg_upgrade_dump_512600.custom" >> "pg_upgrade_dump_512600.log" 2>&1 
>
>     pg_restore: verbinde mit der Datenbank zur Wiederherstellung
>     pg_restore: erstelle pg_largeobject „pg_largeobject“
>     pg_restore: erstelle pg_largeobject_metadata „pg_largeobject_metadata“
>     pg_restore: erstelle SCHEMA „au“
>     pg_restore: erstelle SCHEMA „audit“
>     pg_restore: erstelle SCHEMA „bill“
>     pg_restore: erstelle COMMENT „SCHEMA "bill"“
>     pg_restore: erstelle SCHEMA „blobs“
>     pg_restore: erstelle SCHEMA „cfg“
>     pg_restore: erstelle COMMENT „SCHEMA "cfg"“
>     pg_restore: erstelle SCHEMA „clin“
>     pg_restore: erstelle SCHEMA „de_de“
>     pg_restore: erstelle SCHEMA „dem“
>     pg_restore: erstelle SCHEMA „gm“
>     pg_restore: erstelle SCHEMA „i18n“
>     pg_restore: erstelle SCHEMA „public“
>     pg_restore: erstelle COMMENT „SCHEMA "public"“
>     pg_restore: erstelle SCHEMA „ref“
>     pg_restore: erstelle COMMENT „SCHEMA "ref"“
>     pg_restore: erstelle SCHEMA „staging“
>     pg_restore: erstelle COMMENT „SCHEMA "staging"“
>     pg_restore: erstelle EXTENSION „pg_trgm“
>     pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
>     pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
>     pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
>     pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 1255 511230 FUNCTION gtrgm_in("cstring")
postgres
>     pg_restore: [Archivierer (DB)] could not execute query: ERROR:  pg_type OID value not set when in binary upgrade
mode
>         Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS "gtrgm"
>         LANGUAGE "c" IMMUTABLE STRICT
>         AS '$libdir/pg_trgm', 'gtrgm_in'...
>
>
> I do have pg_trgm installed in the 9.4 cluster for use with
> the gnumed_vXX databases.
>
> The relevant 9.5 Debian package containing pg_trgm for PG 9.5
> (postgresql-contrib-9.5) is installed.
>
> I am running this with a libpq compiled against PG 9.5.
>
> For one thing - does it seem odd that the function would be
> named "gtrgm_in" rather than "pgtrgm_in" ?
>
> Anything else that seems off from the information given above?
>
> What other information do I need to provide ?
>
> (For what it's worth, I have also tried the --method=dump way
> of using Debian's pg_upgradecluster which internally uses a
> dump/restore cycle rather than calling pg_upgrade. That
> failed due to ordering problems with table data vs table
> constraints.)

I thought --method=dump was the default, so this:

pg_upgradecluster -v 9.5 9.4 main

was using that?

At any rate:

http://www.postgresql.org/docs/9.5/interactive/pgupgrade.html

"If an error occurs while restoring the database schema, pg_upgrade will
exit and you will have to revert to the old cluster as outlined in step
16 below. To try pg_upgrade again, you will need to modify the old
cluster so the pg_upgrade schema restore succeeds. If the problem is a
contrib module, you might need to uninstall the contrib module from the
old cluster and install it in the new cluster after the upgrade,
assuming the module is not being used to store user data."


>
> Thanks a lot for any advice,
> Karsten
>


--
Adrian Klaver
adrian.klaver@aklaver.com