Обсуждение: Unexpected chunk number
Hi, On running pg_dump, I am consistently getting the following errors: pg_dump: ERROR: unexpected chunk number 2 (expected 0) for toast value 223327 pg_dump: SQL command to dump the contents of table "pagecache" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: unexpected chunk number 2 (expected 0) for toast value 223327 pg_dump: The command was: COPY meatballwiki.pagecache (page, lastmodified, response) TO stdout; I am running psql 8.1.4. The disk storing the database was recently corrupted, and we restored from an old image; I appreciate this is likely to have triggered the error. What I'm interested in is how to fix it! I've tried a simple `VACUUM ANALYZE FULL`, I've done `REINDEX DATABASE foo`, and I've stopped and started postmaster (all in that order). Nothing has helped. I've looked at the pgsql mailing list archives, but so far can find no solution that fits; for instance, I cannot find any tables under pg_toast., so cannot use the thread at http://archives.postgresql.org/pgsql-admin/2005-09/msg00057.php Any help would be most appreciated! Cheers, Chris Purcell
Chris Purcell wrote: > Hi, > > On running pg_dump, I am consistently getting the following errors: > > pg_dump: ERROR: unexpected chunk number 2 (expected 0) for toast value > 223327 > pg_dump: SQL command to dump the contents of table "pagecache" failed: > PQendcopy() failed. > pg_dump: Error message from server: ERROR: unexpected chunk number 2 > (expected 0) for toast value 223327 > pg_dump: The command was: COPY meatballwiki.pagecache (page, > lastmodified, response) TO stdout; > > I am running psql 8.1.4. The disk storing the database was recently > corrupted, and we restored from an old image; I appreciate this is > likely to have triggered the error. What I'm interested in is how to fix > it! "old image" - does that refer to something like an filesystem level backup or the restoration of a former pg_dump generated backup ? The former is generally NOT save (except if you followed the PITR-advises in the docs or similiar) with a running postmaster ... Stefan
> "old image" - does that refer to something like an filesystem level > backup or the restoration of a former pg_dump generated backup ? > The former is generally NOT save (except if you followed the PITR- > advises in the docs or similiar) with a running postmaster ... Ah. Yes, the former, as we did not have a recent pg_dump. Oops. Given that we are where we are, what is the best advice? Can we recover the database, given that 99% of the data works? I can happily drop the entire contents of the "pagecache" table, as it is regenerated on the fly, if that will obviate the problem. Cheers, Chris
Chris Purcell <chris.purcell.39@gmail.com> writes:
> Given that we are where we are, what is the best advice? Can we
> recover the database, given that 99% of the data works? I can happily
> drop the entire contents of the "pagecache" table, as it is
> regenerated on the fly, if that will obviate the problem.
That will get you past the reported problem, but I wonder what other
corruption is lurking ... once you've managed to pg_dump you'd better
inspect the data very carefully.
regards, tom lane
> That will get you past the reported problem, but I wonder what other > corruption is lurking ... once you've managed to pg_dump you'd better > inspect the data very carefully. Would the best advice be to get a pg_dump, then drop the database entirely and rebuild it? Cheers, Chris
Chris Purcell <chris.purcell.39@gmail.com> writes:
>> That will get you past the reported problem, but I wonder what other
>> corruption is lurking ... once you've managed to pg_dump you'd better
>> inspect the data very carefully.
> Would the best advice be to get a pg_dump, then drop the database
> entirely and rebuild it?
Definitely. It's entirely possible for pg_dump to dump successfully
from a database that still contains corruption. An example:
broken indexes on user tables. COPY just does a seqscan and never looks
at the contents of indexes ...
regards, tom lane
>> Would the best advice be to get a pg_dump, then drop the database >> entirely and rebuild it? > > Definitely. It's entirely possible for pg_dump to dump successfully > from a database that still contains corruption. An example: > broken indexes on user tables. COPY just does a seqscan and never > looks > at the contents of indexes ... Just out of curiosity, why is it not possible to rebuild these indices entirely from scratch, dropping the defective file entirely, *without* reimporting into a fresh database? Cheers, Chris
Chris Purcell <chris.purcell.39@gmail.com> writes:
> Would the best advice be to get a pg_dump, then drop the database
> entirely and rebuild it?
>>
>> Definitely. It's entirely possible for pg_dump to dump successfully
>> from a database that still contains corruption. An example:
>> broken indexes on user tables. COPY just does a seqscan and never
>> looks
>> at the contents of indexes ...
> Just out of curiosity, why is it not possible to rebuild these
> indices entirely from scratch, dropping the defective file entirely,
> *without* reimporting into a fresh database?
See REINDEX. But my point was that there may be undetected corruption.
If I were you I'd not rely on REINDEX to prevent all problems.
regards, tom lane
> See REINDEX. But my point was that there may be undetected > corruption. > If I were you I'd not rely on REINDEX to prevent all problems. Indeed; REINDEX neither detected nor fixed the corruption. Thanks for all your help; we'll recreate the database as soon as we can. Many thanks, Chris Purcell