How to do it…

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.

To find the PID of a user, we can look at the pg_stat_activity view. We can use it in a query, like this:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE ...

There are a couple of things to note if you run this query. If the WHERE clause doesn't match any sessions, then you won't get any output from the query. Similarly, if it matches multiple rows, you will get a fairly useless result, that is, a list of Boolean true values. Unless you are careful enough to exclude your own session from the query, you will disconnect yourself! What's even funnier is that you'll disconnect yourself halfway through disconnecting the other users, as the query will run pg_terminate_backend() in the order in which sessions are returned from the outer query.

Therefore, I suggest a safer and more useful query that gives a useful response in all cases, which is as follows:

postgres=# SELECT count(pg_terminate_backend(pid))
FROM pg_stat_activity
WHERE usename NOT IN
(SELECT usename
 FROM pg_user
WHERE usesuper);
 count
-------
     1

This is assuming that superusers are performing administrative tasks.

Other good filters might be the following:

WHERE application_name = 'myappname'
WHERE wait_event_type IS NOT NULL AND wait_event_type != 'Activity'
WHERE state = 'idle in transaction'
WHERE state = 'idle'