Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gPPJI-0000cU-Bx for pgsql-admin@arkaria.postgresql.org; Wed, 21 Nov 2018 10:00:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gPPJG-0005mI-LL for pgsql-admin@arkaria.postgresql.org; Wed, 21 Nov 2018 10:00:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gPPJG-0005mB-Dg for pgsql-admin@lists.postgresql.org; Wed, 21 Nov 2018 10:00:38 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gPPJ9-0005W8-LK for pgsql-admin@lists.postgresql.org; Wed, 21 Nov 2018 10:00:38 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.14.3/8.14.3/Debian-9.4) with ESMTP id wALA0OBE019202 for ; Wed, 21 Nov 2018 12:00:29 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id wALA0NED071086 for ; Wed, 21 Nov 2018 12:00:24 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: Error while upgrading from 9.5 to 10 To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios Message-ID: <8cf77b4d-64af-a986-1819-767e71554b0a@matrix.gatewaynet.com> Date: Wed, 21 Nov 2018 12:00:23 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101 Thunderbird/60.0.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------C50B320C36E76B92CB9FA7EC" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.98.7 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-27.0 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_NAUTICAL,DYN_HAM_SIGNAT1,HTML_MESSAGE autolearn=unavailable version=3.3.1 X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------C50B320C36E76B92CB9FA7EC Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 21/11/18 6:59 π.μ., Srinivas Reddy wrote: > Hi > > Trying to upgrade major vesion from 9.5 to 10 using pg_ugrade, but getting below error, > > pg_restore: connecting to database for restore > pg_restore: creating pg_largeobject "pg_largeobject" > pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata" > pg_restore: creating COMMENT "DATABASE "postgres"" > pg_restore: creating SCHEMA "postgres_exporter" > pg_restore: creating SCHEMA "public" > pg_restore: creating COMMENT "SCHEMA "public"" > pg_restore: creating VIEW "postgres_exporter.pg_stat_activity" What is this schema : postgres_exporter ? I'd suggest taking a back up of the objects in this schema, den DROP everything in there and retry. > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 182; 1259 17640 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('17642'::pg_catalog.oid); > > > -- For binary upgrade, must preserve pg_type array oid > SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('17641'::pg_catalog.oid); > > > -- For binary upgrade, must preserve pg_class oids > SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('17640'::pg_catalog.oid); > > CREATE VIEW "postgres_exporter"."pg_stat_activity" AS >  SELECT "pg_stat_activity"."datid", >     "pg_stat_activity"."datname", >     "pg_stat_activity"."pid", >     "pg_stat_activity"."usesysid", >     "pg_stat_activity"."usename", >     "pg_stat_activity"."application_name", >     "pg_stat_activity"."client_addr", >     "pg_stat_activity"."client_hostname", >     "pg_stat_activity"."client_port", >     "pg_stat_activity"."backend_start", >     "pg_stat_activity"."xact_start", >     "pg_stat_activity"."query_start", >     "pg_stat_activity"."state_change", >     "pg_stat_activity"."waiting", >     "pg_stat_activity"."state", >     "pg_stat_activity"."backend_xid", >     "pg_stat_activity"."backend_xmin", >     "pg_stat_activity"."query" >    FROM "pg_stat_activity"; > > how to resolve this issue? > > -- > _THANKS AND REGARDS_ > /SRINIVAS REDDY G A/ -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------C50B320C36E76B92CB9FA7EC Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 21/11/18 6:59 π.μ., Srinivas Reddy wrote:
Hi

Trying to upgrade major vesion from 9.5 to 10 using pg_ugrade, but getting below error,

pg_restore: connecting to database for restore
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
pg_restore: creating COMMENT "DATABASE "postgres""
pg_restore: creating SCHEMA "postgres_exporter"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating VIEW "postgres_exporter.pg_stat_activity"

What is this schema : postgres_exporter ?
I'd suggest taking a back up of the objects in this schema, den DROP everything in there and retry.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 182; 1259 17640 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('17642'::pg_catalog.oid);
 

-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('17641'::pg_catalog.oid);
 

-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('17640'::pg_catalog.oid);
 
CREATE VIEW "postgres_exporter"."pg_stat_activity" AS
 SELECT "pg_stat_activity"."datid",
    "pg_stat_activity"."datname",
    "pg_stat_activity"."pid",
    "pg_stat_activity"."usesysid",
    "pg_stat_activity"."usename",
    "pg_stat_activity"."application_name",
    "pg_stat_activity"."client_addr",
    "pg_stat_activity"."client_hostname",
    "pg_stat_activity"."client_port",
    "pg_stat_activity"."backend_start",
    "pg_stat_activity"."xact_start",
    "pg_stat_activity"."query_start",
    "pg_stat_activity"."state_change",
    "pg_stat_activity"."waiting",
    "pg_stat_activity"."state",
    "pg_stat_activity"."backend_xid",
    "pg_stat_activity"."backend_xmin",
    "pg_stat_activity"."query"
   FROM "pg_stat_activity";

how to resolve this issue?

--
THANKS AND REGARDS
SRINIVAS REDDY G A


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--------------C50B320C36E76B92CB9FA7EC--