Subject: Re: Data corruption
To: pgsql-admin@lists.postgresql.org
References: 
 <CAGHa9HXONb+ayki84FrNW1cVLuWrkv_=1q+HAngq923GBvnUgw@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <608090c5-0972-9fee-fb6f-9462ee3fc941@gmail.com>
Date: Wed, 21 Jul 2021 13:33:45 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGHa9HXONb+ayki84FrNW1cVLuWrkv_=1q+HAngq923GBvnUgw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------E763DE98E7620E029883FE65"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/608090c5-0972-9fee-fb6f-9462ee3fc941%40gmail.com>
Precedence: bulk

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

I've zeroes out Pg 8.4 pc_clog files using dd (instructions are out there on 
the web) without any visible negative effect.

Naturally, YMMV.

On 7/21/21 11:14 AM, Konrad Garus wrote:
> Hello,
>
> we have a database that apparently got corrupted in a crash a few months 
> ago, long enough that there is no "good" backup.
>
> There are at least two tables occasionally emitting errors like this:
>
> ERROR: invalid page header in block 39684 of relation base/12926/32397 
> while performing SQL query: SQL DML: insert into foo (a, b, c) values (?, 
> ?, ?) | PARAMETERS: foo, bar, baz: org.postgresql.util.PSQLException: 
> ERROR: invalid page header in block 39684 of relation base/12926/32397
>
> The files in question (e.g. 32397) correspond to the table itself, not an 
> index. The larger of these tables has two files (32397 & 32397.1), almost 
> 2G total.
>
> To make things worse, vacuum ends with:
>
> ERROR:  could not access status of transaction 2828785257
> DETAIL:  Could not open file "pg_clog/0A89": No such file or directory.
>
> That file is nowhere to be found.
>
> When I do this:
>
> set zero_damaged_pages = on;
> select count(*) from foo
>
> It shows 3 errors like this:
>
> WARNING:  invalid page header in block 78550 of relation base/12926/31642; 
> zeroing out page
> WARNING:  invalid page header in block 78551 of relation base/12926/31642; 
> zeroing out page
> WARNING:  invalid page header in block 78552 of relation base/12926/31642; 
> zeroing out page
>
> One idea I found is to generate a zero file for the missing clog, set 
> zero_damaged_pages=on, then vacuum or recreate the table. Looks like it 
> might work, but I'm trying to understand the implications.
>
> 1. When I generate a zero clog file like this, what impact will it have on 
> everything? Can it break anything else in the database, introduce some 
> kind of inconsistency with other (correctly committed) table data?
>
> 2. How can I gauge how much actual data (e.g. how many rows) will be lost 
> by doing this (zero clog file + vacuum/recreate table)?
>
> 3. Is there some place I can look to reliably tell how many rows the table 
> should have, so I can compare to how many I can read after setting 
> zero_damaged_pages?
>
> 4. Is there any way at all to recover/repair the corrupted data at this point?
>
> -- 
> Konrad Garus

-- 
Angular momentum makes the world go 'round.

--------------E763DE98E7620E029883FE65
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    I've zeroes out Pg 8.4 pc_clog files using dd (instructions are out
    there on the web) without any visible negative effect.<br>
    <br>
    Naturally, YMMV.<br>
    <br>
    <div class="moz-cite-prefix">On 7/21/21 11:14 AM, Konrad Garus
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAGHa9HXONb+ayki84FrNW1cVLuWrkv_=1q+HAngq923GBvnUgw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hello,
        <div><br>
        </div>
        <div>we have a database that apparently got corrupted in a crash
          a few months ago, long enough that there is no "good" backup.</div>
        <div><br>
        </div>
        <div>There are at least two tables occasionally emitting errors
          like this:</div>
        <div><br>
        </div>
        <div>ERROR: invalid page header in block 39684 of relation
          base/12926/32397 while performing SQL query: SQL DML: insert
          into foo (a, b, c) values (?, ?, ?) | PARAMETERS: foo, bar,
          baz: org.postgresql.util.PSQLException: ERROR: invalid page
          header in block 39684 of relation base/12926/32397<br>
        </div>
        <div>
          <div><br>
          </div>
          <div>The files in question (e.g. 32397) correspond to the
            table itself, not an index. The larger of these tables has
            two files (32397 &amp; 32397.1), almost 2G total.</div>
          <div><br>
          </div>
          <div>To make things worse, vacuum ends with:</div>
          <div><br>
          </div>
          <div>ERROR:  could not access status of transaction 2828785257<br>
            DETAIL:  Could not open file "pg_clog/0A89": No such file or
            directory.<br>
          </div>
          <div><br>
          </div>
          <div>That file is nowhere to be found.</div>
          <div><br>
          </div>
          <div>When I do this:</div>
          <div><br>
          </div>
          <div>set zero_damaged_pages = on;</div>
          <div>select count(*) from foo</div>
          <div><br>
          </div>
          <div>It shows 3 errors like this:</div>
          <div><br>
          </div>
          <div>WARNING:  invalid page header in block 78550 of relation
            base/12926/31642; zeroing out page<br>
            WARNING:  invalid page header in block 78551 of relation
            base/12926/31642; zeroing out page<br>
            WARNING:  invalid page header in block 78552 of relation
            base/12926/31642; zeroing out page<br>
          </div>
          <div><br>
          </div>
          <div>One idea I found is to generate a zero file for the
            missing clog, set zero_damaged_pages=on, then vacuum or
            recreate the table. Looks like it might work, but I'm trying
            to understand the implications.</div>
          <div><br>
          </div>
          <div>1. When I generate a zero clog file like this, what
            impact will it have on everything? Can it break anything
            else in the database, introduce some kind of inconsistency
            with other (correctly committed) table data?</div>
          <div><br>
          </div>
          <div>2. How can I gauge how much actual data (e.g. how many
            rows) will be lost by doing this (zero clog file +
            vacuum/recreate table)?</div>
          <div><br>
          </div>
          <div>3. Is there some place I can look to reliably tell how
            many rows the table should have, so I can compare to how
            many I can read after setting zero_damaged_pages?</div>
          <div><br>
          </div>
          <div>4. Is there any way at all to recover/repair the
            corrupted data at this point?</div>
          <div>
            <div><br>
            </div>
            -- <br>
            <div dir="ltr" class="gmail_signature"
              data-smartmail="gmail_signature">Konrad Garus</div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------E763DE98E7620E029883FE65--