Drop and reload table to clear corruption?

From: jboes(at)nexcerpt(dot)com (Jeff Boes)
To: pgsql-admin(at)postgresql(dot)org
Subject: Drop and reload table to clear corruption?
Date: 2002-10-22 17:42:44
Message-ID: d40a65a1.0210220942.211df2bf@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

We have a few tables that seem to have suffered some kind of
corruption in their TOAST files. Symptoms include:

+ Runaway CPU usage during VACUUM ANALYZE (load average
spikes of 20x normal for 10-15 minutes at a time) and
more rarely during other operations

+ Recurring messages of "ERROR: Parent tuple was not found"
during VACUUM FULL

We have upgraded from 7.2.1 to 7.2.3 without apparent effect. Now
what I would like to try is:

1. pg_dump the table

2. truncate the table

3. VACUUM FULL (is this necessary?)

4. reload from pg_dump file

However, due to the very large number of foreign key constraints into
this table, I really don't want to drop the table, nor do I want to
trigger cascading deletes or updates. Can I use the same snippet of
code generated by pg_dump to disable the triggers? Are there any
other precautions I should take?

Please CC me on any responses, as I read this list via NNTP, and
news.postgresql.org seems to be on the blink this week.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jeff Self 2002-10-22 17:44:53 Setting DateStyle
Previous Message Igor Georgiev 2002-10-22 16:45:07 Re: [HACKERS] Security question : Database access control