Re: database corruption - Mailing list pgsql-general

From Jeff Brenton
Subject Re: database corruption
Date
Msg-id F489AB573A749146B33461ECE080913A09DF6862@EXCHANGE-1.sandvine.com
Whole thread Raw
In response to Re: database corruption  (Adrian Klaver <aklaver@comcast.net>)
Responses Re: database corruption
Re: database corruption
List pgsql-general
There are no filesystem level content size restrictions that I am aware
of on this system.  The user pgsql should have full access to the
filesystems indicated except for the root filesystem.

Where is the temporary location?  I am searching around to see if I can
specify it anywhere in the config files but can't seem to find anything
which leads me to believe that its part of the postgres data directory.


-----Original Message-----
From: Adrian Klaver [mailto:aklaver@comcast.net]
Sent: Wednesday, April 08, 2009 10:10 PM
To: pgsql-general@postgresql.org
Cc: Jeff Brenton
Subject: Re: [GENERAL] database corruption

On Wednesday 08 April 2009 6:32:06 pm Jeff Brenton wrote:
> I've encountered some db corruption after restarting postgres on my
> database server running 8.2.4.  I think that postgres did not shut
down
> cleanly.  Postgres started appropriately but crashed 45 minutes later.
> I used pg_resetxlog after the crash to get the db to start again but
it
> appears that the database is not running properly now.  When users try
> to access some of the tables in the db they get the error below;
>
>
>
> ERROR:  index "testrun_log_pkey" contains unexpected zero page at
block
> 3155408
>
> HINT:  Please REINDEX it.}> <SQL environment diagnostic: no diagnostic
> record
>
> SQL connection is null
>
> SQL statement diagnostic: XX002 7 {Error while executing the query;
>
> ERROR:  index "testrun_log_pkey" contains unexpected zero page at
block
> 3155408
>
> HINT:  Please REINDEX it.}
>
>
>
> I've attempted to re-index the pkey listed but after an hour it fails
> with
>
>
>
> REINDEX INDEX testrun_log_pkey;
>
>
>
> ERROR:  could not write block 1832079 of temporary file: No space left
> on device
>
> HINT:  Perhaps out of disk space?
>
>
>
> There is currently 14GB free on the disk that postgres is installed
on.
> Does anyone know what I can do to get the db up and running again?

I guess the first question is, does the db have permissions(access) to
all that
space?

>
>
>
> /dev/amrd0s1a    3.9G    2.7G    898M    75%    /
>
> /dev/amrd0s1e    115G     43G     63G    40%    /backup
>
> /dev/amrd1s1d    133G    748M    121G     1%    /wal
>
> /dev/amrd2s1d    663G    596G     14G    98%    /db
>
> /dev/amrd0s1d    3.9G    184M    3.4G     5%    /var



--
Adrian Klaver
aklaver@comcast.net

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: database corruption
Next
From: "Jeff Brenton"
Date:
Subject: Re: database corruption