14

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?

4 Answers 4

13

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.

8
  • +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. Jul 2, 2009 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, 2009 at 22:31
  • 1
    stackoverflow.com/questions/920956/… It seems I am wrong.
    – Bribles
    Jul 2, 2009 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, 2009 at 23:16
  • 2
    @Bribles please add a warning to your post! SIGQUIT will cause SERIOUS ISSUES if tried. I just did and it caused a whole lot of problems - I wish I could go back in time and stop myself from hitting that Enter key!
    – ADTC
    Feb 7, 2014 at 10:30
9

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.

6

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

1

bribles is correct in his statement above...

IF you are trying to SHUTDOWN the server, for me though:

I'm just trying to remove retired Databases/Schemas, that still have a lingering connection that it won't let go of.

So, to answer your question,

If I have a long-running Postgres query ...

pg_cancel_backend doesn't work...

what should I do?

NOT RELATED to shutting down the server in any way.

I have also seen this behavior of pg_cancel_backend() not working. And wanted to share my working solution.

I have not seen an issue thus far, with any kind of "loss" of data.

Again, I'm not trying to kill Active queries either.

-- I'm logged into as USER "A" with a session or PID of 777777.

-- And going to try and force disconnect another session from USER "A" open as 123456789

-- Which is a sleeping connection, and is why I also look for idle in my queries below.

SELECT * 
FROM pg_stat_activity 
WHERE pid = 123456789 
      AND STATE = 'idle';

-- Attempt 1

SELECT pg_cancel_backend(pid) 
FROM pg_stat_activity 
WHERE pid = 123456789 
      AND STATE = 'idle';

-- Interestingly enough Result states that the cancellation is TRUE but still exists.

SELECT * 
FROM pg_stat_activity 
WHERE pid = 123456789 
      AND STATE = 'idle';

-- Attempt 2

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid = 123456789 
      AND STATE = 'idle';

-- And Now it doesn't exist..

SELECT * 
FROM pg_stat_activity 
WHERE pid = 123456789 
      AND STATE = 'idle';

-- NOTE: I tried to use ridiculous pid #'s to help prevent people from copying & pasting and wrecking their lives.

-- NOTE: By default postgres will ONLY allow you to kill processes running under YOUR logging in USER,

-- NOTE: but you knew that already.

Hope this helps. =)

~Jay

You must log in to answer this question.