Nextcloud eats all postgresql connections

Nextcloud version (eg, 20.0.5): 20.0.7
Operating system and version (eg, Ubuntu 20.04): Ubuntu 20.04
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.41
PHP version (eg, 7.4): 7.4
Database: Postgresql 12

The issue you are facing:
Nextcloud eats all postgresql connections and then it stops working until connections clear on their own

Is this the first time you’ve seen this error? (Y/N): No, it happens multiple times

Steps to replicate it:

  1. My guess is that this is triggered by background jobs (cron.php) which is set to run every 5 minutes

The output of your Nextcloud log in Admin > Logging:

"Message":"Failed to connect to the database: An exception occurred in driver: SQLSTATE[08006] [7] FATAL:  remaining connection slots are reserved for non-replication superuser connections"

Additional info:

Postgresql connections are filled by same 2 queries:

  1. SELECT * FROM "oc_appconfig" → which is in an idle state with wait_event = ClientRead

  2. UPDATE "oc_appconfig" SET "configvalue" = '1623767609' WHERE ("appid" = 'user_ldap') AND ("configkey" = '_lastChange') AND (("configvalue" IS NULL) OR ("configvalue" <> '1623767609')) → which is in active state with wait_event_type = Lock; this looks like it’s updating the last time LDAP was checked for changes and some of these queries take even 6 minutes when the DB connections build up

My question is, how can I fix this and prevent the DB from reaching the max connections? Could it be a Nextcloud bug that doesn’t close the DB connections or maybe a misconfiguration on the postgresql server?

1 Like

Hey @Emi,

Did you find any fix for this ? I’m seeing the same behaviour on NC 21.0.5 with NGINX.

Hi @hmihov,

Unfortunately no, I haven’t manage to find the exact cause or some fix for this problem. When this started to happen one of the first things I did was upgrade Nextcloud. After that the problem stopped for a while, but came back after a month or so. Then I upgraded again if I recall correctly (running 21.0.4 at the time of writing this comment) and I don’t think it happened again ever since. At least not lately. But if you find any additional info on this issue I would be grateful for sharing.

Also some things to note: I manage multiple Nextcloud instances with (LDAP auth), but only this one instance encountered this issue. But it is also the biggest one I manage: 550 - 600 users coming from LDAP, ~100 active within an hour span, 17TB of data.

oh, I regret now skipping the 21.0.4 version …

How many DB connection have you allowed to the database ?
I have similar sized instance, maybe about 450 users with ~110 online during the day, 40TB of data.

I’ve had to allow 400 DB connection to deal with the spikes of idle connections.

Only 150 and most of them are unused if the problem doesn’t appear. We didn’t increase the number of connections as we thought they will be eaten no matter how many we allow and instead tried to find/fix the problem, with accent on “tried”.

I think we have another issue of eating/not closing connections too. I believe it started surfacing after we added more SMB storages and running the notify as a service. Might be separate than your issue though.