Обсуждение: BUG #9187: corrupt toast tables

The following bug has been logged on the website:

Bug reference:      9187
Logged by:          kevin
Email address:      kevin.hughes@uk.fujitsu.com
PostgreSQL version: 9.2.3
Operating system:   Centos 6.2 (64 bit)
Description:

I am unsure if this is a bug but other than a h/w problem I cannot explain
it (and there is no evidence of any h/w problem so far).

We have been running an instance of PostgreSQL on a cloud server for
approaching a year now. It provides the underlying DB for a Confluence wiki
installation. So far everything has gone well - postgres just did the job
:).

Sometime between the morning of the 31st of January and the morning of the
3rd of February the database was corrupted. During that period there was
little or no activity - other than the nightly postgres dump

Looking at various logs we found this error:  ERROR: unexpected chunk number
110 (expected 106) for toast value 223972 in pg_toast_80768;
Looking at our dump we discovered they had been failing - they were short.
Rerunning these online showed the pg_dumpall failing with toast errors and
creating a small dump file

I used psql to look at various tables and found 3 corrupt table entries -
all were toast table entries.

Investigation showed that the entries had not been intentionally changed by
the DB s/w since 2013 (entries are time stamped by Confluence)

On the evening of the 10th of February the database problems were fixed –
the broken entries were removed.

However on dumping the db a short dump was created although no errors were
reported. On investigation a further corruption was located – a corruption
that was NOT identified previously although the new corruption was detected
in exactly the same way as the previous three corruptions. Again the
corrupted entry had not apparently  changed since 2013. NOTE – the database
use had been limited to read only access since the first corruptions were
discovered


Based on the evidence the suggestions from the internet point to a h/w
fault.... however there is no other evidence that a h/w fault has occurred


I regret I don't know what evidence to provide - or what evidence remains
from when the corruption occurred.

kevin.hughes@uk.fujitsu.com writes:
> Sometime between the morning of the 31st of January and the morning of the
> 3rd of February the database was corrupted. During that period there was
> little or no activity - other than the nightly postgres dump

> Looking at various logs we found this error:  ERROR: unexpected chunk number
> 110 (expected 106) for toast value 223972 in pg_toast_80768;

FWIW, this type of error is sometimes due to corruption in the toast
table's index, in which case you can fix it with a REINDEX.  I'd try
that before deleting data, anyway.

Hard to tell what the actual underlying cause is :-(.  I do note that
you're running a PG version that's a year or so old.  It'd be prudent
to think about updating to 9.2.7 when that comes out next week.

            regards, tom lane

Hello

I have a similar report:

    2014-01-17 02:02:45 CET ERROR:  missing chunk number 0 for toast value
26127 in pg_toast_20142
    2014-01-17 02:02:45 CET STATEMENT:  COPY journal.product_journal
(product_id, revision,
tablename, changes, create_user_id, item_id, created, processed,
process_user_id, confirmed) TO stdout;

PG 9.2.4

probably related
http://www.postgresql.org/message-id/C62EC84B2D3CF847899CCF4B589CCF70B20AA08F@BBMBX.backbone.local

Regards

Pavel


2014-02-11 22:19 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

> kevin.hughes@uk.fujitsu.com writes:
> > Sometime between the morning of the 31st of January and the morning of
> the
> > 3rd of February the database was corrupted. During that period there was
> > little or no activity - other than the nightly postgres dump
>
> > Looking at various logs we found this error:  ERROR: unexpected chunk
> number
> > 110 (expected 106) for toast value 223972 in pg_toast_80768;
>
> FWIW, this type of error is sometimes due to corruption in the toast
> table's index, in which case you can fix it with a REINDEX.  I'd try
> that before deleting data, anyway.
>
> Hard to tell what the actual underlying cause is :-(.  I do note that
> you're running a PG version that's a year or so old.  It'd be prudent
> to think about updating to 9.2.7 when that comes out next week.
>
>                         regards, tom lane
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>