Re: Detecting corrupt table

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

you might want to log any errors resulting from pg_dump and then grep
through them to verify. or you could record the exit status ( $? ) for each
pg_dump command.

I was also thinking about how to check if something malformed your data on
disk. I could think of some ways to do that, but it doesn't look like you
are looking for that.

hth

WBL

On Wed, Apr 18, 2012 at 12:05 AM, David Larochelle <
dlarochelle(at)cyber(dot)law(dot)harvard(dot)edu> wrote:

> 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
>
>
>

--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw