FATAL: connection pool is full #197

fluca1978 · 2022-02-01T16:13:07Z

fluca1978
Feb 1, 2022
Collaborator

I'm surely not understanding well how the configuration works. In order to test the connection on hold, I configured the pooler as:

and when launched, the pooler shows me:

% sudo -u pgagroal pgagroal-cli details
Status:              Running
Active connections:  0
Total connections:   0
Max connections:     1
---------------------
Server:              venkman
Host:                venkman
Port:                5432
State:               Not init (primary)
---------------------
Server:              spengler
Host:                spengler
Port:                5432
State:               Not init
---------------------
Connection    0:     Not initialized

So far, so good. But then I tried to connect, only one connection;

% psql -h stantz -U luca testdb
psql: error: connection to server at "stantz" (192.168.222.15), port 5432 failed: FATAL:  connection pool is full

(stantz is the connection pooler).
Why is the pool full if no active connections are there?
Raising the max_connections to 2 seems to do the trick, so there could be something I don't get here:

Status:              Running
Active connections:  0
Total connections:   0
Max connections:     2
---------------------
Server:              venkman
Host:                venkman
Port:                5432
State:               Not init (primary)
---------------------
Server:              spengler
Host:                spengler
Port:                5432
State:               Not init
---------------------
Connection    0:     Not initialized                                     
Connection    1:     Not initialized

and one connection can be opened. However, when the connection is closed, the pool becomes to be full again. Doing a flush-all fixes the problem: % sudo -u pgagroal pgagroal-cli flush-all. However, again, only one connection can be issued against the pooler.

I've observerd that the pooler seems to open an extra connection. The initial situation is one connection to pgbench:

% sudo -u pgagroal pgagroal-cli details
Status:              Running
Active connections:  1
Total connections:   2
Max connections:     3
---------------------
Server:              venkman
Host:                venkman
Port:                5432
State:               Primary
---------------------
Server:              spengler
Host:                spengler
Port:                5432
State:               Not init
---------------------
Connection    0:     Not initialized                                     
Connection    1:     Active          2022-02-01 11:07:53 1134   9      pgbench pgbench psql
Connection    2:     Free            2022-02-01 11:08:42        15     template1 luca

then I open another connection to template1:

% sudo -u pgagroal pgagroal-cli details
Status:              Running
Active connections:  3
Total connections:   3
Max connections:     3
---------------------
Server:              venkman
Host:                venkman
Port:                5432
State:               Primary
---------------------
Server:              spengler
Host:                spengler
Port:                5432
State:               Not init
---------------------
Connection    0:     Active          2022-02-01 11:08:56 1199   9      template1 luca psql
Connection    1:     Active          2022-02-01 11:07:53 1134   9      pgbench pgbench psql
Connection    2:     Active          2022-02-01 11:08:54 1198   15     template1 luca psql

why there are three connections active while I'm using only two? Going directly on the PostgreSQL machines shows effectively two connections on template1 while I'm running a single psql instance:

=# select datname, usename, client_addr, application_name from pg_stat_activity;
  datname  | usename  |  client_addr   | application_name 
-----------+----------+----------------+------------------
           |          |                | 
           | postgres |                | 
 template1 | luca     | 192.168.222.15 | psql
 pgbench   | pgbench  | 192.168.222.15 | psql
 template1 | luca     | 192.168.222.15 | psql

jesperpedersen · 2022-02-01T16:53:16Z

jesperpedersen
Feb 1, 2022
Maintainer

The issue is that psql - and the underlying library used - is actually opening 2 socket connections per physical connection - e.g. your session. The first socket connection is closed, but only after a while which is why you run into these connection pool is full scenarios. I havn't investigated the scenario in detail and therefore havn't submitted a patch for it either.

Try your test case with another client library like JDBC and you should get the behaviour you are looking for.

2 replies

fluca1978 Feb 2, 2022
Collaborator Author

The issue is that psql - and the underlying library used - is actually opening 2 socket connections per physical connection - e.g. your session. The first socket connection is closed, but only after a while which is why you run into these connection pool is full scenarios. I havn't investigated the scenario in detail and therefore havn't submitted a patch for it either.

Interesting, I was not aware of that and seems I cannot find it in the documentation of psql.
I'm not able to observe such behavior with netstat, neither with pg_stat_activity, at least with a \watch of 1 second.
Is pgbench suffering of the same problem of two sockets per connection?

jesperpedersen Feb 2, 2022
Maintainer

You will have to look at the network packets sent, as we are talking milliseconds here. You can use https://github.com/jesperpedersen/pgprtdbg/ f.ex. Each psql connection will only take up 1 network socket once fully initialized (e.g. post-auth).

Yes, pgbench uses the same library so same issue. I would prefill pgagroal (using -u and -l) if you are looking to run pgbench.

sheshjee · 2024-01-28T12:43:23Z

sheshjee
Jan 28, 2024

The same problem I am facing. I have assigned max_connection is 1000 in pgagroal. But only 31 connections are active at a time. So where are the remaining? And why I am getting the same error as connection pool is full if max_conn is 1000

2 replies

jesperpedersen Jan 28, 2024
Maintainer

You need to debug it - when psql disconnects does the count go down, and so on.

A 1000 is way too much... you may be able to go 2.5 x CPU threads with a minimum of 100...

sheshjee Apr 14, 2024

no, count is not going down even after disconnecting..

psql -h localhost -p 2345 -U postgres -d mydb
psql (13.3)
Type "help" for help.
mydb=# \q
[postgres@test ~]$ pgagroal-cli status
Status: Running
Active connections: 251
Total connections: 252
Max connections: 900

psql -h localhost -p 2345 -U postgres -d template1
psql (13.3)
Type "help" for help.

template1=# \q
[postgres@test ~]$ pgagroal-cli status
Status: Running
Active connections: 251
Total connections: 253
Max connections: 900

psql -h localhost -p 2345 -U postgres -d postgres
psql (13.3)
Type "help" for help.

postgres=# \q
[postgres@test ~]$ pgagroal-cli status
Status: Running
Active connections: 251
Total connections: 254
Max connections: 900

sheshjee · 2024-01-28T14:07:51Z

sheshjee
Jan 28, 2024

can you please provide the steps? I am totally new to pgagroal.

…

On Sun, Jan 28, 2024 at 7:05 PM Jesper Pedersen ***@***.***> wrote: You need to debug it - when psql disconnects does the count go down, and so on. A 1000 is way too much... you may be able to go 2.5 x CPU threads with a minimum of 100... — Reply to this email directly, view it on GitHub <#197 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG6YMT25XCTZPKB7HASLYQZHZXAVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRXGE3DENA> . You are receiving this because you commented.Message ID: ***@***.***>

-- sheshjee

0 replies

sheshjee · 2024-01-28T14:11:21Z

sheshjee
Jan 28, 2024

this is the scenario.. bash-4.4$ pgagroal-cli details Status: Running Active connections: 1 Total connections: 2 Max connections: 900 --------------------- Server: primary Host: localhost Port: 5432 State: Replica --------------------- Connection 0: Free 2024-01-28 19:06:04 abc postgres Connection 1: Not initialized Connection 2: Not initialized Connection 3: Not initialized Connection 4: Not initialized Connection 5: Not initialized Connection 6: Not initialized Connection 7: Not initialized Connection 8: Not initialized Connection 9: Not initialized Connection 10: Not initialized Connection 11: Active 2024-01-26 16:38:00 3658426 replication standby luna Connection 12: Not initialized Connection 13: Not initialized Connection 14: Not initialized Connection 15: Not initialized Connection 16: Not initialized Connection 17: Not initialized Connection 18: Not initialized Connection 19: Not initialized Connection 20: Not initialized Connection 21: Not initialized Connection 22: Not initialized Connection 23: Not initialized Connection 24: Not initialized and so on On Sun, Jan 28, 2024 at 7:37 PM Sheshjee Pandey ***@***.***> wrote:

…

can you please provide the steps? I am totally new to pgagroal. On Sun, Jan 28, 2024 at 7:05 PM Jesper Pedersen ***@***.***> wrote: > You need to debug it - when psql disconnects does the count go down, and > so on. > > A 1000 is way too much... you may be able to go 2.5 x CPU threads with a > minimum of 100... > > — > Reply to this email directly, view it on GitHub > <#197 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/BFMCG6YMT25XCTZPKB7HASLYQZHZXAVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRXGE3DENA> > . > You are receiving this because you commented.Message ID: > ***@***.***> > -- sheshjee

-- sheshjee

1 reply

jesperpedersen Jan 28, 2024
Maintainer

Perfect.

Now, you look at the active connections after each psql is done (\q) and see if the connection count is correct.

In the above example you have 1 active client, and 1 free - so on the PostgreSQL side you should see 2 connections.

sheshjee · 2024-01-28T15:07:46Z

sheshjee
Jan 28, 2024

postgres=# select count(*) from pg_stat_activity ; count ------- 38 (1 row)

…

On Sun, Jan 28, 2024 at 8:14 PM Jesper Pedersen ***@***.***> wrote: Perfect. Now, you look at the active connections after each psql is done (\q) and see if the connection count is correct. In the above example you have 1 active client, and 1 free - so on the PostgreSQL side you should see 2 connections. — Reply to this email directly, view it on GitHub <#197 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG65FEIMQNNLU77LVXHTYQZP5XAVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRXGIYDSMI> . You are receiving this because you commented.Message ID: ***@***.***>

-- sheshjee

0 replies

sheshjee · 2024-01-28T15:20:22Z

sheshjee
Jan 28, 2024

datid | 24577 datname | abc pid | 3636925 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 45116 backend_start | 2024-01-26 16:25:05.877202+05:30 xact_start | query_start | 2024-01-26 16:27:55.16265+05:30 state_change | 2024-01-26 16:27:55.162676+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 22 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 3636926 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 45132 backend_start | 2024-01-26 16:25:05.877829+05:30 xact_start | query_start | 2024-01-26 16:27:55.164361+05:30 state_change | 2024-01-26 16:27:55.164395+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 23 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 245277 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 40340 backend_start | 2024-01-09 21:31:54.126106+05:30 xact_start | query_start | 2024-01-09 21:31:54.142876+05:30 state_change | 2024-01-09 21:31:54.142923+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 24 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 730058 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 38170 backend_start | 2024-01-10 00:41:53.673015+05:30 xact_start | query_start | 2024-01-10 00:41:53.698355+05:30 state_change | 2024-01-10 00:41:53.698396+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 25 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 1833153 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 60224 backend_start | 2024-01-10 11:58:53.836091+05:30 xact_start | query_start | 2024-01-10 11:58:53.857363+05:30 state_change | 2024-01-10 11:58:53.857406+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 26 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ datid | 24577 datname | abc pid | 721952 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 60082 backend_start | 2024-01-12 04:29:59.496081+05:30 xact_start | query_start | 2024-01-12 04:29:59.520691+05:30 state_change | 2024-01-12 04:29:59.520727+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 27 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | datname | pid | 3658427 leader_pid | usesysid | 16384 usename | standby application_name | luna client_addr | ::1 client_hostname | client_port | 39742 backend_start | 2024-01-26 16:38:00.639314+05:30 xact_start | query_start | state_change | 2024-01-26 16:38:00.823744+05:30 wait_event_type | Activity wait_event | WalSenderMain state | active backend_xid | backend_xmin | query | backend_type | walsender -[ RECORD 28 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 3994908 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 58808 backend_start | 2024-01-18 06:37:02.561552+05:30 xact_start | query_start | 2024-01-18 06:37:02.589741+05:30 state_change | 2024-01-18 06:37:02.589795+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 29 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 4057979 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 40280 backend_start | 2024-01-19 20:40:09.560764+05:30 xact_start | query_start | 2024-01-19 20:40:09.590613+05:30 state_change | 2024-01-19 20:40:09.590668+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 30 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 1494379 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 55342 backend_start | 2024-01-12 13:28:13.857679+05:30 xact_start | query_start | 2024-01-12 13:28:13.869656+05:30 state_change | 2024-01-12 13:28:13.869688+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 31 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 13434 datname | postgres pid | 3480627 leader_pid | usesysid | 10 usename | postgres application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2024-01-28 20:39:10.824813+05:30 xact_start | 2024-01-28 20:39:31.686013+05:30 query_start | 2024-01-28 20:39:31.686013+05:30 state_change | 2024-01-28 20:39:31.686017+05:30 wait_event_type | wait_event | state | active backend_xid | backend_xmin | 1447454953 query | select * from pg_stat_activity ; backend_type | client backend -[ RECORD 32 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 1151035 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 47132 backend_start | 2024-01-24 10:31:59.206838+05:30 xact_start | query_start | 2024-01-24 10:31:59.219231+05:30 state_change | 2024-01-24 10:31:59.219274+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 33 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 4122156 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 45800 backend_start | 2024-01-21 09:28:28.220546+05:30 xact_start | query_start | 2024-01-21 09:28:28.237184+05:30 state_change | 2024-01-21 09:28:28.237241+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend -[ RECORD 34 ]---+------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- datid | 24577 datname | abc pid | 1203847 leader_pid | usesysid | 10 usename | postgres application_name | client_addr | ::1 client_hostname | client_port | 55058 backend_start | 2024-01-27 17:33:26.173983+05:30 xact_start | query_start | 2024-01-27 17:33:26.190765+05:30 state_change | 2024-01-27 17:33:26.190805+05:30 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | DISCARD ALL; backend_type | client backend On Sun, Jan 28, 2024 at 8:37 PM Sheshjee Pandey ***@***.***> wrote:

…

postgres=# select count(*) from pg_stat_activity ; count ------- 38 (1 row) On Sun, Jan 28, 2024 at 8:14 PM Jesper Pedersen ***@***.***> wrote: > Perfect. > > Now, you look at the active connections after each psql is done (\q) and > see if the connection count is correct. > > In the above example you have 1 active client, and 1 free - so on the > PostgreSQL side you should see 2 connections. > > — > Reply to this email directly, view it on GitHub > <#197 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/BFMCG65FEIMQNNLU77LVXHTYQZP5XAVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRXGIYDSMI> > . > You are receiving this because you commented.Message ID: > ***@***.***> > -- sheshjee

-- sheshjee

4 replies

jesperpedersen Jan 28, 2024
Maintainer

This doesn't help.

You need go through it

Check pgagroal
Connect a client
Check pgagroal
Disconnect client
Check pgagroal

and so on - you need to find the sequence of events where the connections are not released.

psql works here...

sheshjee Apr 14, 2024

psql -h localhost -p 2345 -U postgres -d mydb
psql (13.3)
Type "help" for help.
mydb=# \q
[postgres@test ~]$ pgagroal-cli status
Status: Running
Active connections: 251
Total connections: 252
Max connections: 900

psql -h localhost -p 2345 -U postgres -d template1
psql (13.3)
Type "help" for help.

template1=# \q
[postgres@test ~]$ pgagroal-cli status
Status: Running
Active connections: 251
Total connections: 253
Max connections: 900

psql -h localhost -p 2345 -U postgres -d postgres
psql (13.3)
Type "help" for help.

postgres=# \q
[postgres@test ~]$ pgagroal-cli status
Status: Running
Active connections: 251
Total connections: 254
Max connections: 900

jesperpedersen Apr 14, 2024
Maintainer

It means that pgagroal didn't receive a Close (F) command with a P (Portal) subcommand from the application framework that you are using.

Once pgagroal does that the connection is put back in the pool, and idled out due to the policies

sheshjee Apr 14, 2024

Yes you are right, but when I saw this after some time again the active connection is 251 and Total connection is 251, which means the connection closed by me by \q has been removed from pool. Now the problem is why these 251 connections are still active while many of these is active for more than a month

fluca1978 · 2024-01-28T16:28:26Z

fluca1978
Jan 28, 2024
Collaborator Author

Why don't you try to:

psql -h <your pgagroal> -U <user> -c 'SELECT count(*) FROM pg_stat_activity where datname = <yourdb>;' <yourdb>

then see what the status is. You can automate the above and loop to see if there is a problem with connection release.
Also you can add a select pg_sleep() like query to let the connection be used for a certain amount of time.

Note that you server is reported as replica, is that normal?
Also you need to specify the version of pgagroal you are using, as well as the configuration.

1 reply

sheshjee Jan 28, 2024

Select count (*) from pg_stat_activity shows 31

fluca1978 · 2024-01-28T16:57:22Z

fluca1978
Jan 28, 2024
Collaborator Author

Select count (*) from pg_stat_activity shows 31

We cannot help if you don't do what we asked for:

execute the query and report back the exact query and output
show the configuration (of the pooler)
show the pgagroal version

Moreover please restart (if possible) pgagroal after having verified that PostgreSQL does not report any connection from the poooler.

0 replies

sheshjee · 2024-01-28T23:00:38Z

sheshjee
Jan 28, 2024

psql -h localhost -U postgres -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'abc';" abc count ------- 29 (1 row) pgagroal configuration: /etc/pgagroal/pgagroal.conf [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 [primary] host = localhost port = 5432 ################ /etc/pgagroal/pgagroal_hba.conf # # TYPE DATABASE USER ADDRESS METHOD # host all all all all ############# /etc/systemd/system/pgagroal.service # systemd service unit for pgagroal # # - Adjust the user running the service in User # - Adjust the path in ExecStart # [Unit] Description=High-performance connection pool for PostgreSQL Documentation=man:pgagroal(1) Documentation=https://agroal.github.io/pgagroal/ After=network.target [Service] Type=exec User=postgres ExecStart=/bin/pgagroal ExecReload=/bin/kill -HUP $MAINPID KillSignal=SIGINT LimitNOFILE=10240 [Install] WantedBy=multi-user.target pgagroal -V pgagroal 1.3.0

…

On Sun, Jan 28, 2024 at 10:27 PM Luca Ferrari ***@***.***> wrote: Select count (*) from pg_stat_activity shows 31 We cannot help if you don't do what we asked for: - execute the query and report back the exact query and output - show the configuration (of the pooler) - show the pgagroal version Moreover please restart (if possible) pgagroal after having verified that PostgreSQL does not report any connection from the poooler. — Reply to this email directly, view it on GitHub <#197 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG6YKINJBDLF4HB2INELYQZ7P5AVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRXGMYTAMA> . You are receiving this because you commented.Message ID: ***@***.***>

-- sheshjee

0 replies

fluca1978 · 2024-01-29T07:44:22Z

fluca1978
Jan 29, 2024
Collaborator Author

Your first post stated that:

bash-4.4$ pgagroal-cli details
Server:              primary
State:               Replica

but there is no replica in the configuration you shown. I don't understand why it is reported as not being primary.
pgagroal 1.3 is a little old, we are now at 1.5.1, any chance you can test it with a recent version?

0 replies

sheshjee · 2024-01-29T07:51:01Z

sheshjee
Jan 29, 2024

Ignore replica. Does the connection pool error is due to old version of pgagroal? Do you see any error in pgagroal configuration?

…

On Mon, Jan 29, 2024, 1:14 PM Luca Ferrari ***@***.***> wrote: Your first post stated that: bash-4.4$ pgagroal-cli details Server: primary State: Replica but there is no replica in the configuration you shown. I don't understand why it is reported as not being primary. pgagroal 1.3 is a little old, we are now at 1.5.1, any chance you can test it with a recent version? — Reply to this email directly, view it on GitHub <#197 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG64A4UXQCMB4PILHRRDYQ5HOBAVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRXG44TSNA> . You are receiving this because you commented.Message ID: ***@***.***>

0 replies

fluca1978 · 2024-01-29T07:58:27Z

fluca1978
Jan 29, 2024
Collaborator Author

Ignore replica.

Why? It looks strange to me at glance, why are you suggesting to ignore that?

Does the connection pool error is due to old version of pgagroal?

I don't know, but you are running an old version, and there is a possibility that the replica status is badly reported due to this. Besides, I don't think the old version is causing the connection pool error, but we are probably not getting the right information out of the old version.

There are no apparent errors in pgagroal.conf, I would suggest to increase the log level to debug and see if something else comes out from the logs.

0 replies

sheshjee · 2024-01-29T08:35:56Z

sheshjee
Jan 29, 2024

I am using 3 node cluster where one is master and the other two are replica. Main issue is when max_connection is 1000 then why not pgagroal/patroni is using all connections. why connection pool error is being generated despite of max limit 1000?

…

On Mon, Jan 29, 2024 at 1:28 PM Luca Ferrari ***@***.***> wrote: Ignore replica. Why? It looks strange to me at glance, why are you suggesting to ignore that? Does the connection pool error is due to old version of pgagroal? I don't know, but you are running an old version, and there is a possibility that the replica status is badly reported due to this. Besides, I don't think the old version is causing the connection pool error, but we are probably not getting the right information out of the old version. There are no apparent errors in pgagroal.conf, I would suggest to increase the log level to debug and see if something else comes out from the logs. — Reply to this email directly, view it on GitHub <#197 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG62GI4THKD7S23BYEALYQ5JC5AVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRXHAYTEMI> . You are receiving this because you commented.Message ID: ***@***.***>

-- sheshjee

0 replies

fluca1978 · 2024-01-29T11:38:28Z

fluca1978
Jan 29, 2024
Collaborator Author

I am using 3 node cluster where one is master and the other two are replica. Main issue is when max_connection is 1000 then why not pgagroal/patroni is using all connections. why connection pool error is being generated despite of max limit 1000?

So, after a long discussion, we are now getting more information: you are using patroni and two replicas.
These information has to be clear from the beginning, we cannot help you if you hide things. Now, the fact that you are seeing a replica status from a non-replica host means that is something else going on here.

Last time I asked you if you can update pgagroal, you haven't replied.
I also asked to dig the logs after setting debug as log level, and again, no news.
We cannot help you if you don't provide information nor do some inspection.
Are you sure your connections are routed to the correct PostgreSQL host?

0 replies

sheshjee · 2024-01-29T12:26:14Z

sheshjee
Jan 29, 2024

Yes connection are routed to correct postgresql logs. I can not upgrade pgagroal because I am not sure it will resolve the issue. Log level is info, not debug. Will do it debug

…

On Mon, Jan 29, 2024, 5:08 PM Luca Ferrari ***@***.***> wrote: I am using 3 node cluster where one is master and the other two are replica. Main issue is when max_connection is 1000 then why not pgagroal/patroni is using all connections. why connection pool error is being generated despite of max limit 1000? So, after a *long* discussion, we are now getting more information: you are using patroni and two replicas. These information has to be clear from the beginning, we cannot help you if you hide things. Now, the fact that you are seeing a replica status from a non-replica host means that is something else going on here. Last time I asked you if you can update pgagroal, you haven't replied. I also asked to dig the logs after setting debug as log level, and again, no news. We cannot help you if you don't provide information nor do some inspection. Are you sure your connections are routed to the correct PostgreSQL host? — Reply to this email directly, view it on GitHub <#197 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG63YC2IGTFEEOMNAUHLYQ6C37AVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRYGA4TMNQ> . You are receiving this because you commented.Message ID: ***@***.***>

0 replies

fluca1978 · 2024-01-29T13:51:35Z

fluca1978
Jan 29, 2024
Collaborator Author

I can not upgrade pgagroal because I am not sure it will resolve the issue.

It will not, almost certainly. The fact is that you are probably seeing some less-than-awesome information due to the fact you are running an old version.

Log level is info, not debug. Will do it debug

You don't need to restart pgagroal, so this is straightforward.

0 replies

sheshjee · 2024-01-29T13:56:22Z

sheshjee
Jan 29, 2024

Ok, thanks for the update.

…

On Mon, Jan 29, 2024, 7:21 PM Luca Ferrari ***@***.***> wrote: I can not upgrade pgagroal because I am not sure it will resolve the issue. It will not, almost certainly. The fact is that you are probably seeing some less-than-awesome information due to the fact you are running an old version. Log level is info, not debug. Will do it debug You don't need to restart pgagroal, so this is straightforward. — Reply to this email directly, view it on GitHub <#197 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG66BHL3LEWPT3253NBTYQ6SPHAVCNFSM5NJSYQ22U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMRYGMYDONY> . You are receiving this because you commented.Message ID: ***@***.***>

0 replies

fluca1978 · 2024-04-14T15:10:23Z

fluca1978
Apr 14, 2024
Collaborator Author

Yes you are right, but when I saw this after some time again the active connection is 251 and Total connection is 251, which means the connection closed by me by \q has been removed from pool. Now the problem is why these 251 connections are still active while many of these is active for more than a month

Where are these 251 connections generated from? Could it be some application server that performs a keepalive over the connections?

1 reply

sheshjee Apr 14, 2024

These connections are generated from client side by their servers. They do some transactions our database server through pgagroal.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FATAL: connection pool is full #197

{{title}}

Replies: 18 comments 11 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

FATAL: connection pool is full #197

fluca1978 Feb 1, 2022 Collaborator

Replies: 18 comments · 11 replies

jesperpedersen Feb 1, 2022 Maintainer

fluca1978 Feb 2, 2022 Collaborator Author

jesperpedersen Feb 2, 2022 Maintainer

jesperpedersen Jan 28, 2024 Maintainer

jesperpedersen Jan 28, 2024 Maintainer

jesperpedersen Jan 28, 2024 Maintainer

jesperpedersen Apr 14, 2024 Maintainer

fluca1978 Jan 28, 2024 Collaborator Author

fluca1978 Jan 28, 2024 Collaborator Author

fluca1978 Jan 29, 2024 Collaborator Author

fluca1978 Jan 29, 2024 Collaborator Author

fluca1978 Jan 29, 2024 Collaborator Author

fluca1978 Jan 29, 2024 Collaborator Author

fluca1978 Apr 14, 2024 Collaborator Author

fluca1978
Feb 1, 2022
Collaborator

Replies: 18 comments 11 replies

jesperpedersen
Feb 1, 2022
Maintainer

fluca1978 Feb 2, 2022
Collaborator Author

jesperpedersen Feb 2, 2022
Maintainer

jesperpedersen Jan 28, 2024
Maintainer

jesperpedersen Jan 28, 2024
Maintainer

jesperpedersen Jan 28, 2024
Maintainer

jesperpedersen Apr 14, 2024
Maintainer

fluca1978
Jan 28, 2024
Collaborator Author

fluca1978
Jan 28, 2024
Collaborator Author

fluca1978
Jan 29, 2024
Collaborator Author

fluca1978
Jan 29, 2024
Collaborator Author

fluca1978
Jan 29, 2024
Collaborator Author

fluca1978
Jan 29, 2024
Collaborator Author

fluca1978
Apr 14, 2024
Collaborator Author