[ADMIN] Database is in recovery mode.

[Lukasz Brodziak]

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.

The results of the pg_dump of the broken table:
pg_dump: Dumping the contents of table "invoice" failed: PQgetCopyData()
failed.
pg_dump: Error message from server: server closed the connection
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: The command was: COPY public.invoice(Inv_id, iss_date, pay_date,
comp_name)
pg_dump: *** aborted because of error

Regards
Luke

[Craig Ringer]

Post by Lukasz Brodziak
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

[Scott Marlowe]

On Mon, Nov 28, 2011 at 10:11 AM, Lukasz Brodziak

Post by Lukasz Brodziak
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.

[Lukasz Brodziak]

Hi,

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).

Post by Scott Marlowe
On Mon, Nov 28, 2011 at 10:11 AM, Lukasz Brodziak

Post by Lukasz Brodziak
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

Post by Lukasz Brodziak
connection closes. I have located the faulty table and when I try to

cluster

Post by Lukasz Brodziak
it I get FATAL: database is in recovery mode. It seems that there are

some

Post by Lukasz Brodziak
corrupted rows in the table but I can't find the way to repai the table

and

Post by Lukasz Brodziak
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.

[Tom Lane]

Post by Lukasz Brodziak
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

[Lukasz Brodziak]

Hello,

Thank You for the reply. I managed to delete the row the way below:
1. Using EMS Manager for PostgreSQL I selected all the rows except the
corrupted one.
2. Extracted the data to INSERT script
3. SET zero_damaged_pages TO true; VACUUM FULL table;
4. DROP/CREATE
5. INSERT script without the corrupted row
6. INSERT script for the missing row created with EMS from the las
uncorrupted data folder

Everything went as predicted and the table is ok now.

Post by Tom Lane

Post by Lukasz Brodziak
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

Post by Lukasz Brodziak
an older backup of the db which contains correct row. Is there a way I

can

Post by Lukasz Brodziak
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