Re: Data corruption

On Wed, 21 Jul 2021 at 21:44, Konrad Garus <konrad(dot)garus(at)gmail(dot)com> 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.
>

I have not tried this (not sure to simulate corruption on a laptop) , but
,maybe the below extension and demo, help to see if how much of it has got
corrupted ?
Physical recovery with pg_filedump (alexey-n-chernyshov.github.io)
<https://alexey-n-chernyshov.github.io/blog/physical-recovery-with-pg_filedump.html>

Also, does pg_dump on those relations complete? or does it throw errors and
abort?

--
Thanks,
Vijay
Mumbai, India