Обсуждение: Fix corrupt pg_toast table?

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

The bigger concern is whether there is other damage.  Have you had any
system crashes, indications of flaky hardware, etc on that machine?

            regards, tom lane

On Fri, Jul 31, 2009 at 8:01 PM, Michael Clark<codingninja@gmail.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

Michael Clark <codingninja@gmail.com> writes:
> On Fri, Jul 31, 2009 at 5:24 PM, Greg Stark <gsstark@mit.edu> wrote:
>>> Now I am getting:
>>> ERROR: could not access status of transaction 3839923882

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

It seems certain that you're looking at corrupt data.  Trashed data
frequently manifests this way, because the transaction ID is the first
field of tuple headers that the database can cross-check with any
amount of rigor.

At this point we know that at least two unrelated disk blocks have been
clobbered by something (first that index page, and now this).  There's
no very good reason to think there are only two :-(.  I'd bet at least
a cheese sandwich on hardware problems.  Test and fix/replace your
hardware, then go back to your last backup (I hope you've got one).

            regards, tom lane