Background
I have a PgPool-II cluster (ver 4.1.4) running on 3 centos 8 machines (virtual); SQL1, SQL2 and SQL3 (each on different hardware). On SQL1 and SQL2 PostgreSQL-12 are running (currently SQL1 is master and SQL2 in streaming replication standby).
In the db cluster there are currently 4 databases for 2 different software environments. One customer (cust) environment with quite much traffic and one educational (edu) environment with basically no user activity at all.
Both environments have one db each and also shares two databases (only for reading).
The application is written in net core 3.1 at the moment and uses npgsql and entity framework core for connecting to the pgpool cluster.
Except "normal" application sql requests to the databases with entity framework there are also periodical calls with psql in order to run the "show pool_nodes;" in pgpool. This could not be done with entity framework, hence psql instead.
Each environment also has one "main api" which handles internet traffic, and one "service api" which runs background tasks. Both uses entity framework to call the database. And psql is also sometimes invoked from the "service api" as described above.
On top of this all applications also have an A and B system.
So to sum up:
2 Environments (cust, edu) has A and B system, which also has "main api" and "service api" each => 8 applications, (12 if counting that all "service api" also invokes psql every 5 min).
The applications are on 2 different machines (A and B), "main api" and "service api" runs on the same machine for one environment and system.
Each entity framework application can also make parallell/multiple simultaneous requests depending on the user activity to the api.
My question
Every now and then there is an error from the pgpool cluster: Sorry, too many clients already
.
Usually when connecting with psql, but sometimes this also happens from entity framework.
My initial thought was that this was because the databases had to many clients connected, but running pg_stat_activity just seconds after the error shows that there are way less connections (around 50), then the 150 max_connections in psql config. I could not find any errors at all in psql logs in the "log" folder in psql data directory.
But in the pgpool.log file there are an error entry:
Oct 30 16:34:19 sql1 pgpool[4062984]: [109497-1] 2020-10-30 16:34:19: pid 4062984: ERROR: Sorry, too many clients already
pgpool has num_init_children = 32 and max_pool = 4 so I do not really see where the problem might be coming from.
Some files that might be needed for more info:
pg_stat_activity (Taken 11s after the error)
pgpool.log
pgpool.conf
postgresql.conf
2
Answers
This problem might exist from a bug that according to one of the developers of pgpool will have its fix included in the 4.1.5 update november 19, 2020.
The bug makes the counter for existing connections not counting down if a query is cancelled.
Simply upgrade pgpool from 4.1.2 to 4.1.5 (Newer versions of pgpool are available as well)
I had the same problem with pgpool 4.1.2, Sorry, too many clients already was occurring almost every hour.
The problem got fixed after I upgraded pgpool from 4.1.2 to 4.1.5. We never experienced the error afterward