{"id":620,"date":"2012-02-03T12:32:33","date_gmt":"2012-02-03T20:32:33","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=620"},"modified":"2018-02-28T20:40:07","modified_gmt":"2018-03-01T04:40:07","slug":"killing-a-pg-query-on-linux","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=620","title":{"rendered":"Killing a Pg Query on Linux"},"content":{"rendered":"

\"pg_logo\"<\/p>\n

kill<\/strong> => linux utility to send a signal to a process
\npid<\/strong> => a process ID<\/p><\/blockquote>\n

Using the linux kill<\/code> utility, a user with sufficient privileges could
\nterminate a running Postgres query specified by process ID<\/strong> (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:<\/p>\n

## PG10 Syntax
\nSELECT datname,usename,pid,client_addr,wait_event,query_start,query
\nFROM pg_stat_activity ;
\n<\/code>
\nor simply
\n
\nSELECT pg_backend_pid() ;
\n<\/code><\/p>\n

With the pid, a user might try a termination signal first (bad idea<\/em>):<\/p>\n

kill<\/strong> -SIGTERM pid
\nkill<\/strong> -15 pid<\/p><\/blockquote>\n

Or a SIGKILL<\/strong> signal could be issued (also a bad idea<\/em>). The Linux scheduler executes the command, the process is never notified. There are three equivalent forms :<\/p>\n

kill<\/strong> -SIGKILL pid
\nkill<\/strong> -KILL pid
\nkill<\/strong> -9 pid<\/p><\/blockquote>\n

PostgreSQL<\/strong> provides ways to stop a running query (good idea<\/strong>).<\/p>\n

pg_terminate_backend<\/strong>(pid int) boolean Terminate a backend
\npg_cancel_backend<\/strong>(pid int) boolean Cancel a backend's current query<\/code><\/p>\n

Lastly, the Postgres utility pg_ctl<\/code> can issue the SIGKILL signal, and is available on non-Linux platforms.<\/p>\n

see<\/em><\/p>\n

http:\/\/www.postgresonline.com\/journal\/archives\/134-Terminating-Annoying-Back-Ends.html<\/a><\/p>\n

http:\/\/www.postgresql.org\/docs\/9.1\/static\/functions-admin.html<\/a><\/p>\n

http:\/\/www.postgresql.org\/docs\/9.1\/static\/app-pg-ctl.html<\/a><\/p>\n

Postgres 9.2 release notes:
\n* 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.<\/p>\n

Peter Eisentraut writes:
\n   “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.”
\nexample:<\/em>  sudo pg_ctlcluster 10 main stop --mode immediate<\/code>
\n alt:<\/em>  sudo systemctl stop postgresql@10-main<\/code><\/p>\n

— postscript from IRC chat<\/em><\/p>\n

peerce<\/strong> 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.<\/p>\n

RhodiumToad<\/strong>\tuse 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)<\/p>\n

dbb<\/strong>\tif you send a kill signal to a query it will certainly panic postgresql ?
\nRhodiumToad<\/strong>\tyes<\/p>\n

RhodiumToad<\/strong>\tbecause 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<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/620"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=620"}],"version-history":[{"count":43,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/620\/revisions"}],"predecessor-version":[{"id":3459,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/620\/revisions\/3459"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=620"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}