Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42655=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1X7rIc-0003BK-JG
 for pgsql-admin@arkaria.postgresql.org; Thu, 17 Jul 2014 19:25:02 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42655=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1X7rIb-0006aG-H7
 for pgsql-admin@arkaria.postgresql.org; Thu, 17 Jul 2014 19:25:01 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1X7rIa-0006ZF-4E
 for pgsql-admin@postgresql.org; Thu, 17 Jul 2014 19:25:00 +0000
Received: from mail-ie0-x22b.google.com ([2607:f8b0:4001:c03::22b])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1X7rIW-0002eC-05
 for pgsql-admin@postgresql.org; Thu, 17 Jul 2014 19:24:58 +0000
Received: by mail-ie0-f171.google.com with SMTP id at1so3627644iec.16
 for <pgsql-admin@postgresql.org>; Thu, 17 Jul 2014 12:24:54 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type;
 bh=dpRpOAHXSM0dCPpnGhjakiqEmX407Vge8xSP0W8SxR4=;
 b=IPNaY3JY1V7Nvw8NFYWoaTxfzgjADPYqNIW6LgjaAg3YaTm4aXFfP9VilnHt/le9Xg
 3E0jXBVm64KMtV8ZJRfkynz9qmdDTnkxvsO9NfJMntw3XgWNajM4164m9A0rRARpzULh
 PS9TkYA9DKCvlUO4AzXcRonQsV2efY4af+vEb6QmJuTB8dnkR2zD6Lpi9IaafdU6GGuG
 mPmOlClsAoFFamCG1I5gS9zey3D5kFzjRZpi2DhAfBm44PI7dPyxP7A5HqqzlabT84xU
 faGyNwHW0UohIJb+yGiFp6NK0EKtGASj6/JK4FJ66dInw9o8ObxdplyM5ukyddDMDFAU
 s7bA==
X-Received: by 10.43.64.77 with SMTP id xh13mr539780icb.68.1405625093922;
 Thu, 17 Jul 2014 12:24:53 -0700 (PDT)
Received: from [192.168.254.2] (h77.89.102.166.static.ip.windstream.net.
 [166.102.89.77])
 by mx.google.com with ESMTPSA id u6sm9169352igs.12.2014.07.17.12.24.52
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 17 Jul 2014 12:24:53 -0700 (PDT)
Message-ID: <53C822FF.4070301@gmail.com>
Date: Thu, 17 Jul 2014 15:24:47 -0400
From: John Scalia <jayknowsunix@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Scott Whitney <swhitney@journyx.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Trying to recover a corrupted database
References: <53C81F98.9070405@gmail.com>
 <984033755.512124.1405624595840.JavaMail.zimbra@mail.int.journyx.com>
In-Reply-To: 
 <984033755.512124.1405624595840.JavaMail.zimbra@mail.int.journyx.com>
Content-Type: multipart/alternative;
 boundary="------------070203070202010901040004"
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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

We're only working with a single database on this system, and yes, I attempted a pg_dump earlier today on just one single table - it failed (I know pg_dump and pg_basebackup use 
different mechanisms.) Mind you it's a large table with 154,000 rows in it. The overall database is somewhere around 43GB.

