PG_UPGRADE FAILED FROM 9.5 to 11*
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
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 postgrespg_restore: [archiver (db)] could not execute query: ERROR: column
pg_stat_activity.waiting does not existLINE 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 <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Bruce Momjian <bruce@momjian.us> 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
Thanks Tom
I have dropped the view and upgrade successful
Thanks for your quick response
-Jas
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, June 18, 2020 2:16 PM
To: Bruce Momjian <bruce@momjian.us>
Cc: Jaspreet Singh <jaspresingh@tesla.com>; pgsql-bugs@postgresql.org
Subject: Re: PG_UPGRADE FAILED FROM 9.5 to 11*
Bruce Momjian <bruce@momjian.us> 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