Re: pg_upgrade failed for 9.0 to 9.2

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Vibhor Kumar <vibhor(dot)kumar(at)enterprisedb(dot)com>
Cc: AI Rumman <rummandba(at)gmail(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_upgrade failed for 9.0 to 9.2
Date: 2013-01-13 19:28:15
Message-ID: 20130113192815.GB3997@momjian.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-general

On Sun, Jan 13, 2013 at 02:17:34PM -0500, Vibhor Kumar wrote:
>
> On Jan 13, 2013, at 1:36 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>
> > CREATE VIEW stats_slowest_queries AS
> > SELECT pg_stat_activity.procpid, (('now'::text)::timestamp(6) with time
> > zone - pg_stat_activity.query_start) AS execution_time,
> > pg_stat_activity.current_query FROM pg_stat_activity WHERE
> > (pg_stat_activity.current_query !~~ '<IDLE%'::text) ORDER BY
> > (('now'::text)::timestamp(6) with time zone - pg_stat_activity.query_start)
> > DESC;
> > psql:pg_upgrade_dump_db.sql:498897: ERROR: column pg_stat_activity.procpid
> > does not exist
> > LINE 2: SELECT pg_stat_activity.procpid, (('now'::text)::timesta...
> > ^
> > It failed.
>
>
> Reason is: From postgreSQL 9.2 onwards, pg_stat_activity.procpid is renamed as pg_stat_activity.pid.
> http://www.postgresql.org/docs/9.2/interactive/release-9-2.html

Yes. Pg_upgrade doesn't know about renamed system columns, so if you
had a view that referenced a renamed column, pg_upgrade wouldn't know
about that until it tried to restore the view and it failed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

In response to

Browse pgsql-general by date

  From Date Subject
Next Message SUNDAY A. OLUTAYO 2013-01-13 21:12:21 Re: Linux Distribution Preferences?
Previous Message Scott Ribe 2013-01-13 19:26:33 9.2 upgrade glitch with search_path