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

[Murthy Nunna <mnunna@xxxxxxxx>]

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"));

 

 

 

 

 

 

 

 

 

From: MichaelDBA Vitale <michaeldba@xxxxxxxxxxx>
Sent: Friday, July 22, 2022 12:13 PM
To: David G. Johnston <david.g.johnston@xxxxxxxxx>; Murthy Nunna <mnunna@xxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: pg_upgrade failure upgrading from v10.8 to v14.4

 

Pretty self explanatory...

 

You have a view that references PG catalog tables whose structure has changed in the new version.  Capture the DDL for that view, drop the view, proceed with the upgrade and attempt to create that view in the target DB.  PG will give you good error msgs pointing you to what you need to change to get the query to work.

 

Regards,

Michael Vitale

 

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

 

 

On Fri, Jul 22, 2022 at 9:19 AM Murthy Nunna <mnunna@xxxxxxxx> 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.