Re: Data corruption

This is very nasty error scenario .

You can try by faking the clog blocks . Anyways your data is corrupted and if you don’t have any back up so it’s lost .

Thanks
Soumitra

Sent from my iPhone

On 21-Jul-2021, at 10:02 PM, Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com> wrote:


[https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif][https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif]On Wed, 21 Jul 2021 at 21:44, Konrad Garus <konrad(dot)garus(at)gmail(dot)com<mailto: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