ERROR c.j.a.d.AbstractAppDBDAOImpl-[-1] Error while preparing App DB connection: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

[sheshjee]

Is "min_pool_size" a valid parameter in pgagroal?

[jesperpedersen]

You are looking for https://github.com/agroal/pgagroal/blob/master/doc/CONFIGURATION.md#pgagroal_databases-configuration

[sheshjee]

Thanks for sharing. But the issue is we are using pgagroal with postgresql-13.
We have a 3 node database patroni & consul cluster, which serves multiple clients request.
Sometimes we face an issue: psql Error: too many clients already. When i saw it on grafana, there were 700 active connections ans 300 idle connections at the time of error.
Now all is normal, and active connections are 7 according to SELECT * FROM pg_stat_activity;
This is my pgagroal configuration:
[pgagroal]
host = *
port = 2345

log_type = file
log_level = info
log_path = /var/log/pgagroal/pgagroal.log

max_connections = 900
idle_timeout = 600
validation = off
unix_socket_dir = /tmp
metrics = 9119

[primary]
host = localhost
port = 5432

And, in patroni.yml configuration- max_connection = 1000.

How to resolve this issue?

[jesperpedersen]

Have you tried the flush CLI command ?

[sheshjee]

No I didn't because in pgagroal.conf I already set idle_timeout = 600, I changed the Leader and error resolved but after some time again the same error. The problem is error occurred suddenly and we don't know when it will come again. Moreover, when I allowed max_connection is 1000 then why pg_stat_activity shows only 7 or 10 connections. Where are those rest 990?

…

On Fri, Apr 12, 2024, 6:09 PM Jesper Pedersen ***@***.***> wrote: Have you tried the flush CLI command ? — Reply to this email directly, view it on GitHub <#439 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG64B2TL5VGCGRE52353Y47IXFAVCNFSM6AAAAABGD7NFMOVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TAOJVGY3DK> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[jesperpedersen]

0 means that there is no prefill of connections

[sheshjee]

Also please see my below responses why active connection and total connection are same . Should active connection closes after some time?

[jesperpedersen]

No, because they need Close (F) + Portal (P) for that to happen.

You can use tools like https://github.com/jesperpedersen/pgprtdbg to debug what is going on

[sheshjee]

So will it be closed/released on postgresql side by flush all or on application code side?

[jesperpedersen]

All the way through the stack

[fluca1978]

The max_connections is how much the pooler will handle, but if there are no reasons to open such a large amount, the pool will not. To force/reserve connections, you have to define connection limits in pgagroal_databases.conf.
Also have a look at the output of pgagroal-cli status and pgagroal-cli details to better understand what are those seven connections.

[sheshjee]

postgres=# select count(*) from pg_stat_activity;
count

 7

(1 row)

[postgres@test1 ~]$ pgagroal-cli details
Status: Running
Active connections: 251
Total connections: 251
Max connections: 900

Server: primary
Host: localhost
Port: 5432
State: Primary

Connection 0: Not initialized
Connection 1: Active 2024-03-04 15:07:32 4046585 19 db postgres
Connection 2: Active 2024-03-04 15:07:21 4034583 17 db postgres
Connection 3: Active 2024-03-04 15:07:30 4044494 22 db postgres
Connection 4: Active 2024-03-04 15:07:38 4051689 30 db postgres
Connection 5: Active 2024-03-04 15:07:19 4032297 27 db postgres
Connection 6: Active 2024-03-04 15:07:33 4047947 26 db postgres
Connection 7: Active 2024-03-04 15:07:40 4053841 45 db postgres
Connection 8: Active 2024-03-04 15:07:37 4050946 21 db postgres
Connection 9: Not initialized
Connection 10: Active 2024-03-04 15:07:41 4054815 34 db postgres
Connection 11: Active 2024-03-04 21:22:20 2537150 29 db postgres
..........
and so on, upto 250

[sheshjee]

The surprising thing is Active connections and Total connections are the same which means that once the connection is opened it is not closing as we can see above i.e. Connection 1: Active 2024-03-04

[sheshjee]

And the main issue is
ERROR c.j.a.d.AbstractAppDBDAOImpl-[-1] Error while preparing App DB connection: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

[sheshjee]

One more thing that bothers me is why pg_stat_activity shows only 7 connections whereas, pgagroal-cli details shows 251 connections.

[sheshjee]

@fluca1978 ..how much connection limit should I define in pgagroal_databases.conf?

[sheshjee]

I have not configured pgagroal_database.conf. I have only configured pgagroal_conf which I provided earlier. And pgagroal-cli status I will check and let you know.

…

On Fri, Apr 12, 2024, 8:57 PM Jesper Pedersen ***@***.***> wrote: Well, what does your pgagroal_databases.conf look like ? Connections will idle out down to min_size... — Reply to this email directly, view it on GitHub <#439 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG65KKBJSWW54RGFJLELY474P7AVCNFSM6AAAAABGD7NFMOVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TAOJXGU2TK> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[fluca1978]

@fluca1978 ..how much connection limit should I define in pgagroal_databases.conf?

It depends on your aim: you define a set of and .

What is not clear to me is from where those 250+ connections are originated, and if they are limited to a single database or other databases.

[sheshjee]

They are limited to a single database and they are originated from our client side by some application code.

[fluca1978]

They are limited to a single database and they are originated from our client side by some application code.

So, just to make it clear:

• your (java) client is originating around 50 connections
• pgagroal shows that these connections are active in the pool
• postgresql shows only seven connections, which removing also entries for maintanance, narrows down the number at almost the half I guess
• assuming you didn't change anything, your clients are all connecting to db as superusers.

Have you tried to pgagroal-cli flush all?
Which version of pgagroal are you running?

Can you deliberately close a client and see what changes in the pooler status? Or, viceversa, open a new client and see what happend and then close such client?

[sheshjee]

yes I tried flush all command and it removed all connections.
pgagroal 1.5

Yes I created a new connection and close . On creation of new connection count increased and by closing the connection count decreased.

[fluca1978]

yes I tried flush all command and it removed all connections. pgagroal 1.5

Can you get a newer version? Even if 1.5 is not so outdated, it would be better to have a more recent version. Even if, quite frankly, I don't think this has nothing to do with your problem.

Yes I created a new connection and close . On creation of new connection count increased and by closing the connection count decreased.

So your connections are all active, what does the pg_stat_activity reports in term of status? Can you also query for the incoming client address and application_name to ensure you are looking at something that comes from pgagroal?

[sheshjee]

pg_stat_activity shows a total of 7 connections in which one is active and 6 are idle.

@can you also query for the incoming client address and application_name to ensure you are looking at something that comes from pgagroal? Can you please provide the query for the same?

[fluca1978]

pg_stat_activity shows a total of 7 connections in which one is active and 6 are idle.

@can you also query for the incoming client address and application_name to ensure you are looking at something that comes from pgagroal? Can you please provide the query for the same?

SELECT application_name, client_inet_addr FROM pg_stat_activity; add also other fields that make sense to you.