Re: Missing chunks from my toast...

Well, I was unable to fix this with any reindexing stuff. Rather than use
last night's pg_dump, which completed successfully, and lose today's data,
however, I did this:

a) dump the schema
b) dump each individual table minus my bad one
c) select all the recs except the bad one
c1) I found this by using "select * from attachments limit 1000 offset 0"
then incremented until I found the bad rec. :)
d) insert the recs manually into the new db

Just an FYI for anyone else who runs into this and doesn't want to use a
known good backup.

-----Original Message-----
From: Scott Whitney
Sent: Jun 20, 2008 12:08 PM
To: pgsql-admin(at)postgresql(dot)org
Subject: RE: [ADMIN] Missing chunks from my toast...

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Jun 20, 2008 11:41 AM
To: Scott Whitney
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Missing chunks from my toast...

"Scott Whitney" <swhitney(at)journyx(dot)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