[GENERAL] Error when trying to use pg_dump on 8.3 after minor release update
Greetings,
I get the following error when I try to dump a database on a production server:
[jbe...@dms dms]$ pg_dump -U dms_user -s dms dms_s.sql
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not open relation
with OID 27224
pg_dump: The command was: SELECT
pg_catalog.pg_get_viewdef('27289'::pg_catalog.oid) as viewdef
It is a RHEL 5 x86_64 server, running 8.3.7/8
Since the previous backup, we upgraded (via yum) the server from 8.3.7
to 8.3.8 without a restart.
With this error, I'm concerned about stopping and starting postgresql
to see if the error goes away: I don't want us to be left with a dead
database and no backup with the latest data.
So, is this expected behavior when you fail to restart after a minor
version upgrade, or is this a sign of hardware failure and we should
try dumping relations one at a time?
Regards,
-Joshua Berry
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error when trying to use pg_dump on 8.3 after minor release update
Joshua Berry yob...@gmail.com writes:
[jbe...@dms dms]$ pg_dump -U dms_user -s dms dms_s.sql
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:Â could not open relation
with OID 27224
pg_dump: The command was: SELECT
pg_catalog.pg_get_viewdef('27289'::pg_catalog.oid) as viewdef
This looks like catalog corruption :-(. Can you find a pg_class row
with that OID, ie
select * from pg_class where oid = 27224
I expect probably not, but then try it with enable_indexscan and
enable_bitmapscan turned off. If that finds a row, then what you
have is a corrupt pg_class_oid_index and you can probably get out
of trouble by reindexing it. You should also look at what 27289
is so you know what view is causing the problem.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error when trying to use pg_dump on 8.3 after minor release update
On Fri, Oct 23, 2009 at 1:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: This looks like catalog corruption :-(. Can you find a pg_class row with that OID, ie select * from pg_class where oid = 27224 I expect probably not, but then try it with enable_indexscan and enable_bitmapscan turned off. If that finds a row, then what you have is a corrupt pg_class_oid_index and you can probably get out of trouble by reindexing it. You should also look at what 27289 is so you know what view is causing the problem. regards, tom lane Thanks, that lead me to a recently added view by the customers. I could not get the view definition: dms=# select * from pg_class where oid = 27289; [lots of columns] v_reporte_pdv_du_epin | 2200 | 27294 |16384 | 0 | 27289 | 0 |0 | 0 | 0 | 0 | f | f | v | 27 | 0 | 0 |0 |0 | 0 | f | f | t | f |0 || (1 row) dms=# \d v_reporte_pdv_du_epin ERROR: could not open relation with OID 27224 However, once the view was dropped, I was able to perform a database dump. Any ideas how this can happen, and how to avoid? Should I assume that there are more problems and recreate the database from a fresh dump? Or will the system suffice in it's current state? Regards, -Joshua Berry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
