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]
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:
- 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
- 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
- Then check with the following:
ps aux | grep 17806
- Once reviewed, soft kill the process (via
kill -9 xxxxwhere xxxx would refer to that process ID).
That will kill the idle connection and thus, the alert.