Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 25 '12 at 2:46

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 1 down vote accepted

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).

share|improve this answer
    
How do I identify whether 11975439 is OID or INDEX? –  G. Ngotho Jun 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. Ngotho Jun 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. Ngotho Jun 26 '12 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.

share|improve this answer
    
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. Ngotho Jun 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. –  kgrittn Jun 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. Ngotho Jun 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)? –  kgrittn Jun 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. Ngotho Jun 25 '12 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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