Step-by-step guide to check PostgreSQL active connections. Monitor connection count, identify connection issues, and ensure optimal database performance.

Last updated: 2026-01-11

How to Check PostgreSQL Active Connections on Linux Server

Monitor PostgreSQL active connections to track database usage, identify connection issues, and prevent connection pool exhaustion. This guide shows you how to check active connections and set up automated monitoring.

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

Set up automated monitoring to track PostgreSQL connections continuously and receive alerts when connection usage exceeds thresholds.

Step 1: Add Connection Monitoring Command

Log in to Zuzia.app Dashboard
- Access your Zuzia.app account
- Navigate to your server
- Click "Add Scheduled Task"
Configure Connection Check Command
```
psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"
```
- Set execution frequency (every 5-10 minutes)
- Configure alerts when connection count increases

Step 2: Configure Alert Thresholds

Warning: Connection usage > 70% of max_connections
Critical: Connection usage > 85% of max_connections
Emergency: Connection usage > 95% of max_connections

Step 3: Monitor Connection Details

Add command to check connection details:

# Check connection details
psql -U postgres -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

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:

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';"

Increase Max Connections:

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

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
- Database Performance Monitoring - MySQL and PostgreSQL Best Practices
- Best Practices for Server Performance Monitoring - Essential Guide to Metrics, Tools, and Strategies
Related recipes
Related problems
- Database Connection Timeout Errors - Emergency Troubleshooting Guide
- Server Slow Performance Issues

How to Check PostgreSQL Active Connections on Linux Server