up vote 1 down vote favorite
share [g+] share [fb]

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?

link|improve this question
feedback

2 Answers

up vote 1 down vote

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.

link|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 1 more comment
feedback
up vote 1 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard