DB connection issue suggestions
Dear All, We have recently upgraded Postgresql 9.4 standalone server to Postgresql 11.2 with High Availability (2 servers : Master and Standby). While trying to test using ETL applications and reports, we observe that the ETL jobs fails with below error, 2022/05/06 16:27:36 - Error occurred while trying to connect to the database 2022/05/06 16:27:36 - Error connecting to database: (using class org.postgresql.Driver) 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already We have increased the max_connections = 1000 in postgresql.conf file. It worked ok for a day and later we get the same error message. Please help to advise on any additional settings required. The prior Postgresql 9.4 had the default max_connections = 100 and the applications worked fine. Regards, Guna
Re: DB connection issue suggestions
Please show output of "show max_connections" to validate your assumptions. On 05/10/2022 12:59 PM Sudhir Guna wrote: Dear All, We have recently upgraded Postgresql 9.4 standalone server to Postgresql 11.2 with High Availability (2 servers : Master and Standby). While trying to test using ETL applications and reports, we observe that the ETL jobs fails with below error, 2022/05/06 16:27:36 - Error occurred while trying to connect to the database 2022/05/06 16:27:36 - Error connecting to database: (using class org.postgresql.Driver) 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already We have increased the max_connections = 1000 in postgresql.conf file. It worked ok for a day and later we get the same error message. Please help to advise on any additional settings required. The prior Postgresql 9.4 had the default max_connections = 100 and the applications worked fine. Regards, Guna
Re: DB connection issue suggestions
On Wed, May 11, 2022 at 12:59:01AM +0800, Sudhir Guna wrote: > Dear All, > > We have recently upgraded Postgresql 9.4 standalone server to Postgresql > 11.2 with High Availability (2 servers : Master and Standby). > > While trying to test using ETL applications and reports, we observe that > the ETL jobs fails with below error, > > 2022/05/06 16:27:36 - Error occurred while trying to connect to the database > 2022/05/06 16:27:36 - Error connecting to database: (using class > org.postgresql.Driver) > 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already > > We have increased the max_connections = 1000 in postgresql.conf file. > > It worked ok for a day and later we get the same error message. > > Please help to advise on any additional settings required. The prior > Postgresql 9.4 had the default max_connections = 100 and the applications > worked fine. It sounds like at least one thing is still running, perhaps running very slowly. You should monitor the number of connections to figure out what. If you expect to be able to run with only 100 connections, then when connections>200, there's already over 100 connections which shouldn't still be there. You could query pg_stat_activity to determine what they're doing - trying to run a slow query ? Are all/most of them stuck doing the same thing ? You should try to provide the information here for the slow query, and for the rest of your environment. https://wiki.postgresql.org/wiki/Slow_Query_Questions -- Justin
Re: DB connection issue suggestions
Em ter., 10 de mai. de 2022 às 14:49, Sudhir Guna escreveu: > Dear All, > > We have recently upgraded Postgresql 9.4 standalone server to Postgresql > 11.2 with High Availability (2 servers : Master and Standby). > > While trying to test using ETL applications and reports, we observe that > the ETL jobs fails with below error, > > 2022/05/06 16:27:36 - Error occurred while trying to connect to the > database > 2022/05/06 16:27:36 - Error connecting to database: (using class > org.postgresql.Driver) > 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already > > We have increased the max_connections = 1000 in postgresql.conf file. > > It worked ok for a day and later we get the same error message. > > Please help to advise on any additional settings required. The prior > Postgresql 9.4 had the default max_connections = 100 and the applications > worked fine. > I guess that ETL is pentaho? You can try to use the latest JDBC driver (42.3.5) . regards, Ranier Vilela
Re: DB connection issue suggestions
On Wed, 2022-05-11 at 00:59 +0800, Sudhir Guna wrote: > We have recently upgraded Postgresql 9.4 standalone server to Postgresql 11.2 > with High Availability (2 servers : Master and Standby). > > While trying to test using ETL applications and reports, we observe that the > ETL jobs fails with below error, > > 2022/05/06 16:27:36 - Error occurred while trying to connect to the database > 2022/05/06 16:27:36 - Error connecting to database: (using class > org.postgresql.Driver) > 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already > > We have increased the max_connections = 1000 in postgresql.conf file. > > It worked ok for a day and later we get the same error message. > > Please help to advise on any additional settings required. The prior > Postgresql 9.4 had the default max_connections = 100and the applications > worked fine. Some application that uses the database has a connection leak: it opens new connections without closing old ones. Examine "pg_stat_activity" to find out which application is at fault, and then go and fix that application. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: DB connection issue suggestions
Dear team, Can you confirm whether, post upgrade activity, all the post-upgrade steps including stats update on all the relations is complete. Upgrade doesn’t carry over the stats to the new upgraded cluster. Regards, Anbazhagan M > On 11-May-2022, at 11:43 AM, Laurenz Albe wrote: > > On Wed, 2022-05-11 at 00:59 +0800, Sudhir Guna wrote: >> We have recently upgraded Postgresql 9.4 standalone server to Postgresql >> 11.2 with High Availability (2 servers : Master and Standby). >> >> While trying to test using ETL applications and reports, we observe that the >> ETL jobs fails with below error, >> >> 2022/05/06 16:27:36 - Error occurred while trying to connect to the database >> 2022/05/06 16:27:36 - Error connecting to database: (using class >> org.postgresql.Driver) >> 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already >> >> We have increased the max_connections = 1000 in postgresql.conf file. >> >> It worked ok for a day and later we get the same error message. >> >> Please help to advise on any additional settings required. The prior >> Postgresql 9.4 had the default max_connections = 100and the applications >> worked fine. > > Some application that uses the database has a connection leak: it opens new > connections > without closing old ones. Examine "pg_stat_activity" to find out which > application is > at fault, and then go and fix that application. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
