Обсуждение: Database is in recovery mode.

--
Łukasz Brodziak

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

On Mon, Nov 28, 2011 at 10:11 AM, Lukasz Brodziak
<lukasz.brodziak@gmail.com> 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

After taking a file system level backup as suggested by Craig, first
try updating to the latest point release of 8.2.  There's literally
years differences / bug fixes between 8.2.0 and 8.2.22 which was the
last release.  It might be that simply updating to 8.2.22 will fix
your problems.

Hi,

Lukasz Brodziak <lukasz.brodziak@gmail.com> writes:
> I have isolated the corrupted row of data and isolated column which
> constains bad data. Unfortunately I can't do anything with the row. I have
> an older backup of the db which contains correct row. Is there a way I can
> restore only the given row or replace it in the file ( I also located the
> row on the page).

You haven't shown us exactly how it's failing, but depending on what
sort of corruption this is, you might be able to delete the damaged row
with
    DELETE FROM table WHERE ctid = '...';

            regards, tom lane

Hello,