We have run into a situation where our postgresql 8.2.5 database appears
to be corrupt and we are no longer able to run pg_dump. We don't know
what is causing the data corruption issues and unfortunately this is one
of those cases where we cannot reliably reproduce the problem (so no
test case can be provided).
The error message output from running pg_dump in verbose mode is:
pg_dump: reading schemas
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: catalog is missing 9
attribute(s) for relid 10297
pg_dump: The command was: SELECT tableoid, oid, nspname, (SELECT rolname
FROM pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM
pg_namespace
pg_dump: *** aborted because of error
Querying 'pg_catalog.pg_roles' directly also produces the error. The
pg_roles object is simply a view of pg_authid that blanks out the
password field. Querying the pg_authid table directly does not produce
the error.
Our server setup is:
Linux 2.4
Postgresql 8.2.5
Our application is fairly transaction heavy, about 50-200 updates per
second on a few small tables, and we have around 40 or so client
connections querying the database.
Checking the postgresql server log file did not provide any additional
useful information beyond what pg_dump provided.
The first time we ran into this problem on 2007-12-16 we decided that
since the database system objects appeared to be corrupt we wiped the
server clean and started over. We also checked for hardware issues but
none were found. We checked for (disk errors, memory errors, fs errors)
as well as (overheating drives, intermittent drive slowness not reported
by the kernel, drive read prediction failures, errors that only surface
under extreme load). We were not able to test (overheating CPU, bad
power supply (bad voltage)).
A few weeks later on Dec 25th the same exact error cropped up again
"catalog is missing 9 attribute(s) for relid 10297".
My questions are:
1) Does this error message generally indicate that the database system
objects are corrupt?
2) To resolve this type of issue what would be the recommended course of
action? In our case we decided that the database was a lost cause and
we started over (unfortunately only to run into the same issue 2 weeks
later).
3) Does anyone have any suggestions for additional logging or monitoring
we could turn on to try and determine the root cause of this issue?
Thanks for any help,
Brendan