Re: Data corruption - Mailing list pgsql-admin



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

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

--

Thanks,

Vijay

Mumbai, India