From: MichaelDBA Vitale <michaeldba@sqlexec.com>
To: David G. Johnston <david.g.johnston@gmail.com>
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: pg_upgrade failure upgrading from v10.8 to v14.4
Date: Fri, 22 Jul 2022 13:13:01 -0400 (EDT)
Message-ID: <1907988893.588756.1658509981368@email.ionos.com> (raw)
In-Reply-To: <CAKFQuwYKaU7eOpO2Z++TuDZX4GzVYmbgPpm0wdoRmW_O=FyGwQ@mail.gmail.com>
References: <DM8PR09MB667740FCD23BCFECA20E8A3CB8909@DM8PR09MB6677.namprd09.prod.outlook.com>
<CAKFQuwYKaU7eOpO2Z++TuDZX4GzVYmbgPpm0wdoRmW_O=FyGwQ@mail.gmail.com>
<!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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: michaeldba@sqlexec.com, david.g.johnston@gmail.com, mnunna@fnal.gov
Subject: Re: pg_upgrade failure upgrading from v10.8 to v14.4
In-Reply-To: <1907988893.588756.1658509981368@email.ionos.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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