Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

If I have a long-running Postgres query, and regular "kill [pid]" doesn't work, and pg_cancel_backend doesn't work, what should I do?

share|improve this question
add comment

3 Answers

You should never kill -9 any postgres process unless your goal is to bring the entire server down forcibly. You can kill any process that doesn't respond to a pg_cancel_backend() call from the shell with

kill <pid>

i.e. not -9. Note that I have seen a few times where even that didn't work due to the process being hung waiting in some loop for data on a network connection. If I recall correctly, killing the client process took care of that.

share|improve this answer
add comment

if you have a recent Postgres you can try pg_terminate_backend instead.

share|improve this answer
add comment

pg_cancel_backend is equivalent to sending SIGINT to the process. If that doesn't work you could try SIGQUIT. Either by using kill directly or pg_ctl kill.

share|improve this answer
    
+1 PostgreSQL uses a process per connection, so you can kill a process without much danger of affecting other connections. I'm not sure if there's any chance of data corruption, but I'd really doubt it. –  David Pashley Jul 2 '09 at 22:26
    
I thought sending kill -9 to a postgres process was disastrous, in that it could put the database in recovery mode which might take it out of commission for many minutes. –  mike Jul 2 '09 at 22:31
    
stackoverflow.com/questions/920956/… It seems I am wrong. –  Bribles Jul 2 '09 at 22:46
    
That link regards sending kill -9 to the server. I'm talking about a single query's pid. Or are those the same thing? –  mike Jul 2 '09 at 23:16
    
Each connection spawns off a postmaster. You can send your signals to the postmaster, but don't send a SIGKILL (killall postmaster) as this will drop whatever it was doing like a hot rock. –  Avery Payne Jul 2 '09 at 23:38
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.