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...9/msg00057.php

Any help would be most appreciated!

Cheers,
Chris Purcell


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Chris Purcell <chris.purcell.39 (AT) gmail (DOT) 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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Would the best advice be to get a pg_dump, then drop the database
entirely and rebuild it?

Cheers,
Chris


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Chris Purcell <chris.purcell.39 (AT) gmail (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Chris Purcell <chris.purcell.39 (AT) gmail (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq