Whenever I inspect a system, there is a system view I prefer to inspect first before digging deeper. I am, of course, talking about pg_stat_activity. The idea behind this view is to give you a chance to figure out what is going on right now.

Here is how it works:

test=# d pg_stat_activity 
 View "pg_catalog.pg_stat_activity"
 Column           |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 datid            | oid                      |           |          | 
 datname          | name                     |           |          | 
 pid              | integer                  |           |          | 
 usesysid         | oid                      |           |          | 
 usename          | name                     |           |          | 
 application_name | text                     |           |          | 
 client_addr      | inet                     |           |          | 
 client_hostname  | text                     |           |          | 
 client_port      | integer                  |           |          | 
 backend_start    | timestamp with time zone |           |          | 
 xact_start       | timestamp with time zone |           |          | 
 query_start      | timestamp with time zone |           |          | 
 state_change     | timestamp with time zone |           |          | 
 wait_event_type  | text                     |           |          | 
 wait_event       | text                     |           |          | 
 state            | text                     |           |          | 
 backend_xid      | xid                      |           |          | 
 backend_xmin     | xid                      |           |          | 
 query            | text                     |           |          | 
 backend_type     | text                     |           |          | 

Furthermore, pg_stat_activity will provide you with one line per active connection. You will see the internal object ID of the database (datid), the name of the database somebody is connected to, and the process ID serving this connection (pid). On top of that, PostgreSQL will tell you who is connected (usename; note the missing r) and that user's internal object ID (usesysid).

Then, there is a field called application_name, which is worth commenting on a bit more extensively. In general, application_name can be set freely by the end user:

test=# SET application_name TO 'www.cybertec-postgresql.com'; 
SET
test=# SHOW application_name;
   application_name
-----------------------
 www.cybertec-postgresql.com
(1 row)

The point is this: let's assume that thousands of connections are coming from a single IP. Can you, as the administrator, tell what a specific connection is really doing right now? You might not know all the SQL off by heart. If the client is kind enough to set an application_name parameter, it is a lot easier to see what the purpose of a connection really is. In my example, I have set the name to the domain the connection belongs to. This makes it easy to find similar connections that might cause similar problems.

The next three columns (client_) will tell you where a connection comes from. PostgreSQL will show IP addresses and (if it has been configured to) even hostnames.

Additionally, backend_start will tell you when a certain connection has started, and xact_start indicates when a transaction has started. Then, there is query_start and state_change. Back in the dark old days, PostgreSQL would only show active queries. During a time when queries took a lot longer than today, this made sense. On modern hardware, OLTP queries might only consume a fraction of a millisecond, and therefore it is hard to catch such queries doing potential harm. The solution was to either show the active query or the previous query that was executed by the connection you are looking at.

Here is what you might see:

test=# SELECT pid, query_start, state_change, state, query   
FROM pg_stat_activity; 
... 
-[ RECORD 2 ]  +--------------------------------------------
pid            | 28001 
query_start    | 2018-11-05 10:03:57.575593+01
state_change   | 2018-11-05 10:03:57.575595+01 
state          | active 
query          | SELECT pg_sleep(10000000);

In this case, you can see that pg_sleep is being executed in a second connection. As soon as this query is terminated, the output will change:

-[ RECORD 2 ]+--------------------------------------------
pid          | 28001 
query_start  | 2018-11-05 10:03:57.575593+01 
state_change | 2018-11-05 10:05:10.388522+01 
state        | idle 
query        | SELECT pg_sleep(10000000); 

The query is now marked as idle. The difference between state_change and query_start is the time the query needs to execute.

Therefore, pg_stat_activity will therefore give you a great overview of what is going on in your system right now. The new state_change field makes it a lot more likely to spot expensive queries.

The question now is this: once you have found bad queries, how can you actually get rid of them? PostgreSQL provides two functions to take care of these things: pg_cancel_backend and pg_terminate_backend. The pg_cancel_backend function will terminate the query, but will leave the connection in place.

The pg_terminate_backend function is a bit more radical and will kill the entire database connection, along with the query.

If you want to disconnect all other users but yourself, here is how you can do that:

test=# SELECT pg_terminate_backend(pid) 
       FROM  pg_stat_activity 
       WHERE pid <> pg_backend_pid() 
             AND backend_type = 'client backend' 
 pg_terminate_backend 
----------------------  
 t 
 t 
(2 row) 

If you happen to be kicked out, the following message will be displayed:

test=# SELECT pg_sleep(10000000); 
FATAL: terminating connection due to administrator command server closed the connection unexpectedly

This probably means that the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.