Re: Fix corrupt pg_toast table?

On Fri, Jul 31, 2009 at 8:01 PM, Michael Clark<codingninja(at)gmail(dot)com> wrote:
> I tried this, and might have exposed another issue.
> Now I am getting:
> ERROR: could not access status of transaction 3839923882
> DETAIL: could not open file "pg_clog/0E4E": No such file or directory.
> I checked the pg_clog folder, and there is only a 0000 file.

How long has this database been in use? That's a very high transaction
number that would only be reached on a heavily used database after a
reasonably long period of use. If that's not likely than a likely
possibility is that your table has been overwritten with garbage.
Possibly by a filesystem bug or hardware failure.

You can work around this problem for that row by creating a file named
0E4E in the clog directory. I think you can fill it with zeros but if
you search the mailing list you'll find instructions for doing this
that are might have better suggestions.

You'll still be best off recovering rows one by one. If you have
trouble using the index you might find it more reliable (but more
tedious) to do it using:

select * from tab where ctid = '(0,0)'
select * from tab where ctid = '(0,1)'
select * from tab where ctid = '(0,2)'
...

The first digit is the page number and the second is the index on the
page which can go as high as about 250.

--
greg
http://mit.edu/~gsstark/resume.pdf