How to Show Connections in PostgreSQL

Shihab Sikder Feb 02, 2024
  1. Status of the Connections/Queries in PostgreSQL
  2. Drop a Database in PostgreSQL
How to Show Connections 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.

  1. 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>;
    
  2. 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.

  3. Perform the DROP command like the following.

    DROP DATABASE Shop
    

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

Shihab Sikder avatar Shihab Sikder avatar

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

Related Article - PostgreSQL Connection