pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase is in recovery mode.
6+ messages / 4 participants
[nested] [flat]
* Database is in recovery mode.
@ 2011-11-28 17:11 Lukasz Brodziak <lukasz.brodziak@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Lukasz Brodziak @ 2011-11-28 17:11 UTC (permalink / raw)
To: pgsql-admin
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
--
Łukasz Brodziak
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database is in recovery mode.
@ 2011-11-29 02:42 Craig Ringer <ringerc@ringerc.id.au>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2011-11-29 02:42 UTC (permalink / raw)
To: Lukasz Brodziak <lukasz.brodziak@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database is in recovery mode.
@ 2011-11-29 04:51 Scott Marlowe <scott.marlowe@gmail.com>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2011-11-29 04:51 UTC (permalink / raw)
To: Lukasz Brodziak <lukasz.brodziak@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database is in recovery mode.
@ 2011-11-30 07:40 Lukasz Brodziak <lukasz.brodziak@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Lukasz Brodziak @ 2011-11-30 07:40 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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).
2011/11/29 Scott Marlowe <scott.marlowe@gmail.com>
> 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.
>
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database is in recovery mode.
@ 2011-11-30 14:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-11-30 14:40 UTC (permalink / raw)
To: Lukasz Brodziak <lukasz.brodziak@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database is in recovery mode.
@ 2011-11-30 14:56 Lukasz Brodziak <lukasz.brodziak@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Lukasz Brodziak @ 2011-11-30 14:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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.
2011/11/30 Tom Lane <tgl@sss.pgh.pa.us>
> 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
>
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-11-30 14:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-28 17:11 Database is in recovery mode. Lukasz Brodziak <lukasz.brodziak@gmail.com>
2011-11-29 02:42 ` Craig Ringer <ringerc@ringerc.id.au>
2011-11-29 04:51 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-11-30 07:40 ` Lukasz Brodziak <lukasz.brodziak@gmail.com>
2011-11-30 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-30 14:56 ` Lukasz Brodziak <lukasz.brodziak@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox