Sometimes it happens: you need, as a DBA, to be harsh and terminate a backend, that is a user connection.
kill that backend!
There are two main ways to do that:
- use the operating system
kill(1)command to, well, kill such process;
- use PostgreSQL administrative functions like
pg_terminate_backend()or the more polite `pg_cancel_backend()**.
What is the difference between the two functions?
Quite easy to understand:
SIGINTto the backend process, that is it asks politely to exit. It is the equivalent of a standard
kill -INTagainst the process.
But, what does it mean to aks politely to exit? It means to cancel the current query, that is it does not terminates the user session, rather the user interaction. That is why it is mapped to
SIGINT, the equivalent to
CTRL-c(interrupt by keyboard).
On the other hand,
SIGTERMto the process, that is equivalent to
kill -TERMand forces brutally the process to exit.
Now, Kill it!Which method should you use?
If you are absolutely sure about what you are doing, you can use whatever method you want!
But sometimes caffeine is at a too low level in your body to do it right, you should use the PostgreSQL way! There are at least two good reasons to use the PostgreSQL administrative functions:
- you don’t need access to the server, i.e., you don’t need an operating system shell;
- you will not accidentally kill another process.
The first reason is really simple to understand, and improves security about the machine hosting PostgreSQL, at least in my opinion.
The second reason is a little less obvious, and relies on the fact that
pg_terminate_backend()act only against processes within the PostgreSQL space, that is only processes spawn by the
Let’s see this in action: imagine we select the wrong process to kill, like
174601that is running Emacs on the server.
% ssh luca@miguel 'ps -aux | grep emacs' luca 174601 1.6 4.6 320068 46584 pts/0 S+ 08:40 0:04 emacs % psql -h miguel -U postgres -c "SELECT pg_cancel_backend( 174601 );" testdb WARNING: PID 174601 is not a PostgreSQL server process pg_cancel_backend ------------------- f (1 row) % psql -h miguel -U postgres -c "SELECT pg_terminate_backend( 174601 );" testdb WARNING: PID 174601 is not a PostgreSQL server process pg_terminate_backend ---------------------- f (1 row)
As you can see, there is no way to misbehave against a non PostgreSQL process! The logs provide, of course, the very same warning message:
WARNING: PID 174601 is not a PostgreSQL server process
Now, imagine what happened if the administrator did run something like:
% ssh luca@miguel 'sudo kill 1747601'
The process, in this case Emacs, would have been killed.