What do I do when pg_cancel_backend doesn't work?

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?

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.

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.

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

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