On 7/17/2014 3:16 PM, Scott Whitney wrote:
> a) How many databases do you have?
> b) Have you tried to pg_dump the database(s) to see if that succeeds?
>
> If you _can_ pg_dump (which you might or might not be able to do), you could re-init the cluster and restore.
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>     Hi all,
>
>     You may have seen my post from yesterday about our production database getting corrupted. Well, this morning we brought the system down to single user and ran an fsck which did
>     report some drive errors. We repeated until no additional errors were reported. Then, we brought the system back to multi-user status and ran a successful pg_basebackup on the
>     broken database. Since then we restarted the database and a ps -ef result looks like:
>
>     /usr/pgsql-9.2/bin/postmaster -D /opt/datacenter -o -c zero_damaged_pages=true -i -N 384 -p 5431
>
>     After the Db started up, we ran a VACUUM FULL ANALYZE which ran for about 3 hours, but the database is still showing the same type of errors in its log: invalid page header in
>     block 29718... etc. What disturbed me a little, is that I don't think the zero_damaged_pages got applied. Checking the pg_settings table, we got:
>
>     select name, setting, boot_val, reset_val from pg_settings where name = 'zero_damaged_pages';
>                        name             |  setting  |  boot_val  | reset_val
>     ---------------------------------------------------------------------------------
>       zero_damaged_pages  |  on          | off              | on
>
>     Now, my colleague ran this after he tried running some operations again after I told him how to set zero_damaged_pages again. He swears that that it was on when the first VACUUM
>     FULL ANALYZE was run, but I'm not as sure. Plus, I don't understand why the boot_val shows as off. In any event, as we're still getting log errors like before, I don't really
>     know
>     what to try next other than rerunning the VACUUM FULL again. Help?
>     --
>     Jay
>
>
>
>     -- 
>     Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-admin
>
>


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

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    We're only working with a single database on this system, and yes, I
    attempted a pg_dump earlier today on just one single table - it
    failed (I know pg_dump and pg_basebackup use different mechanisms.)
    Mind you it's a large table with 154,000 rows in it. The overall
    database is somewhere around 43GB.<br>
    <br>
    <div class="moz-cite-prefix">On 7/17/2014 3:16 PM, Scott Whitney
      wrote:<br>
    </div>
    <blockquote
cite="mid:984033755.512124.1405624595840.JavaMail.zimbra@mail.int.journyx.com"
      type="cite">
      <div style="font-family: times new roman, new york, times, serif;
        font-size: 12pt; color: #000000">
        <div>
          <div>a) How many databases do you have?</div>
          <div>b) Have you tried to pg_dump the database(s) to see if
            that succeeds?</div>
          <div><br>
          </div>
          <div>If you _can_ pg_dump (which you might or might not be
            able to do), you could re-init the cluster and restore.</div>
        </div>
        <div><br>
        </div>
        <hr id="zwchr">
        <blockquote style="border-left:2px solid
#1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;">Hi
          all,<br>
          <div><br>
          </div>
          You may have seen my post from yesterday about our production
          database getting corrupted. Well, this morning we brought the
          system down to single user and ran an fsck which did <br>
          report some drive errors. We repeated until no additional
          errors were reported. Then, we brought the system back to
          multi-user status and ran a successful pg_basebackup on the <br>
          broken database. Since then we restarted the database and a ps
          -ef result looks like:<br>
          <div><br>
          </div>
          /usr/pgsql-9.2/bin/postmaster -D /opt/datacenter -o -c
          zero_damaged_pages=true -i -N 384 -p 5431<br>
          <div><br>
          </div>
          After the Db started up, we ran a VACUUM FULL ANALYZE which
          ran for about 3 hours, but the database is still showing the
          same type of errors in its log: invalid page header in <br>
          block 29718... etc. What disturbed me a little, is that I
          don't think the zero_damaged_pages got applied. Checking the
          pg_settings table, we got:<br>
          <div><br>
          </div>
          select name, setting, boot_val, reset_val from pg_settings
          where name = 'zero_damaged_pages';<br>
                             name             |  setting  |  boot_val  |
          reset_val<br>
---------------------------------------------------------------------------------<br>
            zero_damaged_pages  |  on          | off              | on<br>
          <div><br>
          </div>
          Now, my colleague ran this after he tried running some
          operations again after I told him how to set
          zero_damaged_pages again. He swears that that it was on when
          the first VACUUM <br>
          FULL ANALYZE was run, but I'm not as sure. Plus, I don't
          understand why the boot_val shows as off. In any event, as
          we're still getting log errors like before, I don't really
          know <br>
          what to try next other than rerunning the VACUUM FULL again.
          Help?<br>
          --<br>
          Jay<br>
          <div><br>
          </div>
          <br>
          <div><br>
          </div>
          -- <br>
          Sent via pgsql-admin mailing list (<a class="moz-txt-link-abbreviated" href="mailto:pgsql-admin@postgresql.org">pgsql-admin@postgresql.org</a>)<br>
          To make changes to your subscription:<br>
          <a class="moz-txt-link-freetext" href="http://www.postgresql.org/mailpref/pgsql-admin">http://www.postgresql.org/mailpref/pgsql-admin</a><br>
        </blockquote>
        <div><br>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------070203070202010901040004--