kill => linux utility to send a signal to a process
pid => a process ID
Using the linux kill utility, a user with sufficient privileges could
terminate a running Postgres query specified by process ID (pid). This is a bad idea though, because it will cause PostgreSQL to panic. It is useful to know the pid of the query, as we shall see below. To find the pid of a query, execute the following:
## PG10 Syntax
SELECT datname,usename,pid,client_addr,wait_event,query_start,query
FROM pg_stat_activity ;
or simply
SELECT pg_backend_pid() ;
With the pid, a user might try a termination signal first (bad idea):
kill -SIGTERM pid
kill -15 pid
Or a SIGKILL signal could be issued (also a bad idea). The Linux scheduler executes the command, the process is never notified. There are three equivalent forms :
kill -SIGKILL pid
kill -KILL pid
kill -9 pid
PostgreSQL provides ways to stop a running query (good idea).
pg_terminate_backend(pid int) boolean Terminate a backend
pg_cancel_backend(pid int) boolean Cancel a backend's current query
Lastly, the Postgres utility pg_ctl can issue the SIGKILL signal, and is available on non-Linux platforms.
see
http://www.postgresonline.com/journal/archives/134-Terminating-Annoying-Back-Ends.html
http://www.postgresql.org/docs/9.1/static/functions-admin.html
http://www.postgresql.org/docs/9.1/static/app-pg-ctl.html
Postgres 9.2 release notes:
* Allow non-superusers to use pg_cancel_backend() and pg_terminate_backend() on other sessions belonging to the same user (Magnus Hagander, Josh Kupershmidt, Dan Farina) Previously only superusers were allowed to use these functions.
Peter Eisentraut writes:
“You shouldn’t run pg_ctl directly under Ubuntu/Debian. Use pg_ctlcluster instead, which is installed by postgresql-common. See its man page for documentation.”
example: sudo pg_ctlcluster 10 main stop --mode immediate
alt: sudo systemctl stop postgresql@10-main
— postscript from IRC chat
peerce pg_ctl kill TERM sends things a SIGTERM, they have to be listening for it to exit .. SIGKILL, now that will kill it, but thats usually very bad for postgres as it causes immediate exit without any cleanup.
RhodiumToad use pg_cancel_backend (which sends SIGINT, not SIGTERM) and pg_terminate_backend (which does send SIGTERM) exclusively. if you find something that doesn’t die in the face of either of those, the next stop is pg_ctl restart -mimmediate. do not ever send SIGKILL to postgres processes. (if you do kill a backend with -KILL, then pg will do a panic restart, and if you mess up and kill the postmaster with -KILL, you end up with a serious mess)
dbb if you send a kill signal to a query it will certainly panic postgresql ?
RhodiumToad yes
RhodiumToad because pg then has no way to know what the dead process was doing with shared memory, so the only option is to SIGKILL every other process, throw the shared memory segment away, and restart with a new one, which requires doing a recovery from WAL. pg_ctl stop -mimmediate or restart -mimmediate also does the recovery from WAL, but in a slightly more controlled fashion, for a query not to die from pg_terminate_backend after a reasonably short time interval is a bug, though (last one I saw was due to index corruption making vacuum go round in circles) pg_cancel_backend will likewise kill any query that isn’t actually catching a query_canceled exception