[Discuss] PostgreSQL too many files under Ubuntu

[Craig Maloney]

Hey all

Have a puzzle regarding PostgreSQL under Ubuntu 20.04. We're getting
"too many files" under Ubuntu, whereas before under CentOS we didn't
have this issue. My suspicions are with systemd and how it sets ulimit,
but I'm not 100% if that's the issue.

Anyone set up PostgreSQL under Ubuntu and run into this issue, and if
you have do you have pointers on how to fix this?

For reference we're looking into this as a potential fix:

https://serverfault.com/questions/785330/getting-too-many-open-files-error-for-postgres

and setting ulimit under systemd:

https://serverfault.com/a/678861/70780

but anyone with hands-on experience with this would be a huge help.

Thanks in advance.

--
Craig Maloney (cr...@decafbad.net) http://decafbad.net

_______________________________________________
Sponsors:
http://a2hosting.com a2hosting - proudly providing web hosting services for MUG.org
http://altair.com Altair - Altair transforms products and orgs via simulation, optimization and HPC
http://www.avairis.com Avairis, Inc. - Web based medical practice management systems
http://www.penguicon.org Penguicon - Annual convention for Linux, Science fiction and more

Discuss mailing list
http://mug.org/mailman/listinfo/discuss_mug.org

[Craig Maloney]

The error messages we're getting are the following:

note: username and db redacted to [username] and [database]

Lots of:

2020-11-07 23:19:38 UTC [4005788]: [3-1] user=[username],db=[database] LOG:
out of file descriptors: Too many open files in system; release and
retry

And:

2020-11-07 23:56:41 UTC [1015]: [42388-1] user=,db= LOG:could not open
log file "log/postgresql-2020-11-07.log": Too many open files in system
2020-11-07 23:56:41 UTC [955]: [1774-1] user=,db= LOG:could not accept
new connection: Too many open files in system
2020-11-07 23:56:41 UTC [1015]: [42389-1] user=,db= LOG:could not open
log file "log/postgresql-2020-11-07.log": Too many open files in system
2020-11-07 23:56:41 UTC [955]: [1775-1] user=,db= LOG:WAL writer
process (PID 1020) was terminated by signal 6: Aborted
2020-11-07 23:56:41 UTC [955]: [1776-1] user=,db= LOG:terminating any
other active server processes
2020-11-07 23:56:41 UTC [4092063]: [63-1] user=[username],db=[database]
WARNING:terminating connection because of crash of another server
process
2020-11-07 23:56:41 UTC [4092063]: [64-1] user=[username],db=[database]
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.

[James McQuillan]

I'm running Postgres 12 on Ubuntu 20.04 in a test environment (not yet in production).

I just checked and using lsof, I see the postgres process has 2965 files open.  I've not had a problem.

I'm not sure if it matters, but I don't start my postgres via systemd.

This problem interests me, because when I do upgrade the production servers to Ubuntu 20.04, It would be very bad if I start having "too many files" once lots of users start logging in and running queries.

Jim.

[Zachary Wincek]

Craig,

Check to ensure that you have not exhausted the memory of the computer.

• free -g

Check to see whether the logs of the system contain anything
pertinent, such as an invocation of the OoM killer.

• journalctl

You might want to check the limits of the PostgreSQL service.

• systemctl show postgresql-13.service -p LimitNOFILE
• systemctl show postgresql-13.service -p LimitNPROC
• systemctl show postgresql-13.service -p LimitLOCKS

If a limit is too low, then check both the system-specific
configuration and the vendor-supplied configuration.

• less /usr/lib/systemd/system/postgresql-13.service
• less /etc/systemd/system/postgresql-13.service

Trying to reproduce the error on another VM might allow you to
determine whether the problem be from either a unique configuration or
a default configuration.

Good luck,
--
Zachary Wincek

[Craig Maloney]

Thanks! Will give this a peek.