Thread: Postmaster crash and failed restart. How much space to allocate.

Hi all,

My postmaster just crashed , it was 7.1release (Debian package version
4).

I found that the postmaster processes was not running and restarted the
postmaster (Unsuccessfully.. )

It appears that it was due to running out of space.
Are there any quick tips for receiving notification of critical errors
such as this, and how much breathing space to leave on the filesystem
for the database transaction logs.

I have included an excerpt from the postmaster log:

2001-08-30 15:01:30 [12298]  DEBUG:  connection: host=127.0.0.1
user=www-data da
tabase=nvplayer
2001-08-30 15:01:34 [12299]  FATAL 2:
ZeroFill(/var/lib/postgres/data/pg_xlog/x
logtemp.12299) failed: No space left on device
Server process (pid 12299) exited with status 512 at Thu Aug 30 15:01:34
2001
Terminating any active server processes...
2001-08-30 15:01:34 [12263]  NOTICE:  Message from PostgreSQL backend:
         The Postmaster has informed me that some other backend  died
abnormally
and possibly corrupted shared memory.
         I have rolled back the current transaction and am       going to
termina
te your database system connection and exit.
         Please reconnect to the database system and repeat your query.
Server processes were terminated at Thu Aug 30 15:01:34 2001
Reinitializing shared memory and semaphores
2001-08-30 15:01:34 [12300]  DEBUG:  database system was interrupted at
2001-08-
30 15:01:33 EST
2001-08-30 15:01:34 [12300]  DEBUG:  CheckPoint record at (0, 365079992)
2001-08-30 15:01:34 [12300]  DEBUG:  Redo record at (0, 365079992); Undo
record
at (0, 0); Shutdown FALSE
2001-08-30 15:01:34 [12300]  DEBUG:  NextTransactionId: 1584877;
NextOid: 179582
2001-08-30 15:01:34 [12300]  DEBUG:  database system was not properly
shut down;
  automatic recovery in progress...
2001-08-30 15:01:34 [12300]  DEBUG:  ReadRecord: record with zero len at
(0, 365080056)
2001-08-30 15:01:34 [12300]  DEBUG:  redo is not required
The Data Base System is starting up
2001-08-30 15:01:36 [12300]  FATAL 2:
ZeroFill(/var/lib/postgres/data/p/usr/lib/postgres
ql/bin/postmaster: Startup proc 12300 exited with status 512 - abort
2001-08-30 15:08:00 [12427]  DEBUG:  database system was shut down at
2001-08-30 15:01:36
  EST
2001-08-30 15:08:00 [12427]  DEBUG:  CheckPoint record at (0, 365080056)
2001-08-30 15:08:00 [12427]  DEBUG:  Redo record at (0, 365080056); Undo
record at (0, 0)
; Shutdown TRUE
2001-08-30 15:08:00 [12427]  DEBUG:  NextTransactionId: 1584877;
NextOid: 179582
2001-08-30 15:08:00 [12427]  FATAL 2:
ZeroFill(/var/lib/postgres/data/pg_xlog/xlogtemp.1
2427) failed: No such file or directory
/usr/lib/postgresql/bin/postmaster: Startup proc 12427 exited with
status 512 - abort


At this point, I checked the space on /var and there were approx 16M free
/dev/sda3             486M  445M   16M  97% /var

I cleaned up a little:
/dev/sda3             486M  254M  206M  56% /var

.. and attempted to start again

Successfully this time:

root@rapt:/var/log[39]$ /etc/init.d/postgresql start
Starting PostgreSQL postmaster.
/usr/lib/postgresql/bin/pg_ctl start -D /var/lib/postgres/data -l
/var/log/postgres.log
postmaster successfully started
2001-08-30 15:22:32 [12845]  DEBUG:  database system was shut down at
2001-08-30 15:01:36 EST
2001-08-30 15:22:32 [12845]  DEBUG:  CheckPoint record at (0, 365080056)
2001-08-30 15:22:32 [12845]  DEBUG:  Redo record at (0, 365080056); Undo
record at (0, 0); Shutdown TRUE
2001-08-30 15:22:32 [12845]  DEBUG:  NextTransactionId: 1584877;
NextOid: 179582
2001-08-30 15:22:32 [12845]  DEBUG:  database system is in production
state

Now my Database is really not all that large:
59M     /var/lib/postgres


==============================
David Stanaway
Personal: david@stanaway.net
Work: david@netventures.com.au

David Stanaway writes:

> It appears that it was due to running out of space.
> Are there any quick tips for receiving notification of critical errors
> such as this, and how much breathing space to leave on the filesystem
> for the database transaction logs.

As you found out for yourself, at least 16 MB are required "ahead of time"
for the transaction log.  On occasion, more space will be needed for
temporary sort files and such.  If you're really operating at 97% disk
full you're going to have an awful lot of performance problems.  Many file
systems are completely losing it at about 85%.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

David Stanaway <david@netventures.com.au> writes:
> My postmaster just crashed , it was 7.1release (Debian package version
> 4).

I'd suggest an update to 7.1.3.  Earlier 7.1 releases have problems with
the WAL logfiles growing unreasonably large during long transactions.

            regards, tom lane