pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Craig Ringer <ringerc@ringerc.id.au>
To: Lukasz Brodziak <lukasz.brodziak@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Database is in recovery mode.
Date: Tue, 29 Nov 2011 10:42:22 +0800
Message-ID: <4ED4468E.2070101@ringerc.id.au> (raw)
In-Reply-To: <CAGWYGjWPiffET_K6QVXjjQO_jRQiUEU+BSZr1hurrh1Obv8wyQ@mail.gmail.com>
References: <CAGWYGjWPiffET_K6QVXjjQO_jRQiUEU+BSZr1hurrh1Obv8wyQ@mail.gmail.com>
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
view thread (6+ messages) latest in thread
Message-ID: <4ED4468E.2070101@ringerc.id.au> Permalink: ../4ED4468E.2070101@ringerc.id.au/ Also on: postgresql.org/message-id/4ED4468E.2070101@ringerc.id.au ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-admin@postgresql.org Cc: ringerc@ringerc.id.au, lukasz.brodziak@gmail.com Subject: Re: Database is in recovery mode. In-Reply-To: <4ED4468E.2070101@ringerc.id.au> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
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