pg_upgrade failed for 9.0 to 9.2
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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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@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. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general