After resolving some problems with the network drive on which our Postgres database was hosted, we encountered this error:

OperationalError: FATAL: cache lookup failed for index 2662

That particular error was from a Python command line, but other attempts at connecting to the DB yielded the same thing, either ordinary querying or attempting a dump of the database.

How do you go about fixing this error and recovering the data?


Sounds like you have data corruption. When you say "network drive", that's a red flag right there. It's very hard to configure NFS to get data reliability with PostgreSQL. It's AFAIK impossible to configure SMB to get it. So most likely, you got data corruption in the crash, because the network mount was not safe.

2662 is the oid index on pg_class - which is basically the index on the table that lists which tables and indexes exist in your database. When this is corrupt, well, you're in serious trouble.

Your best bet at this point is trying to start a standalone backend (with the main one shut down of course) with "-P" which turns off usage of system indexes. Then attempt to REINDEX pg_class, and possibly the rest of the system indexes. If this worked, do a pg_dump backup right away, and re-initialize the database. There is likely more corruption in the system.

If this does not work, try posting to the pgsql-general mailinglist, you're likely to get more and quicker responses there. But most likely, it's back to your backups - or hire a data recovery expert if your data is really important and you don't have working backups.

  • Thought this had actually worked, however I could only access the Postgres backend without specifying the data directory. Even with the -P flag, specifying the correct data directory yielded the same cache lookup error. Given that we've got a backup (not as recent as we'd like) can we restore from that point on using the transaction logs?
    – bennylope
    Jun 30 '10 at 11:46
  • Posted a follow up question on how to reindex, given that the -P flag doesn't seem to be working: serverfault.com/questions/156284/…
    – bennylope
    Jun 30 '10 at 12:59
  • If you have your system set up with PITR backups (base backup using pg_start_backup/pg_stop backup and log archiving) then yes, you can restore from that point up until before the corruption occurred. If you are using pg_dump backups then, no, they are logical backups and not physical ones, and thus have no relation to the transaction log. Jun 30 '10 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.