How to Show Connections in PostgreSQL

Shihab Sikder Feb 02, 2024

PostgreSQL PostgreSQL Connection

Status of the Connections/Queries in PostgreSQL
Drop a Database in PostgreSQL

The pg_stat_activity is a database view. The view is a virtual table, and it’s been used for security reasons.

It stores the query based on one or more tables in the database rather than writing the same query repeatedly.

Status of the Connections/Queries in PostgreSQL

The pg_stat_activity shows information related to the current activity. There’s a list of column types in the pg_stat_activity.

Among those, all are not necessary. Let’s say you want to see the active connection in the psql.

So you can run the following command.

SELECT
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active';

Output:

postgres-# WHERE state = 'active';
  pid  | datname  | usename  | application_name | client_hostname | client_port |        backend_start         |          query_start          |          query          | state
-------+----------+----------+------------------+-----------------+-------------+------------------------------+-------------------------------+-------------------------+--------
 24256 | postgres | postgres | psql             |                 |        1068 | 2022-04-02 16:03:07.20189+06 | 2022-04-02 16:03:10.382665+06 | SELECT                 +| active
       |          |          |                  |                 |             |                              |                               |     pid                +|
       |          |          |                  |                 |             |                              |                               |     ,datname           +|
       |          |          |                  |                 |             |                              |                               |     ,usename           +|
       |          |          |                  |                 |             |                              |                               |     ,application_name  +|
       |          |          |                  |                 |             |                              |                               |     ,client_hostname   +|
       |          |          |                  |                 |             |                              |                               |     ,client_port       +|
       |          |          |                  |                 |             |                              |                               |     ,backend_start     +|
       |          |          |                  |                 |             |                              |                               |     ,query_start       +|
       |          |          |                  |                 |             |                              |                               |     ,query             +|
       |          |          |                  |                 |             |                              |                               |     ,state             +|
       |          |          |                  |                 |             |                              |                               | FROM pg_stat_activity  +|
                       | WHERE state = 'active'; |
(1 row)


postgres=#

There’s the list of column types. Some of them we used in the above SQL command.

Here’s the description in the table.

Column Name	Description
`datname`	name of the database
`application_name`	Application connected to this backend.
`client_host-name`	For `localhost`, it will be blank, but there’ll be a hostname for a remote connection.
`client_port`	The TCP port that the client uses for communication with the backend.
`backend_start`	This timestamp shows when the client is connected.
`query_start`	This timestamp shows when this query started. If the database is disabled, it will show when the last query started.
`query`	This text shows the actual query.
`status`	This shows the status of the database.

Here are all the column names and details about it. Also, you can find all the views from this official documentation.

Drop a Database in PostgreSQL

The general command to drop a database is like the following.

DROP DATABASE [IF EXISTS] <database_name>;

But the problem can arise when you have an active connection to the database. In that case, you need to do the following.

First, check if the database is active or not. Using the following query, you can see all the details of a particular database.
```
SELECT *
FROM pg_stat_activity
WHERE datname = <name of the database>;
```
Then, you need to kill the process of that database. Let’s say you have an active database named Shop.

Now let’s kill the process of that database.
```
SELECT pg_terminate_backend (pid)
FROM pg_stat_activity
WHERE pg_stat_activity = 'Shop';
```
Now, you’ve successfully terminated the process.
Perform the DROP command like the following.
```
DROP DATABASE Shop
```

By performing these steps, you should be able to drop any active database.

Author: Shihab Sikder

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

Status of the Connections/Queries in PostgreSQL

Drop a Database in PostgreSQL

Related Article - PostgreSQL Connection