PostgreSQL: Show Active Connections (pg_stat_activity Queries)

Run these in psql (for example sudo -u postgres psql):

-- all connections, grouped by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- active connections only (currently running a query)
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- who is connected: database, user, client, state, current query
SELECT pid, datname, usename, client_addr, state, now() - query_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity WHERE backend_type = 'client backend' ORDER BY query_start;

-- how close you are to the limit
SELECT count(*) AS used, current_setting('max_connections')::int AS max FROM pg_stat_activity;

From the shell, without opening an interactive session:

sudo -u postgres psql -Atc "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';"

active means a query is running right now; idle is an open connection waiting for work (normal with connection pools); idle in transaction is a connection holding a transaction open - many of those, or old ones, block vacuum and hold locks.

For comprehensive database monitoring, see Database Performance Monitoring. For troubleshooting connection issues, see Database Connection Timeout Errors.

Why Checking PostgreSQL Connections Matters

PostgreSQL connection management is critical for database performance. Too many connections can exhaust resources, while connection leaks can cause connection pool exhaustion. Monitoring connections helps maintain optimal database performance.

Method 1: Check Active Connections with psql

Count Active Connections

# Connect to PostgreSQL
psql -U postgres

# Count active connections
SELECT count(*) FROM pg_stat_activity;

# Count connections by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

# Count connections by database
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

View Connection Details

# View all active connections
SELECT pid, usename, datname, state, query FROM pg_stat_activity;

# View connections with details
SELECT pid, usename, datname, state, client_addr, application_name, query_start FROM pg_stat_activity WHERE state = 'active';

# View idle connections
SELECT pid, usename, datname, state, state_change FROM pg_stat_activity WHERE state = 'idle';

Check Max Connections

# Check max connections setting
SHOW max_connections;

# Check current vs max connections
SELECT 
  (SELECT count(*) FROM pg_stat_activity) as current_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_connections;

Method 2: Check Connections from Command Line

Quick Connection Count

# Count active connections
psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# Count connections by state
psql -U postgres -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

# Check max connections
psql -U postgres -c "SHOW max_connections;"

Monitor Connection Usage

# Check connection usage percentage
psql -U postgres -c "SELECT 
  (SELECT count(*) FROM pg_stat_activity)::float / 
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections')::float * 100 as usage_percent;"

Method 3: Automated Connection Monitoring with Zuzia.app

Manual checks work while you are looking. To keep running them without you, Zuzia uses its agent and scheduled tasks.

How it works in Zuzia

• Install the Zuzia agent on the server with one command. CPU, RAM, disk, load and network metrics start reporting on their own, with alerts on a threshold, on drift from the server's normal, or on a disk fill-up forecast.
• Save the commands from this guide as a scheduled task and choose how often it runs, from every minute to once a year. The agent runs it on every host you pick.
• If a command fails, you get an alert right away by email, Slack, Discord, push (through your Pushover account) or SMS (through your Vonage account).
• The output of every run is kept, so you can look back at what changed. On paid plans, AI can read the output and alert you only when it spots a problem.

Setting it up

1. Add the server in the Zuzia dashboard and install the agent.
2. Create a scheduled task with the check commands below, then choose the hosts and how often it runs.
3. Choose your notification channels. Turn on maintenance mode during planned work so it doesn't trigger alerts.

Best Practices for PostgreSQL Connection Monitoring

1. Monitor Connection Count Regularly

• Track active connection count continuously
• Alert when connections approach max_connections
• Monitor connection trends over time
• Plan capacity upgrades based on data

2. Monitor Connection States

• Track active vs idle connections
• Identify connection leaks
• Monitor long-running connections
• Optimize connection usage

3. Set Appropriate Max Connections

• Configure max_connections based on server resources
• Monitor connection usage vs limits
• Adjust limits based on actual usage
• Plan upgrades before limits are reached

4. Optimize Connection Usage

• Implement connection pooling
• Close connections properly
• Monitor connection leaks
• Optimize application connection usage

Troubleshooting Connection Issues

Step 1: Identify Connection Problems

When connection issues occur:

# Check current connection count
psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# Check max connections
psql -U postgres -c "SHOW max_connections;"

# View connection details
psql -U postgres -c "SELECT pid, usename, datname, state, query FROM pg_stat_activity;"

Step 2: Resolve Connection Issues

Based on investigation:

1. Kill Idle Connections:

   # Kill idle connections older than 1 hour
   psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND state_change < now() - interval '1 hour';"
   

2. Increase Max Connections:

   # Edit postgresql.conf
   # max_connections = 200
   sudo systemctl restart postgresql
   

3. Fix Connection Leaks:

   • Review application code
   • Fix unclosed connections
   • Implement connection pooling

FAQ: Common Questions About PostgreSQL Connections

How many connections can PostgreSQL handle?

PostgreSQL can handle connections up to max_connections limit (default 100). Actual capacity depends on server resources. Monitor connection usage and adjust max_connections based on your needs.

How often should I check PostgreSQL connections?

For production databases, continuous automated monitoring is essential. Zuzia.app can check connections every few minutes, alerting you when connection usage exceeds thresholds.

What should I do when connections are exhausted?

When connections are exhausted, kill idle connections, increase max_connections if needed, fix connection leaks in applications, and optimize connection usage. Zuzia.app can help automate this monitoring.

How do I identify connection leaks?

Identify connection leaks by monitoring connection count over time, checking for connections that don't close, reviewing application code for unclosed connections, and using connection pooling to manage connections properly.

Related guides, recipes, and problems

• Related guides

  • guides--monitoring-database-performance-mysql-postgresql.md
  • guides--server-performance-monitoring-best-practices.md

• Related recipes

  • recipes--monitor-mysql-slow-queries-performance.md
  • recipes--monitor-database-connection-pool-usage.md
  • recipes--monitor-postgresql-database-size.md

• Related problems

  • problems--database-connection-timeout-errors.md
  • problems--server-slow-performance-issues.md