From: soumitra bhandary <soumitra.bhandary@hotmail.com>
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Cc: Konrad Garus <konrad.garus@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Data corruption
Date: Wed, 21 Jul 2021 17:03:23 +0000
Message-ID: <MA1PR01MB0634DD078065C4D8E14DDB64E0E39@MA1PR01MB0634.INDPRD01.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAM+6J97ueqECOkQbKtLp2o1A-Ys1mYAfhP0qpaiKbuv7QRa_pA@mail.gmail.com>
References: <CAGHa9HXONb+ayki84FrNW1cVLuWrkv_=1q+HAngq923GBvnUgw@mail.gmail.com>
<CAM+6J97ueqECOkQbKtLp2o1A-Ys1mYAfhP0qpaiKbuv7QRa_pA@mail.gmail.com>
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.github@gmail.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.garus@gmail.com<mailto: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)<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: soumitra.bhandary@hotmail.com, vijaykumarjain.github@gmail.com, konrad.garus@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Data corruption
In-Reply-To: <MA1PR01MB0634DD078065C4D8E14DDB64E0E39@MA1PR01MB0634.INDPRD01.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox