Обсуждение: (pgsql8.4) DATA Corruption
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
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.
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 thisissue... I am moving otrs to another server and during the backup process Iam running into this errorpg_dump: dumping contents of table article_attachmentpg_dump: SQL command failedpg_dump: Error message from server: ERROR: unexpected chunk number 7(expected 6) for toast value 77281 in pg_toast_57366pg_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 errorI 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.
On 20/08/2011 4:27 AM, Mik Rose wrote:
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
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