pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_upgrade failure upgrading from v10.8 to v14.4
7+ messages / 3 participants
[nested] [flat]
* pg_upgrade failure upgrading from v10.8 to v14.4
@ 2022-07-22 16:19 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 16:30 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Murthy Nunna @ 2022-07-22 16:19 UTC (permalink / raw)
To: pgsql-admin
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",
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_upgrade failure upgrading from v10.8 to v14.4
2022-07-22 16:19 pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
@ 2022-07-22 16:30 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:13 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 MichaelDBA Vitale <michaeldba@sqlexec.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-07-22 16:30 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_upgrade failure upgrading from v10.8 to v14.4
2022-07-22 16:19 pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 16:30 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
@ 2022-07-22 17:13 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-22 17:23 ` RE: pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
0 siblings, 1 reply; 7+ messages in thread
From: MichaelDBA Vitale @ 2022-07-22 17:13 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
Pretty self explanatory...
</div>
<div class="default-style">
</div>
<div class="default-style">
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.
</div>
<div class="default-style">
</div>
<div class="default-style">
Regards,
</div>
<div class="default-style">
Michael Vitale
</div>
<div class="default-style">
</div>
<blockquote type="cite">
<div>
On 07/22/2022 12:30 PM EDT David G. Johnston <david.g.johnston@gmail.com> wrote:
</div>
<div>
</div>
<div>
</div>
<div dir="ltr">
<div dir="ltr">
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
<span style="font-family: Arial,Helvetica,sans-serif;">On Fri, Jul 22, 2022 at 9:19 AM Murthy Nunna <<a href="mailto:mnunna@fnal.gov">mnunna@fnal.gov</a>> wrote:</span>
</div>
</div>
<div class="gmail_quote">
<blockquote>
<div style="overflow-wrap: break-word;">
<div class="gmail-m_165588171246110865WordSection1">
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="font-size: 12pt;">I encountered following error. Any help or insight is much appreciated.<u></u><u></u></span></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="font-size: 12pt;">CREATE VIEW "public"."all_tables" AS<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size: 12pt;">SELECT ((("n"."nspname")::"text" || '.'::"text") || ("c"."relname")::"text") AS "tablename",<u></u><u></u></span></p>
<p class="MsoNormal"> </p>
</div>
</div>
</blockquote>
<div>
</div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
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.
</div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
</div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
David J.
</div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
</div>
</div>
</div>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: pg_upgrade failure upgrading from v10.8 to v14.4
2022-07-22 16:19 pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 16:30 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:13 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 MichaelDBA Vitale <michaeldba@sqlexec.com>
@ 2022-07-22 17:23 ` Murthy Nunna <mnunna@fnal.gov>
2022-07-22 17:26 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Murthy Nunna @ 2022-07-22 17:23 UTC (permalink / raw)
To: pgsql-admin
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@sqlexec.com>
Sent: Friday, July 22, 2022 12:13 PM
To: David G. Johnston <david.g.johnston@gmail.com>; Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org
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@gmail.com<mailto:david.g.johnston@gmail.com>> wrote:
On Fri, Jul 22, 2022 at 9:19 AM Murthy Nunna <mnunna@fnal.gov<mailto: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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_upgrade failure upgrading from v10.8 to v14.4
2022-07-22 16:19 pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 16:30 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:13 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-22 17:23 ` RE: pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
@ 2022-07-22 17:26 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:43 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 MichaelDBA Vitale <michaeldba@sqlexec.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-07-22 17:26 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_upgrade failure upgrading from v10.8 to v14.4
2022-07-22 16:19 pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 16:30 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:13 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-22 17:23 ` RE: pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 17:26 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
@ 2022-07-22 17:43 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-27 11:48 ` RE: pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
0 siblings, 1 reply; 7+ messages in thread
From: MichaelDBA Vitale @ 2022-07-22 17:43 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
Hmmm, catalog views are in the pg_catalog schema, not the public schema.
</div>
<div class="default-style">
</div>
<div class="default-style">
Only extensions or user-defined stuff would be created in the public schema.
</div>
<blockquote type="cite">
<div>
On 07/22/2022 1:26 PM EDT David G. Johnston <david.g.johnston@gmail.com> wrote:
</div>
<div>
</div>
<div>
</div>
<div dir="ltr">
<div dir="ltr">
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
<span style="font-family: Arial,Helvetica,sans-serif;">On Fri, Jul 22, 2022 at 10:23 AM Murthy Nunna <<a href="mailto:mnunna@fnal.gov">mnunna@fnal.gov</a>> wrote:</span>
</div>
</div>
<div class="gmail_quote">
<blockquote>
<div style="overflow-wrap: break-word;">
<div class="gmail-m_-5348718172304268188WordSection1">
<p class="MsoNormal"><span style="font-size: 12pt;">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size: 12pt;"> </span></p>
<p class="MsoNormal"><span style="font-size: 12pt;">CREATE VIEW "public"."all_tables" AS</span></p>
</div>
</div>
</blockquote>
<div>
</div>
<div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
The system doesn't create stuff in public. That is user-space.
</div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
</div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
David J.
</div>
<div class="gmail_default" style="font-family: arial,helvetica,sans-serif;">
</div>
</div>
</div>
</div>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: pg_upgrade failure upgrading from v10.8 to v14.4
2022-07-22 16:19 pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 16:30 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:13 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-22 17:23 ` RE: pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 17:26 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:43 ` Re: pg_upgrade failure upgrading from v10.8 to v14.4 MichaelDBA Vitale <michaeldba@sqlexec.com>
@ 2022-07-27 11:48 ` Murthy Nunna <mnunna@fnal.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Murthy Nunna @ 2022-07-27 11:48 UTC (permalink / raw)
To: MichaelDBA Vitale <michaeldba@sqlexec.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
David and Michael,
Thank you both very much for your timely help!
From: MichaelDBA Vitale <michaeldba@sqlexec.com>
Sent: Friday, July 22, 2022 12:44 PM
To: David G. Johnston <david.g.johnston@gmail.com>; Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_upgrade failure upgrading from v10.8 to v14.4
Hmmm, catalog views are in the pg_catalog schema, not the public schema.
Only extensions or user-defined stuff would be created in the public schema.
On 07/22/2022 1:26 PM EDT David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>> wrote:
On Fri, Jul 22, 2022 at 10:23 AM Murthy Nunna <mnunna@fnal.gov<mailto: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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-07-27 11:48 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-22 16:19 pg_upgrade failure upgrading from v10.8 to v14.4 Murthy Nunna <mnunna@fnal.gov>
2022-07-22 16:30 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:13 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-22 17:23 ` Murthy Nunna <mnunna@fnal.gov>
2022-07-22 17:26 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-22 17:43 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-07-27 11:48 ` Murthy Nunna <mnunna@fnal.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox