List Active PostgreSQL Connections

Understanding the pg_stat_activity View:

• Columns: It contains various columns that describe the session's details, such as the process ID, user name, database name, current query, and connection status.
• Purpose: This system view provides information about all active sessions in a PostgreSQL database.

Query to List Active Connections:

To retrieve a list of active connections, you can use the following SQL query:

SELECT
    pid,
    client_addr,
    usename,
    datname,
    state,
    query_start,
    query
FROM
    pg_stat_activity
WHERE
    state NOT IN ('idle', 'fast idle');

Breakdown of the Query:

• WHERE clause: Filters the results to only include active connections.
  • state NOT IN ('idle', 'fast idle'): Excludes connections that are idle or in a fast idle state.
• FROM clause: Specifies the system view to query.
• SELECT clause: Specifies the columns you want to retrieve.
  • pid: Process ID of the connection.
  • client_addr: IP address of the client.
  • usename: User name of the connection.
  • datname: Database name the connection is using.
  • state: Current state of the connection (e.g., 'active', 'idle').
  • query_start: Time the current query started.
  • query: The current query being executed.

Additional Considerations:

• Security Considerations: Be cautious when using this query in production environments to avoid exposing sensitive information.
• Monitoring and Troubleshooting: This query is useful for monitoring active connections, identifying potential performance bottlenecks, and troubleshooting connection issues.
• Filtering by Specific Criteria: You can further filter the results based on specific criteria, such as user name, database name, or query content.

Listing Active PostgreSQL Connections: Example Codes

SQL Query

The most direct way to list active connections is using a SQL query. Here's an example:

SELECT
    pid,
    client_addr,
    usename,
    datname,
    state,
    query_start,
    query
FROM
    pg_stat_activity
WHERE
    state NOT IN ('idle', 'fast idle');

This query filters out idle connections and provides information like the process ID, client address, user name, database name, connection state, query start time, and the current query.

Using a Programming Language (Python Example)

You can use a programming language like Python and a PostgreSQL driver (e.g., psycopg2) to execute the query and process the results:

import psycopg2

try:
    conn = psycopg2.connect("dbname=your_database_name user=your_username password=your_password")
    cur = conn.cursor()
    cur.execute("SELECT * FROM pg_stat_activity WHERE state NOT IN ('idle', 'fast idle');")
    rows = cur.fetchall()

    for row in rows:
        print(row)

except psycopg2.Error as e:
    print("Error:", e)

finally:
    if conn:
        conn.close()

Using pgAdmin

If you're using pgAdmin, you can directly view active connections in the "Server Activity" panel of your database.

Using the pg_stat_activity System View

The pg_stat_activity system view provides detailed information about all active sessions in a PostgreSQL database. You can query it directly using SQL or through your programming language.

Key Points:

• Consider using a programming language to automate the process and integrate it into your monitoring or alerting systems.
• You can customize the query to filter results based on specific criteria (e.g., user name, database name, query content).
• The pg_stat_activity view is a powerful tool for monitoring and troubleshooting PostgreSQL connections.

Alternative Methods for Listing Active PostgreSQL Connections

While the SQL query and programming language approaches are common, there are additional methods to list active PostgreSQL connections:

Using the ps Command:

• For Linux/Unix systems:
  • ps -ef | grep postgres

Using PostgreSQL's pg_controldata:

• Provides information about the PostgreSQL cluster:
  • pg_controldata -D /path/to/your/data/directory
  • This command can show the number of active connections and other relevant statistics.

Using PostgreSQL's pg_stat_statements:

• Tracks statistics about SQL statements:
  • SELECT * FROM pg_stat_statements;

• For replication setups:
  • SELECT * FROM pg_stat_replication;
  • This can show information about active replication connections.

Using a PostgreSQL Monitoring Tool:

• Specialized tools:

Using a Database Management System (DBMS) Tool:

• Integrated features:

Choosing the Right Method:

The best method depends on your specific needs and preferences. If you're familiar with SQL and programming, the SQL query or programming language approach might be the most suitable. For system-level information, ps and pg_controldata can be helpful. For detailed statistics and monitoring, specialized tools or DBMS features might be more appropriate.