Re: Fix corrupt pg_toast table?

Hello,
Thanks for the reply.

On Fri, Jul 31, 2009 at 5:24 PM, Greg Stark <gsstark(at)mit(dot)edu> 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.
>

Not really that long. The database was created in early June, and if that
is supposed to be a transaction count (3839923882), then yeah that seems way
out there.

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

When you say fill it with zeros, is there a certain file size I need to
create?
(I will do some more googling to see if I can find more examples of people
recovering from this problem)

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

I am sorry, that is a little over my head with my knowledge gained with PG
thus far.
Is there some background information I could read that would explain this to
me?

I tried that select a couple of times, and eventually got the "invalid page
header in the block 984 of relation "pg_toast_17431_index" error message
again. ctid = '(0,6)' gave it to me.
(Although, I don't know what I am doing here! :)

Thanks again for the reply, your help is appreciated!
Michael.