Search Postgresql Archives

Detecting corrupt table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux