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?
Use client_addr and client_port from pg_stat_activity to identify the application. The client_addr will give the host it’s connecting from and the port will allow you to determine the process on the client host.
2. How to set up TCP/IP timeouts? I saw some parameters like tcp_user_timeout, tcp_keepalives_idle etc or how?
I meant to say TCP/IP keep alive. There are different ways and options to set keep alive and some are OS dependent.
Here’ s an article that should help you get started: