Yes, i came here only after doing a lot of research on internet. I am doing most of these things already.
I need help on two things now.
1. I see lot of idle connections where application_name is blank and also query is blank, i am identifying only with user. In this case how do I identify which application is using idle connection?
Are you querying pg_stat_activity as a superuser, or at least a user who is a member of pg_monitor? If not, many fields will be uninformative due to security reasons. Although "query" shouldn't be literally blank, it should be the string '<insufficient privilege>', but maybe you misinterpreted that as being blank and being filled-in client-side.
If you are querying it as the superuser and still don't know who is connecting to your database, maybe it is time to change the password and be more diligent about who you hand it out to.
