Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

2 Answers
2

You could issue SET zero_damaged_pages TO ON and retry.
It will not rewrite any page to disk, just in memory. Assuming it works, the idea is to run COPY bad TO 'file' to dump as much valuable data as possible.

(that's also assuming that 11975439 is the OID of a table, not an index. If it's an index, you may just drop it).

How do I identify whether 11975439 is OID or INDEX?
– G. NgothoJun 25 '12 at 19:54

select relname from pg_class where oid=11975439. (it's no OID or index, it's the OID of a table or the OID of an index).
– Daniel VéritéJun 25 '12 at 23:55

The above query returns ac_ledger does this mean 11975439 is an OID of the table ac_ledger ?
– G. NgothoJun 26 '12 at 0:09

Yes. And if this error happens when you were doing select count(*) from ac_ledger that would totally make sense.
– Daniel VéritéJun 26 '12 at 0:11

Your solution worked, because I can get results when I run a select, but I dont know why the connection to the db is terminating when I don't limit. Am dealing with a large database.
– G. NgothoJun 26 '12 at 0:29

Sometimes it is due to usage of non-default PostgreSQL settings which allow DBAs to reduce reliability to improve performance. Some shops with redundant servers find this a worthwhile trade-off, because losing one server in a large redundant server farm is no big deal. You may hear this sort of configuration referred to as "DBAs running with scissors" and consists primarily of turning off fsync, full_page_writes, and synchronous_commit -- all of which are needed to ensure persistence of committed transactions. The first two are required to prevent database corruption on a crash of the OS or hardware. You should not turn these off unless it is OK to lose the entire PostgreSQL instance.

Rarely, people delete some integral part of their database, such as the pg_xlog or pg_clog subdirectory.

Even more rarely, there is a PostgreSQL bug which caused the corruption. Because any report of such a bug is treated very seriously, they tend to get fixed very quickly and are generally encountered these days by people running outdated versions which are out of support. Without knowing the exact version (the output from SELECT version();), it's hard to know whether you are vulnerable to a known, fixed bug. Possibly relevant to this particular error message is whether you used pg_upgrade and exactly which version of that you used.

My case is as a result of hardware issue after some power surge. Am now trying to recover as much data as I can since there was no reliable backup. I however have a copy of data directory which Am using for the repair attempts.
– G. NgothoJun 25 '12 at 3:13

Yeah, that tends to make pg_resetxlog sound like the next step after ensuring you have a good filesystem level copy of where you are right now.
– kgrittnJun 25 '12 at 10:39

I have tried pg_resetxlog and am getting this output Transaction log reset but when I run the select count(*) , I still get the same error output as above @kgrittn ?
– G. NgothoJun 25 '12 at 12:52

It looks like you will need to do some manual work on the pg_clog files. What OS is this? What do you get when you run pg_resetxlog -n (with your data directory)?
– kgrittnJun 25 '12 at 14:19

this is the result from pg_resetxlog -n ` Am running fedora 16 32 bit am getting some results which apparently I cant share in this comment - Its more than the required characters for a comment, but I here's an excerpt First log file ID after reset: 0 First log file segment after reset: 1 pg_control version number: 843 Catalog version number: 200904091 Database system identifier: 5757969149907954287 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/656 Latest checkpoint's NextOID: 11565 `
– G. NgothoJun 25 '12 at 19:41