Sometimes you want to kill a process in Postgres, it might be stuck after lun run, or it's blocking other processes, no matter if it's stuck or active, here is a quick recipe to terminate a process in Postgres.

Find the process id of the query

This is important, you don't want kill a process that you want to keep it running.

select datname,pid,state,query from pg_stat_activity order by query_start ASC;

datname |  pid  | usename  | application_name | client_hostname | state | query                                                 
   
--------+-------+----------+------------------+-----------------+-------+-----------------------------------------
chimera | 13728 | chimera  | psql             |    | active| SELECT p.path FROM t_inodes i, paths p
...

In the query above, the most important info we want to get is the process pid, other columns are to help you identify the query.

Terminate the process id you identified

Replace the pid with the pid you found, 13728

select pg_cancel_backend(pid);

Or

select pg_terminate_backend(pid);

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. 

Check if the query still running

select datname,pid,state,query from pg_stat_activity order by query_start ASC;


The quick recipe has been tested on pg93 and pg94.

What to know all activities in you postgres? see Check current Postgresql activites