How to kill / terminate PostgreSQL hang query
Today, i found out that one of the SQL query is hanging in PostgreSQL, and never release itself. “Cancel query” command won’t help, and the query just hanging there and show an “idle in transaction” status. It left me no choice but go Debian terminal to issue “kill” command to terminate it manually.
Query hanging or not responding in PostgreSQL is because we didn’t handle the transaction manager properly in web application. When System shut down accidentally, running query will hanging in PostgreSQL and the transaction manager (e.g DataSourceTransactionManager) is not manage to rollback the on going transaction.
After i changed the transaction manager to
JtaTransactionManager, my web application is able to kill the ongoing transaction even when system is shut down accidentally.However here i show you how to terminal the hanging SQL query. In PostgreSQL, all hanging query will display as “idle in transaction“. First, you have to list out all the existing PostgreSQL processes and issue a kill terminate command to terminate the hanging query manually.
1. List out all processes
Issue “ps -ef | grep postgres” command to list out all existing processes belong to postgres user.
mkyong:~# ps -ef | grep postgres postgres 13648 1 0 11:04 ? 00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx postgres 13651 13648 0 11:04 ? 00:00:00 postgres: logger process postgres 13653 13648 0 11:04 ? 00:00:00 postgres: writer process postgres 13654 13648 0 11:04 ? 00:00:00 postgres: wal writer process postgres 13655 13648 0 11:04 ? 00:00:00 postgres: autovacuum launcher process postgres 13656 13648 0 11:04 ? 00:00:00 postgres: stats collector process postgres 13668 13648 0 11:04 ? 00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle postgres 13689 13648 0 11:05 ? 00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle postgres 13714 13648 0 11:06 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57586) idle in transaction postgres 13721 13648 0 11:06 ? 00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle postgres 13832 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle postgres 13833 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle postgres 13834 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle postgres 13835 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle postgres 13836 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle postgres 14201 13648 0 11:23 ? 00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle postgres 14202 13648 0 11:23 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle postgres 14207 13648 0 11:24 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle root 18030 17992 0 13:46 pts/0 00:00:00 grep postgres mkyong:~#
2. Find the idle transaction + Kill
Notice process id “13714, idle in transaction“, this is the hanging query in PostgreSQL. Issue “kill” command to terminate the PostgreSQL process manually.
mkyong:~# kill 13714or
mkyong:~# kill -TERM 13714or
mkyong:~# kill -15 137143. Killed ! Done.
Done, the hanging query is gone!
mkyong:~# ps -ef | grep postgres postgres 13648 1 0 11:04 ? 00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx postgres 13651 13648 0 11:04 ? 00:00:00 postgres: logger process postgres 13653 13648 0 11:04 ? 00:00:00 postgres: writer process postgres 13654 13648 0 11:04 ? 00:00:00 postgres: wal writer process postgres 13655 13648 0 11:04 ? 00:00:00 postgres: autovacuum launcher process postgres 13656 13648 0 11:04 ? 00:00:00 postgres: stats collector process postgres 13668 13648 0 11:04 ? 00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle postgres 13689 13648 0 11:05 ? 00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle postgres 13721 13648 0 11:06 ? 00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle postgres 13832 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle postgres 13833 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle postgres 13834 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle postgres 13835 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle postgres 13836 13648 0 11:10 ? 00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle postgres 14201 13648 0 11:23 ? 00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle postgres 14202 13648 0 11:23 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle postgres 14207 13648 0 11:24 ? 00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle root 18030 17992 0 13:46 pts/0 00:00:00 grep postgres mkyong:~#
The proper way to safely kill a postgres process is:
kill -2
Or use the pg_cancel_backend(‘procpid’) method if connecting to the database.
I doubt about the “
pg_cancel_backend“, it’s documented, but not function as what documented, well, i’m using old PostgreSQL, may be the function is improved :)I had an interview for PostGres DBA position yesterday. They asked me, how will I kill an unwanted process. I replied the same, I would use ps -ef | grep postgres, and kill the process. Then they asked me, how would you determine the exact process, as the above command doesn’t show you full description. I had no answer. What is the correct answer for this. Like, in the above article we can see, there are many processes showing as ‘Idle’. How will you determine the exact process from those ‘idle’ processes?
see carefully, normal transaction will display as “idle” only, those “problem” transaction will display as “idle in transaction” status, is this obvious to tell you which to kill? :)
Only kill the process id which has the “idle in transaction” status, accidentally kill others core postgresql instance will caused db to restart automatically.
I’ve done this before and it causes all of postgres to be restarted. I see no way of stopping these hung processes.
I noticed if I use kill -9 to kill the ‘idle in transaction’, the database server will restart. Use kill -TERM is safe.
On PSql Prompt
select * from pg_stat_activity where current_query=’ in transaction’
and
Then select pg_cancle_backend(‘procpid) from above query or
PG_terminate_backend(‘procpid); on psql prompt
Clearly the nicest variant i think, as the DB server manages the killing.
Only a little typo corrected:
select pg_cancel_backend( NNN );