Re: locate DB corruption

On Fri, Aug 31, 2018 at 8:14 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.