Have you ever been tempted to “kill -9” a non-behaving PostgreSQL client session that won’t die via “pg_cancel_backend()” or “pg_terminate_backend()“?

Let’s just say that at first you may believe (incorrectly) that it will affect only the single session you’ve killed, but in reality it’s an incredibly bad idea… as the result will be ALL of your client sessions being reset and the database crashing.

The postmaster detects that a process has exited abnormally:

16:46:44 UTC [15642]: LOG:  server process (PID 15658) was terminated by signal 9: Killed
16:46:44 [15642]: LOG:  terminating any other active server processes
16:46:44 [15648]: DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

The end result is that the entire PostgreSQL cluster resets itself and does crash recovery.

So in short, if you can’t kill a session using the standard PostgreSQL Server Signalling Functions, then the only sensible option is to issue a restart during a maintenance window:

psql -c "checkpoint;"
pg_ctl restart -m fast

Stay safe, and keep your fingers off the trigger!

Greg.