Re: Fix corrupt pg_toast table?

[Michael Clark <codingninja@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

Thanks for the reply!

On Thu, Jul 30, 2009 at 5:59 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Michael Clark <codingninja@xxxxxxxxx> 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.