Detecting corrupt table

From: David Larochelle <dlarochelle(at)cyber(dot)law(dot)harvard(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Detecting corrupt table
Date: 2012-04-17 22:05:38
Message-ID: CABipw1mtbhkYJZwo2QZ7HLc3+YadW2XA_Jc5XD6=-8dKh0Uj4w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Our database has some corrupt tables and I'm trying to figure out what data
can be salvaged and what needs to be restored from backup or regenerated.

Initially I tried running select count(*) on all user tables. While this
did detect some corrupt tables, it missed others. For example, I was able
to run count(*) on a table but then got an error while trying to back it up.

pg_dump: Error message from server: ERROR: missing chunk number 0 for
toast value 368243665 in pg_toast_284730161
pg_dump: The command was: COPY public.stories (stories_id, media_id, url,
guid, title, description, publish_date, collect_date, story_texts_id,
full_text_rss) TO stdout;

Is there a simple way to determine which parts of the database are corrupt?
I'm currently running a script to back up each table individually using
something like the following:

psql -c "select tablename from pg_tables where tableowner = 'db_user' ORDER
by tablename " | tail -n +3 | head -n -2 | xargs -n 1 -i pg_dump --verbose
--table={} --file={}_.dump

But I'm worried that this approach will also miss database corruption and
was wondering if anyone has other suggestions.

Thanks,

David

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nils Gösche 2012-04-17 22:06:11 Re: Feature Proposal: Constant Values in Columns or Foreign Keys
Previous Message Eliot Gable 2012-04-17 22:02:42 Re: LOCK TABLE is not allowed in a non-volatile function