Home / Database / PostgreSQL Too Many Connections: Max Connections Limit

Database

PostgreSQL Too Many Connections: Max Connections Limit

Resolve PostgreSQL 'too many connections' errors by managing connection limits and optimizing connection pooling.

Published: Nov 15, 20259 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

# PostgreSQL Too Many Connections: Max Connections Limit

Your application is throwing errors and users can't connect:

bash

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for non-replication superuser connections

PostgreSQL has a hard limit on concurrent connections. When you hit this limit, new connections are refused. Let's diagnose and fix this systematically.

Introduction

This article covers troubleshooting steps and solutions for PostgreSQL Too Many Connections: Max Connections Limit. The error typically occurs in production environments and can cause service disruptions if not addressed promptly.

Symptoms

Common error messages include:

bash

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for non-replication superuser connections

```sql -- Current max connections setting SHOW max_connections;

-- Connections currently in use SELECT count(*) FROM pg_stat_activity;

-- Available slots SELECT setting::int AS max_connections, (SELECT count(*) FROM pg_stat_activity) AS current_connections, setting::int - (SELECT count(*) FROM pg_stat_activity) AS available_slots FROM pg_settings WHERE name = 'max_connections'; ```

```bash # Superusers have reserved connections psql -U postgres -d postgres

# If that fails, use single-user mode postgres --single -D /var/lib/postgresql/data postgres ```

Common Causes

Configuration misconfiguration
Missing or incorrect credentials
Network connectivity issues
Version compatibility problems
Resource exhaustion or limits
Permission or access denied

Step-by-Step Fix

1.Check logs for specific error messages
2.Verify configuration settings
3.Test network connectivity
4.Review recent changes
5.Apply corrective action
6.Verify the fix

Understanding the Connection Limit

PostgreSQL's max_connections parameter sets the maximum number of concurrent connections. The default is typically 100.

Check your current limits:

```sql -- Current max connections setting SHOW max_connections;

-- Connections currently in use SELECT count(*) FROM pg_stat_activity;

Immediate Resolution

Free Up Connections Now

If you're locked out, first try to connect as superuser (reserved slots exist for this):

```bash # Superusers have reserved connections psql -U postgres -d postgres

# If that fails, use single-user mode postgres --single -D /var/lib/postgresql/data postgres ```

Once connected, find and terminate problematic connections:

```sql -- View all connections SELECT pid, usename, application_name, client_addr, state, query_start, query FROM pg_stat_activity ORDER BY query_start;

-- Find idle connections SELECT pid, usename, application_name, client_addr, state, query_start, now() - query_start AS idle_duration FROM pg_stat_activity WHERE state = 'idle' ORDER BY idle_duration DESC;

-- Terminate specific connection SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

-- Terminate all idle connections older than 10 minutes SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND query_start < now() - interval '10 minutes' AND pid <> pg_backend_pid();

-- Terminate all connections from a specific user SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'problematic_user' AND pid <> pg_backend_pid(); ```

Increase Connection Limit (Temporary Fix)

To allow more connections, modify postgresql.conf:

```sql -- Check current value SHOW max_connections;

-- View config file location SHOW config_file; ```

Edit the configuration:

conf

# postgresql.conf
max_connections = 200

Important: Each connection uses memory. Ensure you have enough:

```sql -- Check shared_buffers and work_mem SHOW shared_buffers; SHOW work_mem;

-- Rough calculation for max_connections: -- Total memory needed ≈ max_connections × (work_mem + maintenance_work_mem) + shared_buffers ```

After editing, restart PostgreSQL:

bash

sudo systemctl restart postgresql

Finding the Root Cause

Analyze Connection Sources

```sql -- Connections by user SELECT usename, count(*) AS connection_count FROM pg_stat_activity GROUP BY usename ORDER BY connection_count DESC;

-- Connections by application SELECT application_name, count(*) AS connection_count FROM pg_stat_activity GROUP BY application_name ORDER BY connection_count DESC;

-- Connections by client IP SELECT client_addr, count(*) AS connection_count FROM pg_stat_activity WHERE client_addr IS NOT NULL GROUP BY client_addr ORDER BY connection_count DESC;

-- Connections by database SELECT datname, count(*) AS connection_count FROM pg_stat_activity GROUP BY datname ORDER BY connection_count DESC; ```

Identify Connection Leaks

```sql -- Find connections idle for extended periods SELECT pid, usename, application_name, client_addr, state, query_start, now() - query_start AS idle_time FROM pg_stat_activity WHERE state IN ('idle', 'idle in transaction', 'idle in transaction (aborted)') AND query_start < now() - interval '5 minutes' ORDER BY idle_time DESC;

-- Find connections in transaction for too long SELECT pid, usename, state, query_start, now() - query_start AS transaction_duration FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY transaction_duration DESC; ```

Check for Prepared Transactions

```sql -- Prepared transactions hold connections SELECT * FROM pg_prepared_xacts;

-- If you find old prepared transactions, commit or rollback -- COMMIT PREPARED 'transaction_id'; -- ROLLBACK PREPARED 'transaction_id'; ```

Connection Per-User Limits

PostgreSQL allows setting connection limits per user:

```sql -- Set user connection limit ALTER USER appuser WITH CONNECTION LIMIT 20;

-- Set superuser limit (though superusers have reserved slots) ALTER USER admin WITH CONNECTION LIMIT 10;

-- Set per-database limit ALTER DATABASE mydb WITH CONNECTION_LIMIT 50;

-- View current limits SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit >= 0; ```

Long-Term Solution: Connection Pooling

Increasing max_connections is a band-aid. Connection pooling is the proper solution.

Option 1: PgBouncer (Recommended)

PgBouncer sits between your application and PostgreSQL, managing a pool of connections:

```bash # Install on Ubuntu/Debian sudo apt install pgbouncer

# Install on RHEL/CentOS sudo yum install pgbouncer ```

Configuration (/etc/pgbouncer/pgbouncer.ini):

```ini [databases] mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 500 default_pool_size = 25 min_pool_size = 5 reserve_pool_size = 5 reserve_pool_timeout = 3

[users] appuser = max_user_connections=50 ```

Create userlist.txt:

```bash # Generate password hash sudo -u postgres psql -c "SELECT '\"appuser\" \"' || passwd || '\"' FROM pg_shadow WHERE usename = 'appuser';"

# Or manually echo '"appuser" "SCRAM-SHA-256$..."' | sudo tee /etc/pgbouncer/userlist.txt ```

Start PgBouncer:

bash

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

Connect through PgBouncer:

bash

psql -h localhost -p 6432 -U appuser -d mydb

Update your application's connection string:

bash

postgresql://appuser:password@localhost:6432/mydb

Option 2: Built-in Application Pooling

Many frameworks support connection pooling:

