Re: pg_xlog disk full error, i need help

On Mon, 28 Mar 2005 08:46:09 -0300, Gustavo Franklin Nóbrega - Planae
<gfnobrega(at)planae(dot)com(dot)br> wrote:
> Hi Janning!
>
> You need to expand your pg_xlog partition. If you use reiserfs, you can
> do this with resize_reiserfs. If you use ext2/ext3 you may try reise2fs.
>
> If you need to repartition your filesystem, by myself experience, I
> recommend to you to use LVM. With LVM, you can expand easily, add more disk
> to you volume group and grow your logical volume. This already saved me some
> hours of maintenance a time.
>
> Atenciosamente,
>
> Gustavo Franklin Nóbrega
> Infraestrutura e Banco de Dados
> Planae Tecnologia da Informação
> (+55) 14 2106-3514
> http://www.planae.com.br
> ----- Original Message -----
> From: "Janning Vygen" <vygen(at)gmx(dot)de>
> To: <pgsql-general(at)postgresql(dot)org>
> Sent: Monday, March 28, 2005 7:19 AM
> Subject: [GENERAL] pg_xlog disk full error, i need help
>
> > Hi,
> >
> > i do a nightly CLUSTER and VACUUM on one of my production databases.
> >
> > Yesterday in the morning the vacuum process was still running after 8
> > hours.
> > That was very unusal and i didnt know exactly what to do. So i tried to
> > stop
> > the process. After it didnt work i killed -9 the Vacuum process. I
> > restarted
> > the database and everything worked fine again. I did know that this was
> > NOT
> > a good idea but i had to fined a quick solution and it did work at least.
> >
> > Tonight know something very strange did happen before or while the
> > clustering did run:
> >
> > PANIC: could not write to file
> > "/home/postgres/data/pg_xlog/xlogtemp.6434":
> > No space left on device
> > server closed the connection unexpectedly
> > This probably means the server terminated abnormally before or while
> > processing the request. connection to server was lost
> > WARNING: terminating connection because of crash of another server
> > process
> > DETAIL: The postmaster has commanded this server process to roll back the
> > current transaction and exit, because another server process exited
> > abnormally and possibly corrupted shared memory.
> > HINT: In a moment you should be able to reconnect to the database and
> > repeat your command.
> > server closed the connection unexpectedly
> > This probably means the server terminated abnormally before or while
> > processing the request. connection to server was lost
> >
> > My disk was running full with 100 GB (!) of data/pg_xlog/ files. I tried
> > to
> > delete some files on the same partition after which i had 3 GB free space
> > again. the i tried to start the postmaster:
> >
> > the startup process logged this:
> >
> > LOG: database system shutdown was interrupted at 2005-03-28 09:33:15 CEST
> > LOG: checkpoint record is at F/EE0F0010
> > LOG: redo record is at F/EC007900; undo record is at 0/0; shutdown FALSE
> > LOG: next transaction ID: 46558173; next OID: 58970
> > LOG: database system was not properly shut down; automatic recovery in
> > progress
> > LOG: redo starts at F/EC007900
> >
> > looks fine as it says "automatic recovery in progress" but there are no
> > more
> > log entries since startup and my process table says:
> > 8495 pts/0 S 0:00 /usr/local/pgsql/bin/postmaster -D
> > /home/postgres/data
> > 8498 pts/0 S 0:00 postgres: stats buffer process
> > 8499 pts/0 S 0:00 postgres: stats collector process
> > 8500 pts/0 D 5:15 postgres: startup subprocess
> >
> > and top says
> > 8500 postgres 15 0 131m 131m 131m D 18.9 6.5 5:18.26 postmaster
> >
> > so the postmaster is still working.
> >
> > How long will it work on this problem? Can i expect to have everything
> > working correctly after this startup process or shoul di stop it and use a
> > backup (which i hope is useful and not corrupt)
> >
> > I am kind of helpless in this situation as i dont know much of all the
> > underlying storage, WAL and xlog things. Maybe i could just delete all
> > files
> > in this directory?
> >
> > Can anybody give me some hints what to do or how to ask?
> >
> > i am really desperate at the moment.
> >
> > kind regards,
> > Janning
> >
> > please excuse bad english and typos. i am kind of nervous at the moment.

Or free up space by deleting something non-critical from that partition.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand http://netllama.linux-sxs.org