More information found. After the hang connection appears, I noticed there were several hundreds of connections of the same user. Since I use pgbouncer and I only set the pool size to 50 for each user, this is very strange. I checked the pgbouncer side, 'show pools' showed the active server connection count is less than 50(only 35 actually). I also checked the client port which is shown in pg process list. It is not used at pgbouncer side when I did the check. So I stopped pgbouncer then the connection count from the user drops slowly. Finally all those connections disappeared. After that I restarted pgbouncer and it looks good again. With this solution, I at least don't have to kill pg when the problem happens. But anyone has a clue why this happens?
It sounds like someone is bypassing your pgbouncer and connecting directly to your database. Maybe they tried to create their own parallelization and have a master connection going through pgbouncer and create many auxiliary connections that go directly to the database (probably because pgbouncer wouldn't let them create as many connections as they wanted through it). That would explain why the connections slowly drain away once pgbouncer is shut down.
Can you change your pg_hba.conf file so that it only allows connections from pgbouncer's IP address? This should flush out the culprit pretty quickly.
