Thread: pg_upgrade failure upgrading from v10.8 to v14.4

Folks,

I am testing upgrade of my cluster from version 10.8 to 14.4

I encountered following error. Any help or insight is much appreciated.

Thanks.

*failure*

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

the probable cause of the failure.

Last few lines of pg_upgrade_dump_16400.log:

pg_restore: error: could not execute query: ERROR:  column c.relhaspkey does not exist

LINE 19:     "c"."relhaspkey" AS "haspkey",

             ^

Command was:

-- For binary upgrade, must preserve pg_type oid

SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('16681'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid

SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16680'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids

SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16679'::pg_catalog.oid);

CREATE VIEW "public"."all_tables" AS

SELECT ((("n"."nspname")::"text" || '.'::"text") || ("c"."relname")::"text") AS "tablename",

I don’t think I am failing in a user created table. Looks like the failed view is one of the catalog views. Looks like I have to go to an intermediate version before getting to 14.4.

Looks like it is expecting relhaspkey column in pg_class view of new version.  Sorry my “create view” statement is truncated in my original message. Full statement is as below

Error is:

pg_restore: error: could not execute query: ERROR:  column c.relhaspkey does not exist

LINE 19:     "c"."relhaspkey" AS "haspkey",

CREATE VIEW "public"."all_tables" AS

SELECT ((("n"."nspname")::"text" || '.'::"text") || ("c"."relname")::"text") AS "tablename",

    "pg_get_userbyid"("c"."relowner") AS "tableowner",

    "c"."reltuples" AS "rows",

    "c"."relpages" AS "pages",

    "t"."spcname" AS "tablespace",

    "c"."relhaspkey" AS "haspkey",

    "c"."relhasindex" AS "hasindexes",

    "c"."relhasrules" AS "hasrules"

   FROM (("pg_class" "c"

     LEFT JOIN "pg_namespace" "n" ON (("n"."oid" = "c"."relnamespace")))

     LEFT JOIN "pg_tablespace" "t" ON (("t"."oid" = "c"."reltablespace")))

  WHERE (("c"."relkind" = 'r'::"char") AND ("n"."nspname" !~ 'pg_'::"text"));

On 07/22/2022 12:30 PM EDT David G. Johnston <david.g.johnston@gmail.com> wrote:

 

 

On Fri, Jul 22, 2022 at 9:19 AM Murthy Nunna <mnunna@fnal.gov> wrote:

 

I encountered following error. Any help or insight is much appreciated.

 

CREATE VIEW "public"."all_tables" AS

SELECT ((("n"."nspname")::"text" || '.'::"text") || ("c"."relname")::"text") AS "tablename",

 

 

You created a view that uses the catalogs and the structure of those catalogs has changed.  pg_upgrade cannot fix this for you.  Unfortunately, the only real solution is to have a pre-upgrade script that removes the not unusable view, perform the upgrade, and then run a post-upgrade script that replaces it with something that will work in the new system.  I'm unaware of a version of PostgreSQL that would act as a transition version where both versions of the view could exist, but you may wish to double-check that if you think such a two-step upgrade path would be easier for you to manage.

 

David J.

 

David and Michael,

Thank you both very much for your timely help!

On 07/22/2022 1:26 PM EDT David G. Johnston <david.g.johnston@gmail.com> wrote:

 

 

On Fri, Jul 22, 2022 at 10:23 AM Murthy Nunna <mnunna@fnal.gov> wrote:

I don’t think I am failing in a user created table. Looks like the failed view is one of the catalog views. Looks like I have to go to an intermediate version before getting to 14.4.

 

CREATE VIEW "public"."all_tables" AS

 

The system doesn't create stuff in public.  That is user-space.

 

David J.