Re: database corruption

On Wed, 2009-04-08 at 22:14 -0400, Jeff Brenton wrote:
> 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.

Inodes?

>
> 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
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997