Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id D678819427CD
	for <pgsql-admin@postgresql.org>; Mon, 28 Nov 2011 22:42:42 -0400 (AST)
Received: from mail-vw0-f46.google.com ([209.85.212.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1RVDec-0000cD-1u
	for pgsql-admin@postgresql.org; Tue, 29 Nov 2011 02:42:42 +0000
Received: by vbbfc26 with SMTP id fc26so4372353vbb.19
	for <pgsql-admin@postgresql.org>; Mon, 28 Nov 2011 18:42:29 -0800 (PST)
Received: by 10.52.36.161 with SMTP id r1mr18990138vdj.118.1322534548658;
	Mon, 28 Nov 2011 18:42:28 -0800 (PST)
Received: from ayaki.localdomain (124-169-45-65.dyn.iinet.net.au.
	[124.169.45.65])
	by mx.google.com with ESMTPS id ir2sm33454154vdb.9.2011.11.28.18.42.25
	(version=SSLv3 cipher=OTHER); Mon, 28 Nov 2011 18:42:27 -0800 (PST)
Message-ID: <4ED4468E.2070101@ringerc.id.au>
Date: Tue, 29 Nov 2011 10:42:22 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20110930 Thunderbird/7.0.1
MIME-Version: 1.0
To: Lukasz Brodziak <lukasz.brodziak@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Database is in recovery mode.
References: 
 <CAGWYGjWPiffET_K6QVXjjQO_jRQiUEU+BSZr1hurrh1Obv8wyQ@mail.gmail.com>
In-Reply-To: 
 <CAGWYGjWPiffET_K6QVXjjQO_jRQiUEU+BSZr1hurrh1Obv8wyQ@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------080907050406040006000500"
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201111/136
X-Sequence-Number: 38730

This is a multi-part message in MIME format.
--------------080907050406040006000500
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------080907050406040006000500
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 11/29/2011 01:11 AM, Lukasz Brodziak wrote:
    <blockquote
cite="mid:CAGWYGjWPiffET_K6QVXjjQO_jRQiUEU+BSZr1hurrh1Obv8wyQ@mail.gmail.com"
      type="cite">
      <div>Hello,</div>
      <div><br>
      </div>
      <div>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.</div>
      <div>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.</div>
      <div>Any help will be useful with this issue.</div>
    </blockquote>
    <br>
    First, before you do ANYTHING else, shut the database down and take
    a file-level copy of the entire datadir. See:<br>
    <br>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a href="http://wiki.postgresql.org/wiki/Corruption">http://wiki.postgresql.org/wiki/Corruption</a><br>
    <br>
    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
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a
href="http://www.postgresql.org/docs/current/interactive/runtime-config-developer.html">http://www.postgresql.org/docs/current/interactive/runtime-config-developer.html</a>)
    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.<br>
    <br>
    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:
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <a href="http://www.postgresql.org/support/professional_support/">http://www.postgresql.org/support/professional_support/</a>
    .<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------080907050406040006000500--