Re: Database is in recovery mode.

[Craig Ringer <ringerc@xxxxxxxxxxxxx>]

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