Re: drive failure

On Thu, Mar 31, 2011 at 7:11 PM, Tony Capobianco <tcapobianco(at)prospectiv(dot)com
> wrote:

> We were able to determine through a few of our queries that an index was
> corrupt. We did this through the process of elimination however. As a
> result, I have two questions:
> How can I determine that we have a corrupt index?
>

Yes, this can be achieved by block level checking with pg_dump utility::

pg_dump -d <DB Name> -p <Port> -v >/dev/null

If there is any corrupted indexes or tables exists in the Database, it will
throw an error with block number.

> How can I determine which datafile (5875980.x) is related to which
> tablespace?
>
>
Get oid & Name of the tablespace using below command:

select oid,* from pg_tablespace;

Once you get the oid,tablespace name, you can easily identify the which
datafile is related to which tablespace in "pg_tblspc" directory.

--Raghu Ram

> On Thu, 2011-03-31 at 08:38 -0400, Tony Capobianco wrote:
> > Hello,
> > He had a drive fail in an array and the spare kicked in to replace the
> > failed drive. However, when I query a specific table, I get the below
> > error:
> >
> > ERROR: could not open file
> > "pg_tblspc/16412/PG_9.0_201008051/16419/5875980.7" (target block
> > 2968776487): No such file or directory
> >
> >
> > When I change to this directory, the file in question does not exist.
> > Am I to assume that I'm completely hosed? If the spare kicked in, what
> > happened to the file listed above? Any hints would be most appreciated.
> >
> > Thanks.
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>