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

3 Answers 3

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

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

share|improve this answer

http://www.postgresql.org/docs/current/static/server-shutdown.html

pg_cancel_backend is equivalent to sending SIGINT to the process.
pg_terminate_backend likewise for SIGTERM, but if pg_cancel_backend isn't working I don't see why pg_terminate_backend would.

If you've tried those options, you could try SIGQUIT. The docs say, "This is recommended only in emergencies."

(If you hate your data and hope it dies, you could use SIGKILL. But I wouldn't.)

You can use either 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

Your Answer

 
discard

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