On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 06/27/2017 01:10 PM, DrakoRod wrote: > >> Hi folks. >> >> Today I had a problem with production's database PostgreSQL version >> 9.4.4.9. >> The server have max_connections set to 200, but today I reviewed >> pg_stat_activity and saw 199 active connections, obviously the server >> rejected any new connection and the production stopped. >> >> I saw another posts with a similar problems, but this was because the >> pg_xlog was full or disk does'nt write, but the directory and disk had no >> problems. >> >> I just canceled some SELECTs querys and the server returned to normality. >> Now a monitoring activity of server and I can see some backends like this: >> >> postgres 9737 23340 2 14:55 ? 00:00:15 postgres: dbname user >> 8.8.8.8[37082] idle in transaction >> postgres 9741 23340 9 14:55 ? 00:00:47 postgres: dbname user >> 8.8.8.8[54286] idle in transaction >> >> Any suggestions? >> > > https://www.postgresql.org/docs/9.4/static/monitoring-stats. > html#PG-STAT-ACTIVITY-VIEW > > SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; > > To get more detail on what is holding these transactions open. > > >> >> >> ----- >> Dame un poco de fe, eso me bastará. >> Rozvo Ware Solutions >> -- >> View this message in context: http://www.postgresql-archive. >> org/postgres-dbname-dbuser-9-9-9-9-2222-PARSE-waiting-tp5968923.html >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *His problem is NOT 'idle in transaction' per se. It is all connections are used up.* *Hence the need for pg_bouncer for connection pooling.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
