Thread: Unexpected chunk number

Unexpected chunk number

From
Chris Purcell
Date:
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

Re: Unexpected chunk number

From
Stefan Kaltenbrunner
Date:
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

Re: Unexpected chunk number

From
Chris Purcell
Date:
> "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

Re: Unexpected chunk number

From
Tom Lane
Date:
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

Re: Unexpected chunk number

From
Chris Purcell
Date:
> 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

Re: Unexpected chunk number

From
Tom Lane
Date:
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

Re: Unexpected chunk number

From
Chris Purcell
Date:
>> 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

Re: Unexpected chunk number

From
Tom Lane
Date:
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

Re: Unexpected chunk number

From
Chris Purcell
Date:
> 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