Overview

You are receiving an alert like the one below for your database instance and wish to understand what is causing this behavior and how to fix it:

FAILURE Wed Oct 19 2022 10:17:42 GMT+0200 (Central European Summer Time)
Application and Database (i-xxxxxxxxxxxxxxxxx)
Process postgresql-txn_idle: POSTGRES_TXN_IDLE CRITICAL: DB 'postgres' longest idle in 
  txn: 2053s (34 minutes 13 seconds) PID:XXXXX database:[database name] username:[user] 
  address:[address] port:[port]

Solution

The alert shown above indicates that there is an idle connection to the DB from an internal IP
via a specific port under the process indicated in PID. You can solve this issue by following this process:

1. Find the process as follows:
   
   ps aux | grep [PID]
   
   You would get a result like the following
   

   postgres [PID] 0.1 8.5 8523740 2747760 ? Ss 06:47 0:10 postgres: [username]
       [database] [IP](56648) idle in transaction

   
   
   
2. Find what process had been utilizing it for access and which instance. In the case of multiple instances, you can track it back to the originating instance.
   
   netstat -ntpu | grep 56648
   
   You would get a result like the following:
   

   tcp        0      0 [IP]:56648              [IP]:5432              ESTABLISHED 17806/puma 5.6.4 (t 
   tcp        0      0 127.0.0.1:56648         127.0.0.1:8002         TIME_WAIT   -                   
   tcp        0      0 [IP]:5432               [IP]:56648             ESTABLISHED #A/postgres: dep 

3. Then check with the following:
   
   ps aux | grep 17806
   
   
4. Once reviewed, soft kill the process (viakill -9 xxxxwhere xxxx would refer to that process ID).

That will kill the idle connection and thus, the alert.