Main Tutorials

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.

Note
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 13714

or


mkyong:~# kill -TERM 13714

or


mkyong:~# kill -15 13714

3. 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:~# 

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
11 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Arif Faiz
10 years ago

Good day Mr. Mkyong,

Is there any suggesting way to kill the idle transaction went the transaction is more then a set time.

Anuj
11 years ago

Clearly the most dangerous way to do.

What happens is that that postgres ends up believing a crash happened and hence tries a recovery.
So for few seconds your database is not connectable.
Normal applications might not be affected but i doubt that postgres is that kind of a database.

PS: I am writing this because i had tried this in my server which does ~5000 insertions a minute and lost data for ~1 second which is a big deal for me.

🙂

Some DB Guy
12 years ago

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.

Vivek
13 years ago

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?

Matt
13 years ago

I’ve done this before and it causes all of postgres to be restarted. I see no way of stopping these hung processes.

Hong
12 years ago
Reply to  Matt

I noticed if I use kill -9 to kill the ‘idle in transaction’, the database server will restart. Use kill -TERM is safe.

Amer
12 years ago
Reply to  Hong

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

Steve Tzeggai
12 years ago
Reply to  Amer

Clearly the nicest variant i think, as the DB server manages the killing.

Only a little typo corrected:
select pg_cancel_backend( NNN );