Обсуждение: 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_284730161pg_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
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
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
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@cyber.law.harvard.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_284730161pg_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={}_.dumpBut 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