[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: locate DB corruption
From:       Dave Peticolas <dave () krondo ! com>
Date:       2018-08-31 15:51:59
Message-ID: CAPRbp07Pq-CkkXqg0et+XrG+Hbb5St+7XPFK_=u3qtO=3HTnRg () mail ! gmail ! com
[Download RAW message or body]

On Fri, Aug 31, 2018 at 8:14 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 08/31/2018 08:02 AM, Dave Peticolas wrote:
> > Hello, I'm running into the following error running a large query on a
> > database restored from WAL replay:
> >
> > could not access status of transaction 330569126
> > DETAIL: Could not open file "pg_clog/0C68": No such file or directory
>
>
> Postgres version?
>

Right! Sorry, that original email didn't have a lot of info. This is 9.6.9
restoring a backup from 9.6.8.


> Where is the replay coming from?
>

From a snapshot and WAL files stored in Amazon S3.



> >
> > Searches of this mailing list seem to indicate this means a database row
> > has been corrupted. How would I go about locating the point of
> > corruption in order to fix?
>
> To me it looks like what it says, the transaction file could not be
> found. From Postgres 9.6 --> 10 pg_clog became pg_xact.
>
> Are you sure you are not working across versions?
>

I am sure, they are all 9.6.


> If not do pg_clog/ and 0C68 actually exist?
>

pg_clog definitely exists, but 0C68 does not. I think I have subsequently
found the precise row in the specific table that seems to be the problem.
Specifically I can select * from TABLE where id = BADID - 1 or id = BADID +
1 and the query returns. I get the error if I select the row with the bad
ID.

Now what I'm not sure of is how to fix.

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_quote"><div dir="ltr">On Fri, Aug 31, 2018 at 8:14 \
AM Adrian Klaver &lt;<a \
href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex">On 08/31/2018 08:02 AM, Dave \
Peticolas wrote:<br> &gt; Hello, I&#39;m running into the following error running a \
large query on a <br> &gt; database restored from WAL replay:<br>
&gt; <br>
&gt; could not access status of transaction 330569126<br>
&gt; DETAIL: Could not open file &quot;pg_clog/0C68&quot;: No such file or \
directory<br> <br>
<br>
Postgres version?<br></blockquote><div><br></div><div>Right! Sorry, that original \
email didn&#39;t have a lot of info. This is 9.6.9 restoring a backup from \
9.6.8.</div><div>  </div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"> Where is the replay coming \
from?<br></blockquote><div><br></div><div>From a snapshot and WAL files stored in \
Amazon S3.</div><div><br></div><div>  </div><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> &gt; <br>
&gt; Searches of this mailing list seem to indicate this means a database row <br>
&gt; has been corrupted. How would I go about locating the point of <br>
&gt; corruption in order to fix?<br>
<br>
To me it looks like what it says, the transaction file could not be <br>
found. From Postgres 9.6 --&gt; 10 pg_clog became pg_xact.<br>
<br>
Are you sure you are not working across \
versions?<br></blockquote><div><br></div><div>I am sure, they are all 9.6.</div><div> \
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">If not do pg_clog/ and 0C68 actually \
exist?<br></blockquote><div><br></div><div>pg_clog definitely exists, but 0C68 does \
not. I think I have subsequently found the precise row in the specific table that \
seems to be the problem. Specifically I can select * from TABLE where id = BADID - 1 \
or id = BADID  + 1 and the query returns. I get the error if I select the row with \
the bad ID.</div><div><br></div><div>Now what I&#39;m not sure of is how to \
fix.</div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]