Re: Fix corrupt pg_toast table?

Hello again.

I have an update, hoping someone can steer me in the right direction here.

After receiving the "could not access transaction" error mentioned in my
previous email I did some more digging and found that people have had
success resolving this issue using pg_resetxlog.

I gave this a try, first without specifying any options, which did not
resolve anything (it did not give an error on the command line). I then
read up on the command and passed a value for every swtich to set new
values, and again this had no effect. It did not give an error on the
command line, but trying to reset the index gives the same error. The
response I get at the command line after running the pg_resetxlog command
is: Transaction log reset

Here is the error I get when trying to reset this particular index:
2009-07-31 12:58:07.503 EDT [Test] - ERROR: could not access status of
transaction 3839923882
2009-07-31 12:58:07.503 EDT [Test] - DETAIL: Could not open file
"pg_clog/0E4E": No such file or directory.
2009-07-31 12:58:07.503 EDT [Test] - STATEMENT: reindex index
pg_toast.pg_toast_17431_index;

Here is the final pg_resetxlog command I tried to resolve the issue:
pg_resetxlog -f -x 0x100000 -m 0x10000 -o 0x10000 -l 0x1,0x1,0xCD ./

Here are the filenames for the files in the various directories that led me
to these switch values, as per the docs on pg_resetxlog:
-x switch - pg_clog folder had 1 file named: 0000
-m switch - pg_multixact/offsets had 1 file named: 0000
-o switch - pg_multixact/members had 1 file named: 0000
-l swtich - pg_xlog had 7 files, the one with the biggest named is:
0000000100000000000000CC

After running the pg_resetxlog command above I get the exact same error
about pg_clog/0E4E missing.

Does anyone have any other ideas about how to solve this?

Thanks,
Michael.

On Thu, Jul 30, 2009 at 6:08 PM, Michael Clark <codingninja(at)gmail(dot)com>wrote:

> Thanks for the reply!
> On Thu, Jul 30, 2009 at 5:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Michael Clark <codingninja(at)gmail(dot)com> writes:
>> > I have a database which appears to have some corruption in a toast
>> value.
>>
>> > When I select a certain table, I get the following error:
>> > ERROR: invalid page header in block 984 of relation
>> "pg_toast_17341_index"
>>
>> If it's only that index that has gotten damaged, REINDEX will fix it, eg
>> reindex index pg_toast.pg_toast_17341_index;
>>
>
>
> 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.
>
> Any ideas on this one?
>
>
>> The bigger concern is whether there is other damage. Have you had any
>> system crashes, indications of flaky hardware, etc on that machine?
>>
>
> This happened on a customer machine which I have not had a chance to
> investigate. I have copied the PGDATA folder to one of our systems to
> diagnose.
>
> Thanks again,
> Michael.
>
>