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(at)comcast(dot)net]
> Sent: Wednesday, April 08, 2009 10:10 PM
> To: pgsql-general(at)postgresql(dot)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(at)comcast(dot)net
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997