[Pkg-postgresql-public] Bug#688087: Too many open files in system

[Giuseppe Sacco]

Package: postgresql-8.4
Version: 8.4.12-0squeeze1
Severity: important

Every two weeks, this database server stop working because of maximum
open files reached by postgres user. This is an excerpt of
postgresql.log

2012-09-18 17:16:31 CEST LOG:  out of file descriptors: Too many open files in system; release and retry
2012-09-18 17:16:31 CEST STATEMENT:  UPDATE agenzia.systemdatabydocument SET creatingUser=$1, creatingDate=$2,lastChangeUser=$3,lastChangeDate=$4,description=$5 WHERE docId=$6 AND seqNr=$7
[...]
2012-09-18 17:16:34 CEST LOG:  out of file descriptors: Too many open files in system; release and retry
2012-09-18 17:16:34 CEST STATEMENT:  SELECT p.proname,p.oid  FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n  WHERE p.pronamespace=n.oid AND n.nspname='pg_catalog' AND ( proname = 'lo_open' or proname = 'lo_close' or proname = 'lo_creat' or proname = 'lo_unlink' or proname = 'lo_lseek' or proname = 'lo_tell' or proname = 'loread' or proname = 'lowrite' or proname = 'lo_truncate'
[...]
2012-09-18 17:16:44 CEST LOG:  could not accept new connection: Too many open files in system
2012-09-18 17:16:44 CEST LOG:  could not accept new connection: Too many open files in system
2012-09-18 17:16:44 CEST LOG:  could not accept new connection: Too many open files in system
2012-09-18 17:16:44 CEST LOG:  could not accept new connection: Too many open files in system
2012-09-18 17:16:44 CEST LOG:  out of file descriptors: Too many open files in system; release and retry
2012-09-18 17:16:44 CEST FATAL:  could not open file "global/pg_database": Too many open files in system
2012-09-18 17:16:44 CEST LOG:  autovacuum launcher process (PID 12943) exited with exit code 1
2012-09-18 17:16:44 CEST LOG:  terminating any other active server processes
2012-09-18 17:16:44 CEST WARNING:  terminating connection because of crash of another server process
2012-09-18 17:16:44 CEST DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and po
ssibly corrupted shared memory.
2012-09-18 17:16:44 CEST HINT:  In a moment you should be able to reconnect to the database and repeat your command.

When this happen, it is not possibile to stop the database using the
pg_ctlcluster utility:

agenzia-f07:~# pg_ctlcluster 8.4 main stop
pg_ctl: il server non si sta spegnendo

(In English: "server is not stopping"), so I have to manually kill every
postgres process before restarting the database using pg_ctlcluster
again.

When I try to stop it, it write in logfile:
2012-09-19 09:29:34 CEST FATAL:  the database system is in recovery mode

What does it means "Too many open files in system"? Is it referring to
postgresql system or to operating system?

I checked /etc/security/limits.conf and found no lines about postgres
user. Is this made on purpose? Would changing the maximum number of open
files for user postgres change anything? Is this a known problem, at
first?

Thanks,
Giuseppe