How do I find the PID of the session I want to kill?
To find out the PID of the current session, run
SELECT pg_backend_pid();
but of course you cannot kill that one with this function.
This query lists the PIDs of currently active processes, and the SQL commands they are running:
SELECT procpid, current_query FROM pg_stat_activity;
procpid | current_query
---------+------------------------------------------------------
2033 | <IDLE>
2478 | select procpid, current_query from pg_stat_activity;
(2 rows)
You can also try using your operating system's process listing:
$ ps feu -C postmaster -C postgres USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres 1402 0.0 0.2 63748 4960 pts/8 S 20:05 0:00 postmaster PGDATA=/pgsql/install/83 postgres 1409 0.0 0.0 63748 1272 ? Ss 20:05 0:00 \_ postgres: writer process postgres 1410 0.0 0.0 63748 1076 ? Ss 20:05 0:00 \_ postgres: wal writer process postgres 1411 0.0 0.0 63884 1508 ? Ss 20:05 0:00 \_ postgres: autovacuum launcher process postgres 1412 0.0 0.0 34240 1196 ? Ss 20:05 0:00 \_ postgres: stats collector process postgres 2033 0.0 0.2 65236 5768 ? Ss 20:12 0:00 \_ postgres: postgres postgres [local] idle idle