Trouble recovering data from corrupted PostgreSQL tables 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?
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.