Repairing broken PostgreSQL databases / tables

From lxadm | Linux administration tips, tutorials, HOWTOs and articles
Jump to: navigation, search

If your server happened to crash, PostgresSQL database is corrupted, but didn’t contain too precious information, you may try the following fix.

The typical symptoms of a corrupted Postgres database would be like below:

 
2013-03-05 11:29:50 GMT ERROR:  invalid page header in block 608102 of
relation base/16385/16615 2013-03-05 11:29:50 GMT STATEMENT:  COPY
public.history (itemid, clock, value) TO stdout; 2013-03-05 11:29:50
GMT LOG:  could not send data to client: Broken pipe

or

Query failed: [0] PGRES_FATAL_ERROR:ERROR:  right sibling's left-link doesn't match:
block 149266 links to 70823 instead of expected 71357 in index "history_uint_1"

The actual fix is quite easy, and basically sets “zero_damaged_pages = on”, then performs vacuum and reindexing.

DATABASE=yourdatabase
 
TABLES=$(echo \\d | psql $DATABASE | grep "^ public" | awk '{print $3}')
 
for TABLE in $TABLES; do 
   echo $TABLE
   echo "SET zero_damaged_pages = on; VACUUM FULL $TABLE; REINDEX TABLE $TABLE" | psql $DATABASE
done