Sometimes, we may need to remove groups of users from the database server for various operational reasons. Here's how.
You can terminate a user's session with the pg_terminate_backend()
function included with PostgreSQL. That function takes the
PID, or the process ID, of the user's session on the server. This process is known as the backend, and it is a different system process from the program that runs the client.
Note
Some of the columns used in this recipe had a different name in version 9.1 and before:
pid
was calledprocpid
query
was calledcurrent_query
The queries in this recipe are written for PostgreSQL 9.4, the most recent stable version at the time of writing this book. They work without changes on versions 9.3 and 9.2. To run them on prior versions, you only have to replace each occurrence of pid
with procpid
and query
with current_query
.
To find the PID of a user, we can look at the pg_stat_activity
view. We can use it in a query, like...