Re: DB corruption

[Tom Lane]

Akshay Ballarpure  writes:
> I have a query on DB corruption. Is there any way to recover from it 
> without losing data ?

You've already lost data, evidently.

> Starting postgresql service: [ OK ]
> psql: FATAL: index "pg_authid_rolname_index" contains unexpected zero page 
> at block 0
> HINT: Please REINDEX it.

This is not good.  It'd be possible to reindex that index, certainly,
but the question is what other files have also been clobbered.

> psql: FATAL: "base/11564" is not a valid data directory
> DETAIL: File "base/11564/PG_VERSION" does not contain valid data.
> HINT: You might need to initdb.

Based on the OID I'm going to guess that this is from an attempt to
connect to the "postgres" database.  (I'm also going to guess that
you're running 8.4.x, because any later PG version would have a higher
OID for "postgres".)  Can you connect to any other databases?  If so,
do their contents seem intact?  If you're really lucky, meaning (a) the
damage is confined to that DB and (b) you didn't keep any important
data in it, then dropping and recreating the "postgres" DB might be
enough to get you out of trouble.  But pg_authid_rolname_index is
a cluster-global index, not specific to the "postgres" DB, so the
fact that it too seems to be damaged is not promising.

TBH your best bet, if the data in this installation is valuable and
you don't have adequate backups, is to hire a professional data
recovery service --- there are several companies that specialize in
getting as much out of a corrupted PG installation as possible.
(See https://www.postgresql.org/support/professional_support/ for
some links.)  You should then plan on updating to some newer PG
release; 8.4.x has been out of support for years, and there are lots
of known-and-unfixed bugs in it.

regards, tom lane

Re: DB corruption

[Michael Paquier]

On Fri, Mar 23, 2018 at 01:29:35PM +0530, Akshay Ballarpure wrote:
> I have a query on DB corruption. Is there any way to recover from it 
> without losing data ?

Corrupted pages which need to be zeroed in order to recover the rest is
data lost forever, except if you have a backup you can rollback to.
Please see here for some global instructions about how to deal with such
situations:
https://wiki.postgresql.org/wiki/Corruption

First take a deep breath, and take the time to read and understand it.

> Notice: The information contained in this e-mail
> message and/or attachments to it may contain 
> confidential or privileged information. If you are 
> not the intended recipient, any dissemination, use, 
> review, distribution, printing or copying of the 
> information contained in this e-mail message 
> and/or attachments to it are strictly prohibited. If 
> you have received this communication in error, 
> please notify us by reply e-mail or telephone and 
> immediately and permanently delete the message 
> and any attachments. Thank you

This is a public mailing list.
--
Michael


signature.asc
Description: PGP signature