Re: Database is in recovery mode.

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