Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEwDC-00049U-Fd for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jul 2022 17:13:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oEwDB-00065e-E2 for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jul 2022 17:13:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEwDB-00064s-3A for pgsql-admin@lists.postgresql.org; Fri, 22 Jul 2022 17:13:13 +0000 Received: from mout.perfora.net ([74.208.4.197]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEwD4-0003H5-Lg for pgsql-admin@postgresql.org; Fri, 22 Jul 2022 17:13:12 +0000 Received: from oxusgaltgw05.schlund.de ([10.72.72.51]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1MILw8-1oKSWQ0ihQ-00EJu1; Fri, 22 Jul 2022 19:13:02 +0200 Date: Fri, 22 Jul 2022 13:13:01 -0400 (EDT) From: MichaelDBA Vitale To: "David G. Johnston" , Murthy Nunna Cc: "pgsql-admin@postgresql.org" Message-ID: <1907988893.588756.1658509981368@email.ionos.com> In-Reply-To: References: Subject: Re: pg_upgrade failure upgrading from v10.8 to v14.4 MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Mailer: Open-Xchange Mailer v7.10.6-Rev18 X-Originating-Client: open-xchange-appsuite X-Provags-ID: V03:K1:dLUBbrcXGcdaCGZtxBZAT7qPa3gmZjLSmkEbfEbz+wqDmqtF4zN HtNa4l2Qa52ULhLLnd4NuGFtE2FbsGCBmy8EZK8Kx2Z+VxEuh2XQFi79C1CkpNT48XFia0y KZoiBPj1EyEdSLSrj3QxN+OAOisrwmJYLzjZGal4caCWwe85CjO+96348jwi0bPuKJXdPGc srUHVH117t6GWqoCAZ7RA== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:Apd7nxfLVYs=:vD1mGVV1yVKlyWaL08Tdw+ NK/P3+Gqe6Cccv2XpjyPMHM5ZjZw/cdkjo0eZLSAlNMX3+7bfbfRuJnCAYgzlKxVh9WqoCR/W YpZHkuxBWbPZk6n15zLBbUCTEQ42saKbWxSb27XWzZvS1jEQz15ztVTXeKQH3rh6aCV178Cj4 eHgJbIaC8xPyF8jR1KDTfK2lNTQUeKtdUHtK9Peq8h3nXitVKHfOKFpgPr/ifYv/aTC5/LHx4 MArtQzjdzitejZtZqS6LaBovGgOoMKAJMLIsYt1+RNqOMVt9RDks6qtTTwrKF2FEeYf0JS+aH frlp/L8aWHjMOw0SbxODuakrlf3K9243ukE7nFtKI9bEXkH3mvAaUXGLGWzTG/6QPRO/zm8fe MBGR5vzwazSOD+qwRv4+MNjck5U9UOR3SRnGW6rVSpO92j9/IC3uPtCW097Bevg1ZYOQZFAs5 l/Xqrjtl/N9G6x6zGZYH14wy301MIpBZXoZnl7fk/1V18hjjDfr6siy39yElhoCGT8mJlZ5fD jLVXEi2QJvokt9lmD1/FApSlJ3GXC2FvfEfo3OKn6PqpNSfV5UQwhzYtHxLFyQx5b4rgd5fIr QI8pPTp8gVlSUvZPuP5IuFkbgy1Pk2m7o6edK9Odx+0UHOTGtqzrvDT/2mzv522VXT+JP0Ywj P0Mbp0uyWLkO/dAqhyjD2VIiMQU7TUy3UGj9hP9k1cmkgNNoAWSRVYwzJ2JaeBMwff6KG2IJB alZxNAYDgCFXcvE0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk

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> 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.