Re: Missing chunks from my toast... - Mailing list pgsql-admin

From Scott Whitney
Subject Re: Missing chunks from my toast...
Date
Msg-id 20080620170458.1D3AD3E2160@mail.int.journyx.com
Whole thread Raw
In response to Re: Missing chunks from my toast...  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-admin
Tom,
Thanks. Here's the output of what just happened:

rt3=# select reltoastrelid::regclass from pg_class where relname =
'attachments';
       reltoastrelid
---------------------------
 pg_toast.pg_toast_8507627
(1 row)

rt3=# reindex table pg_toast.pg_toast_8507627;
REINDEX
rt3=# \q
-bash-2.05b$ pg_dump rt3 > /tmp/rt3
pg_dump: ERROR:  missing chunk number 0 for toast value 110439697
pg_dump: SQL command to dump the contents of table "attachments" failed:
PQendcopy() failed.
pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast
value 110439697
pg_dump: The command was: COPY public.attachments (id, transactionid,
parent, messageid, subject, filename, contenttype, contentencoding, content,
headers, creator, created) TO stdout;


Any way to get this bad boy to backup?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Jun 20, 2008 11:41 AM
To: Scott Whitney
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Missing chunks from my toast...

"Scott Whitney" <swhitney@journyx.com> writes:
> There goes my breakfast...
> pg_dump: ERROR:  missing chunk number 0 for toast value 110439697
> pg_dump: SQL command to dump the contents of table "attachments" failed:
> PQendcopy() failed.
> pg_dump: Error message from server: ERROR:  missing chunk number 0 for
toast
> value 110439697
> pg_dump: The command was: COPY public.attachments (id, transactionid,
> parent, messageid, subject, filename, contenttype, contentencoding,
content,
> headers, creator, created) TO stdout;

> I've tried reindexing this table (attachments), to no avail.

You forgot the attachments, but did you reindex the table itself, or its
toast table?  The former cannot fix this, the latter might possibly.

Easiest way to get the toast table name is
    select reltoastrelid::regclass from pg_class where relname =
'mytable';
(if you've got more than one table with the same name, obviously you
need to add a condition on schema as well).  You'll probably need to be
superuser to reindex it.

            regards, tom lane


pgsql-admin by date:

Previous
From: Roberto Garcia
Date:
Subject: Re: Major upgrade advice
Next
From: Decibel!
Date:
Subject: Re: After upgrade cleanup