Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_upgrade failed for 9.0 to 9.2

pg_upgrade failed for 9.0 to 9.2

From
AI Rumman
Date:
Hi,

When I was upgrading database from 9.0 to 9.2 using pg_upgrade, I got the error:

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.

Could you let me know why?

Thanks.

Re: pg_upgrade failed for 9.0 to 9.2

From
Vibhor Kumar
Date:
On Jan 13, 2013, at 1:36 PM, AI Rumman <rummandba@gmail.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

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog:http://vibhork.blogspot.com

Re: pg_upgrade failed for 9.0 to 9.2

From
Bruce Momjian
Date:
On Sun, Jan 13, 2013 at 02:17:34PM -0500, Vibhor Kumar wrote:
>
> On Jan 13, 2013, at 1:36 PM, AI Rumman <rummandba@gmail.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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