pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed(pgsql8.4) DATA Corruption
4+ messages / 4 participants
[nested] [flat]
* (pgsql8.4) DATA Corruption
@ 2011-08-19 19:08 Mikola Rose <mrose@power-soft.com>
2011-08-19 20:02 ` Re: (pgsql8.4) DATA Corruption Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mikola Rose @ 2011-08-19 19:08 UTC (permalink / raw)
To: pgsql-sql
Happy Friday people!
I was wondering if anyone had any suggestions on how to resolve this issue... I am moving otrs to another server and during the backup process I am running into this error
pg_dump: dumping contents of table article_attachment
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: unexpected chunk number 7 (expected 6) for toast value 77281 in pg_toast_57366
pg_dump: The command was: COPY public.article_attachment (id, article_id, filename, content_size, content_type, content, create_time, create_by, change_time, change_by, content_id, content_alternative) TO stdout;
pg_dump: *** aborted because of error
I have tried a vacuum and reindex with no successes.
Mik Rose
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: (pgsql8.4) DATA Corruption
2011-08-19 19:08 (pgsql8.4) DATA Corruption Mikola Rose <mrose@power-soft.com>
@ 2011-08-19 20:02 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-08-19 20:27 ` Re: (pgsql8.4) DATA Corruption Mik Rose <mrose@power-soft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2011-08-19 20:02 UTC (permalink / raw)
To: Mikola Rose <mrose@power-soft.com>; +Cc: pgsql-sql
On Fri, Aug 19, 2011 at 1:08 PM, Mikola Rose <mrose@power-soft.com> wrote:
> Happy Friday people!
>
>
>
> I was wondering if anyone had any suggestions on how to resolve this
> issue... I am moving otrs to another server and during the backup process I
> am running into this error
>
> pg_dump: dumping contents of table article_attachment
>
>
>
>
>
> pg_dump: SQL command failed
>
> pg_dump: Error message from server: ERROR: unexpected chunk number 7
> (expected 6) for toast value 77281 in pg_toast_57366
>
>
>
>
>
> pg_dump: The command was: COPY public.article_attachment (id, article_id,
> filename, content_size, content_type, content, create_time, create_by,
> change_time, change_by, content_id, content_alternative) TO stdout;
>
>
>
>
>
> pg_dump: *** aborted because of error
>
>
>
>
>
> I have tried a vacuum and reindex with no successes.
You'll likely have to figure which blocks are corrupted, and copy out
the good data using a where clause the excludes it, then get what you
can out of it, truncate the table, and reinsert the data.
Then figure out what part of your hardware is / might be dodgy. mem
test, disk check, etc.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: (pgsql8.4) DATA Corruption
2011-08-19 19:08 (pgsql8.4) DATA Corruption Mikola Rose <mrose@power-soft.com>
2011-08-19 20:02 ` Re: (pgsql8.4) DATA Corruption Scott Marlowe <scott.marlowe@gmail.com>
@ 2011-08-19 20:27 ` Mik Rose <mrose@power-soft.com>
2011-08-26 03:51 ` Re: (pgsql8.4) DATA Corruption Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 4+ messages in thread
From: Mik Rose @ 2011-08-19 20:27 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
Thanks for the suggestions Scott.
I was looking at the zero_damage_page option and enabled it in my postgres.conf. I saw that it detected bad pages and zero'd them out during a number of reindex and a vacuum's.
Now when i reindex though, I am getting this error.
NOTICE: table "pg_foreign_server" was reindexedERROR: could not create unique index "pg_toast_57366_index"
DETAIL: Table contains duplicated values.
ERROR: could not create unique index "pg_toast_57366_index"
DETAIL: Table contains duplicated values.
On 2011-08-19, at 1:02 PM, Scott Marlowe wrote:
> On Fri, Aug 19, 2011 at 1:08 PM, Mikola Rose <mrose@power-soft.com> wrote:
>> Happy Friday people!
>>
>>
>>
>> I was wondering if anyone had any suggestions on how to resolve this
>> issue... I am moving otrs to another server and during the backup process I
>> am running into this error
>>
>> pg_dump: dumping contents of table article_attachment
>>
>>
>>
>>
>>
>> pg_dump: SQL command failed
>>
>> pg_dump: Error message from server: ERROR: unexpected chunk number 7
>> (expected 6) for toast value 77281 in pg_toast_57366
>>
>>
>>
>>
>>
>> pg_dump: The command was: COPY public.article_attachment (id, article_id,
>> filename, content_size, content_type, content, create_time, create_by,
>> change_time, change_by, content_id, content_alternative) TO stdout;
>>
>>
>>
>>
>>
>> pg_dump: *** aborted because of error
>>
>>
>>
>>
>>
>> I have tried a vacuum and reindex with no successes.
>
> You'll likely have to figure which blocks are corrupted, and copy out
> the good data using a where clause the excludes it, then get what you
> can out of it, truncate the table, and reinsert the data.
>
> Then figure out what part of your hardware is / might be dodgy. mem
> test, disk check, etc.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: (pgsql8.4) DATA Corruption
2011-08-19 19:08 (pgsql8.4) DATA Corruption Mikola Rose <mrose@power-soft.com>
2011-08-19 20:02 ` Re: (pgsql8.4) DATA Corruption Scott Marlowe <scott.marlowe@gmail.com>
2011-08-19 20:27 ` Re: (pgsql8.4) DATA Corruption Mik Rose <mrose@power-soft.com>
@ 2011-08-26 03:51 ` Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 4+ messages in thread
From: Craig Ringer @ 2011-08-26 03:51 UTC (permalink / raw)
To: Mik Rose <mrose@power-soft.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
On 20/08/2011 4:27 AM, Mik Rose wrote:
> Thanks for the suggestions Scott.
>
> I was looking at the zero_damage_page option and enabled it in my
> postgres.conf. I saw that it detected bad pages and zero'd them out
> during a number of reindex and a vacuum's.
>
> Now when i reindex though, I am getting this error.
>
> NOTICE: table "pg_foreign_server" was reindexedERROR: could not
> create unique index "pg_toast_57366_index"
> DETAIL: Table contains duplicated values.
>
> ERROR: could not create unique index "pg_toast_57366_index"
> DETAIL: Table contains duplicated values.
You'll have to figure out what what values are duplicates within each
table and DELETE one of them after determining which is the correct one
- if you can. Then you should be able to re-create the index. If the
DELETE fails you might find you have to DROP the index, do the
DELETE(s), then CREATE the index again.
If the index is a partial index (ie it has a WHERE clause) you need to
find duplicates that match that WHERE clause, you can ignore ones that
don't match.
I hope you took a file-system-level backup of your database before
starting to mess with it. People here will normally advise you to do
that first and I'm kind of surprised nobody did.
--
Craig Ringer
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-08-26 03:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-19 19:08 (pgsql8.4) DATA Corruption Mikola Rose <mrose@power-soft.com>
2011-08-19 20:02 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-08-19 20:27 ` Mik Rose <mrose@power-soft.com>
2011-08-26 03:51 ` Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox