I'm trying to recover my database after a recent corruption of the same. Now I have two tables that are really giving me trouble.
When I do select count(*) One table returns:
ERROR: could not access status of transaction 2619431010
DETAIL: Could not open file "pg_clog/09C2": No such file or directory.
The other one returns:
ERROR: invalid page header in block 94206 of relation base/11974232/11975439
Is there a way I can work around and get my data back?
About this error:
ERROR: invalid page header in block 94206 of relation base/11974232/11975439
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).
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).
Commented
Jun 25, 2012 at 23:55
ac_ledger does this mean 11975439 is an OID of the table ac_ledger ?
Commented
Jun 26, 2012 at 0:09
select count(*) from ac_ledger that would totally make sense.
Commented
Jun 26, 2012 at 0:11
select, but I dont know why the connection to the db is terminating when I don't limit. Am dealing with a large database.
Commented
Jun 26, 2012 at 0:29
The very first thing you should do when you encounter database corruption is to capture a filesystem copy of the data directory tree while PostgreSQL is not running and save it somewhere safe.
Once you have done that, if you do not have a backup which you can restore you might want to try pg_resetxlog. Note that the documentation has this recommendation:
You should immediately dump your data, run initdb, and reload. After reload, check for inconsistencies and repair as needed.
Read over the documentation page for pg_resetxlog until you understand the options and which might be best for your situation.
Finally, make sure you understand how the database was corrupted.
Usually this is due to bad hardware or drivers.
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.
Transaction log reset but when I run the select count(*) , I still get the same error output as above @kgrittn ?
Commented
Jun 25, 2012 at 12:52
pg_resetxlog -n (with your data directory)?
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 `
Commented
Jun 25, 2012 at 19:41