Idle user session in psql for RHV
Environment
RHV 4.x+
psql 9.2+
Issue
How do I identify/kill an idle user session in psql?
Resolution
Important Note:
Before doing any changes/alterations with psql/mysql/etc always ensure you have a recent backup!
To locate an idle session you can run the following command:
# /usr/share/ovirt-engine/dbscripts/engine-psql.sh -c "select * from pg_stat_activity ;"
You should see something like:
-[ RECORD 1 ]----+----------------------------------------------------------------------------------------
datid | 24015
datname | engine
pid | 9042
usesysid | 16301
usename | engine
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2015-01-06 15:10:00.295384+00
xact_start |
query_start | 2015-01-06 15:10:37.277436+00
state_change | 2015-01-06 15:10:37.278002+00
waiting | f
state | idle
To remove any idle session ensure you have a backup of the database and then run the following command:
# /usr/share/ovirt-engine/dbscripts/engine-psql.sh -c "select pid, (select pg_terminate_backend(pid)) as killed from pg_stat_activity where state like 'idle';"
The output from above should be something like:
[ RECORD 1 ]
pid | 9042
killed | t
Root Cause
Occasionally a session may hang and go idle which may cause certain database transactions from initiating or completing. Clearing the 'idle' session(s) will allow for those tasks to start and ultimately complete.
This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.
Comments