Node.js (node-postgres): ``javascript const { Pool } = require('pg'); const pool = new Pool({ host: 'localhost', database: 'mydb', user: 'appuser', password: 'password', max: 20, // Maximum connections in pool idleTimeoutMillis: 30000, // Close idle connections after 30s connectionTimeoutMillis: 2000, });

Python (psycopg2): ```python import psycopg2 from psycopg2 import pool

connection_pool = psycopg2.pool.ThreadedConnectionPool( minconn=5, maxconn=20, host='localhost', database='mydb', user='appuser', password='password' ) ```

Java (HikariCP): ``java HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:postgresql://localhost/mydb"); config.setUsername("appuser"); config.setPassword("password"); config.setMaximumPoolSize(20); config.setMinimumIdle(5); config.setIdleTimeout(30000); HikariDataSource dataSource = new HikariDataSource(config);

Monitoring Connections

Set Up Connection Monitoring

```sql -- Create a view for connection monitoring CREATE VIEW connection_stats AS SELECT datname, usename, application_name, client_addr, state, count(*) OVER (PARTITION BY datname) AS db_connections, count(*) OVER (PARTITION BY usename) AS user_connections, count(*) OVER () AS total_connections, (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections FROM pg_stat_activity;

-- Query it SELECT * FROM connection_stats WHERE state = 'active'; ```

Create an Alert Function

```sql CREATE OR REPLACE FUNCTION check_connection_limit() RETURNS TABLE(alert_level text, current_count int, max_allowed int) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT CASE WHEN count(*) > (SELECT setting::int * 0.9 FROM pg_settings WHERE name = 'max_connections') THEN 'CRITICAL' WHEN count(*) > (SELECT setting::int * 0.75 FROM pg_settings WHERE name = 'max_connections') THEN 'WARNING' ELSE 'OK' END, count(*)::int, (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') FROM pg_stat_activity; END; $$;

-- Run check SELECT * FROM check_connection_limit(); ```

Configuration Tuning

When increasing max_connections, adjust related settings:

```conf # postgresql.conf

# Connection limit max_connections = 200

# Reserved for superusers (default 3) superuser_reserved_connections = 5

# Memory per connection affects total RAM usage work_mem = 4MB # Lower if many connections maintenance_work_mem = 64MB

# Shared memory shared_buffers = 256MB # Should be ~25% of RAM

# Connection timeout settings tcp_keepalives_idle = 60 tcp_keepalives_interval = 10 tcp_keepalives_count = 5 ```

Calculate memory requirements:

``` Approximate memory usage: = shared_buffers + (max_connections × work_mem) + (max_connections × maintenance_work_mem)

Example with max_connections=200, work_mem=4MB, shared_buffers=256MB: = 256MB + (200 × 4MB) + (200 × 64MB) = 256MB + 800MB + 12.8GB = ~14GB ```

Verification

After implementing fixes:

```bash # Check max_connections is applied psql -U postgres -c "SHOW max_connections;"

# Monitor connections over time watch -n 5 'psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"'

# Check for PgBouncer psql -h localhost -p 6432 -U appuser -d mydb -c "SELECT 1;"

# Verify connection pool is working psql -U postgres -c "SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;" ```

Verification

Action	Command
Check max connections	`SHOW max_connections;`
Count current connections	`SELECT count(*) FROM pg_stat_activity;`
Find idle connections	`SELECT * FROM pg_stat_activity WHERE state='idle';`
Terminate connection	`SELECT pg_terminate_backend(pid);`
Set user limit	`ALTER USER name WITH CONNECTION LIMIT n;`
Reload config	`SELECT pg_reload_conf();`
Restart PostgreSQL	`sudo systemctl restart postgresql`

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

PostgreSQL Too Many Connections: Max Connections Limit PostgreSQL Too Many Connections: Max Connections Limit Database PostgreSQL Too Many Connections: Max Connections Limit troubleshooting PostgreSQL Too Many Connections: Max Connections Limit fix Resolve PostgreSQL 'too many connections' errors by managing connection limits and optimizing connection pooling Database Resolve PostgreSQL 'too many connections' errors by managing connection limits and optimizing connection pooling

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Nov 15, 2025

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

PostgreSQL Too Many Connections: Max Connections Limit

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Understanding the Connection Limit

Immediate Resolution

Free Up Connections Now

Increase Connection Limit (Temporary Fix)

Finding the Root Cause

Analyze Connection Sources

Identify Connection Leaks

Check for Prepared Transactions

Connection Per-User Limits

Long-Term Solution: Connection Pooling

Option 1: PgBouncer (Recommended)

Option 2: Built-in Application Pooling

Monitoring Connections

Set Up Connection Monitoring

Create an Alert Function

Configuration Tuning

Verification

Verification

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

PostgreSQL Too Many Connections: Max Connections Limit

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Understanding the Connection Limit

Immediate Resolution

Free Up Connections Now

Increase Connection Limit (Temporary Fix)

Finding the Root Cause

Analyze Connection Sources

Identify Connection Leaks

Check for Prepared Transactions

Connection Per-User Limits

Long-Term Solution: Connection Pooling

Option 1: PgBouncer (Recommended)

Option 2: Built-in Application Pooling

Monitoring Connections

Set Up Connection Monitoring

Create an Alert Function

Configuration Tuning

Verification

Verification

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading