Re: DB corruption - Mailing list pgsql-performance

From Tom Lane
Subject Re: DB corruption
Date
Msg-id 21158.1521817736@sss.pgh.pa.us
Whole thread Raw
In response to DB corruption  (Akshay Ballarpure <akshay.ballarpure@tcs.com>)
List pgsql-performance
Akshay Ballarpure <akshay.ballarpure@tcs.com> 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


pgsql-performance by date:

Previous
From: Peter
Date:
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)
Next
From: Peter
Date:
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)