PG_UPGRADE FAILED FROM 9.5 to 11*

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PG_UPGRADE FAILED FROM 9.5 to 11*

Jaspreet Singh

Hi Team

We are upgrading our 9.5 postgres database to 11* version and it failed with below error . please help .

 

/usr/pgsql-11/bin/pg_upgrade  -b /usr/pgsql-9.5/bin -B /usr/pgsql-11/bin -d /pgdata/data -D /var/lib/pgsql/11/data/

 

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 185; 1259 1183792 VIEW pg_stat_activity postgres

pg_restore: [archiver (db)] could not execute query: ERROR:  column pg_stat_activity.waiting does not exist

LINE 27:     "pg_stat_activity"."waiting",

             ^

    Command was:

-- For binary upgrade, must preserve pg_type oid

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

 

 

-- For binary upgrade, must preserve pg_type array oid

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

 

 

-- For binary upgrade, must preserve pg_class oids

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

Thanks & Regards

Jaspreet Singh

Reply | Threaded
Open this post in threaded view
|

Re: PG_UPGRADE FAILED FROM 9.5 to 11*

Bruce Momjian
On Thu, Jun 18, 2020 at 06:42:11PM +0000, Jaspreet Singh wrote:

> Hi Team
>
> We are upgrading our 9.5 postgres database to 11* version and it failed with
> below error . please help .
>
>  
>
> /usr/pgsql-11/bin/pg_upgrade  -b /usr/pgsql-9.5/bin -B /usr/pgsql-11/bin -d /
> pgdata/data -D /var/lib/pgsql/11/data/
>
>  
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>
> pg_restore: [archiver (db)] Error from TOC entry 185; 1259 1183792 VIEW
> pg_stat_activity postgres
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  column
> pg_stat_activity.waiting does not exist
>
> LINE 27:     "pg_stat_activity"."waiting",
>
>              ^
>
>     Command was:

You didn't show us the command that was causing the error.  I am
thinking it might be a system view or function that references a renamed
system column.  I suggest dropping the view/function in the old cluster,
running pg_upgrade, then recreating the view with the new column name.
Also, check the release notes for renamed columns.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee



Reply | Threaded
Open this post in threaded view
|

Re: PG_UPGRADE FAILED FROM 9.5 to 11*

Tom Lane-2
Bruce Momjian <[hidden email]> writes:
> On Thu, Jun 18, 2020 at 06:42:11PM +0000, Jaspreet Singh wrote:
>> We are upgrading our 9.5 postgres database to 11* version and it failed with
>> below error . please help .
>>
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 185; 1259 1183792 VIEW
>> pg_stat_activity postgres
>> LINE 27:     "pg_stat_activity"."waiting",

> You didn't show us the command that was causing the error.  I am
> thinking it might be a system view or function that references a renamed
> system column.

Well, we can see that the problematic view is named "pg_stat_activity",
but why would pg_dump have dumped a system view?  I am thinking that the
source database contains a duplicate (and now obsolete) copy of the
pg_stat_activity view.  Probably just getting rid of that would do the
trick.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

RE: PG_UPGRADE FAILED FROM 9.5 to 11*

Jaspreet Singh
Thanks Tom
I have dropped the view and upgrade successful
Thanks for your quick response

-Jas

-----Original Message-----
From: Tom Lane <[hidden email]>
Sent: Thursday, June 18, 2020 2:16 PM
To: Bruce Momjian <[hidden email]>
Cc: Jaspreet Singh <[hidden email]>; [hidden email]
Subject: Re: PG_UPGRADE FAILED FROM 9.5 to 11*

Bruce Momjian <[hidden email]> writes:
> On Thu, Jun 18, 2020 at 06:42:11PM +0000, Jaspreet Singh wrote:
>> We are upgrading our 9.5 postgres database to 11* version and it
>> failed with below error . please help .
>>
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 185; 1259 1183792
>> VIEW pg_stat_activity postgres
>> LINE 27:     "pg_stat_activity"."waiting",

> You didn't show us the command that was causing the error.  I am
> thinking it might be a system view or function that references a
> renamed system column.

Well, we can see that the problematic view is named "pg_stat_activity", but why would pg_dump have dumped a system view?  I am thinking that the source database contains a duplicate (and now obsolete) copy of the pg_stat_activity view.  Probably just getting rid of that would do the trick.

                        regards, tom lane