On 11/29/2011 01:11 AM, Lukasz Brodziak wrote:
Hello,
I have a problem with client's database. When I log into application
and work on the data everything seems fine, but when I try to dumpd
the database connection closes. I have located the faulty table and
when I try to cluster it I get FATAL: database is in recovery mode. It
seems that there are some corrupted rows in the table but I can't find
the way to repai the table and therefore whole DB.
PG is in the version 8.2 (in 2 weeks we are migrating to 9.0 and we
are preparing our clients' DBs for the migration) running on Windows.
Any help will be useful with this issue.
First, before you do ANYTHING else, shut the database down and take a
file-level copy of the entire datadir. See:
http://wiki.postgresql.org/wiki/Corruption
Once you've done that, you can attempt a repair. If the data in the
problem table isn't too important you can just drop the table. If it's
more important you could try enabling zero_damaged_pages (after READING
THE DOCUMENTATION THAT WARNS YOU THAT THIS CAUSES PERMANENT DATA LOSS
http://www.postgresql.org/docs/current/interactive/runtime-config-developer.html)
and do a "SELECT * FROM thetable" to see if Pg can fix it. Running
"REINDEX thetable" might also help if the problem is with an index, but
since a COPY fails I rather doubt it's index related in your case.
If zero_damaged_pages doesn't help or if you can't afford to risk losing
*any* possibly recoverable data, you should consider hiring a consultant
who knows PostgreSQL's innards and the table format well. See:
http://www.postgresql.org/support/professional_support/ .
--
Craig Ringer