postgresql won't restart. Cannot allocate memory. Must reboot. Any alternative short of reboot?

From: Mitchell Laks <mlaks(at)post(dot)harvard(dot)edu>
To: pgsql-admin(at)postgresql(dot)org
Subject: postgresql won't restart. Cannot allocate memory. Must reboot. Any alternative short of reboot?
Date: 2009-07-06 16:11:33
Message-ID: 20090706161133.GA20160@earthlink.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I am a very happy user of Postgresql! Thank you all for this marvelous work!

I have an older linux server running debian etch 4.0 using the old postgresql-7.4.

There is essentially a single application running on that machine which serves up data from a single postgresql database.
There is no new data added to the database. It is simply serving up legacy information. The server does nothing else.

I suspect I am working at the limits of the memory capabilities of this machine. It has 1G of ram. My postgresql database has a few very small control tables
and has a single very large table LTA_IDB (the pg_dump of this main table is 1.9G in size ) and
du -sh /var/lib/postgresql/7.4/
5.7G /var/lib/postgresql/7.4/

These are my settings in
postgresql.conf:

shared_buffers = 48000
max_connections = 512
sort_mem = 4096
effective_cache_size = 4000
wal_buffers = 8

------

while in the file

/etc/sysctl.conf
kernel.shmmax = 635000000

----------------------------------

The server works fine most of the time, though occasionally has trouble and my application fails

When I log into the machine I see that Postgresql has closed and the database wont restart.

Thus when I log in to the machine and try to restart postgresql I get this message:

A2006:/home/wustl# /etc/init.d/postgresql-7.4 start
Starting PostgreSQL 7.4 database server: main* The PostgreSQL server failed to start. Please check the log output:
2009-07-06 09:37:56 [1251] FATAL: could not create shared memory segment: Cannot allocate memory
DETAIL: Failed system call was shmget(key=5432001, size=407429120, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 407429120 bytes).
The PostgreSQL documentation contains more information about shared memory configuration.
failed!

Thus interactive restarting the database seems not to work at that point.

However I can easily restart the postgresql database system by rebooting the server itself. Then postgresql will start easily and all is fine.

Question 1:

Is there something I can do besides rebooting the whole server to reset the memory so that postgreql will start up again? What does rebooting do?

Question 2:

I would prefer to do the minimal changes to preserve the working of this system, and avoid this recurrent problem.
Might changing the paramaters help? I would prefer not to have to
add memory to this older system, as I might have trouble matching the memory and the server is far from where I am etc.
If I do need to add memory, what should I set the parameters to?

Question 3:
I run the vacuum program 4 times a day from a cron job.

30 1 * * * vacuumdb --analyze LTA_IDB
30 12 * * * vacuumdb --analyze LTA_IDB
30 17 * * * vacuumdb --analyze LTA_IDB
30 20 * * * vacuumdb --analyze LTA_IDB

where LTA_IDB is the large database table mentioned above.

Is this neccessary for a system that is no longer is storing new information? What does it accomplish? Does it tune it more for the queries?

Thank you so much!

Mitchell

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2009-07-06 16:58:04 Re: Questions on setup and usage
Previous Message Peter Eisentraut 2009-07-06 12:35:11 Re: Make the primary key a multilingual value