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?
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
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.
pg_cancel_backend is equivalent to sending SIGINT to the process.
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