Troubleshooting PostgreSQL Connection Limit Reached: Database Pool Full on Ubuntu 22.04 LTS

Resolve 'PostgreSQL connection limit reached' errors on Ubuntu 22.04 LTS. Diagnose, adjust max_connections, optimize pooling, and prevent database service interruptions.

This guide addresses a common yet critical issue in PostgreSQL environments: hitting the configured maximum connection limit. When your database connection pool is full, applications can no longer establish new connections, leading to service degradation or complete downtime. Users will typically encounter "connection refused" errors or internal server errors (HTTP 5xx) on web applications, signaling that the backend database is unreachable or overwhelmed.

Symptom & Error Signature

When the PostgreSQL connection limit is reached, applications will fail to connect, and the database server will log specific error messages.

Typical Application Log Errors:

For a Ruby on Rails application using pg gem:

PG::ConnectionBad: FATAL:  remaining connection slots are reserved for non-replication superuser connections
    from /var/www/app/shared/bundle/ruby/2.7.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:84:in `initialize'
    ...

For a Python Django application:

django.db.utils.OperationalError: FATAL:  too many connections

For a Node.js application using pg module:

Error: FATAL:  too many connections
    at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:639:13)
    at Connection.handleReadyForQuery (/app/node_modules/pg/lib/connection.js:239:10)
    ...

PostgreSQL Server Log Errors (typically in /var/log/postgresql/postgresql-14-main.log or similar):

2023-10-26 10:30:05 UTC [12345]: [6-1] user=app_user,db=app_db,host=127.0.0.1 FATAL:  too many connections
2023-10-26 10:30:05 UTC [12345]: [6-2] DETAIL:  Sorry, too many clients already.

Or, if all slots are reserved for superusers (e.g., during a maintenance window or extreme load):

2023-10-26 10:30:05 UTC [12345]: [6-1] user=app_user,db=app_db,host=127.0.0.1 FATAL:  remaining connection slots are reserved for non-replication superuser connections

Root Cause Analysis

The "PostgreSQL connection limit reached" error fundamentally means that PostgreSQL cannot accept new client connections because it has hit the max_connections limit defined in its configuration. Several factors can contribute to this state:

1. max_connections Parameter Too Low: The most straightforward cause. The configured max_connections value in postgresql.conf is simply lower than the peak demand for connections from your applications or services.
2. Application Connection Leaks: Applications failing to properly close database connections after use can lead to a gradual accumulation of idle but active connections, eventually exhausting the pool.
3. Inefficient Application Connection Pooling:
   • No Pooling: Applications opening and closing connections for every request are highly inefficient and prone to hitting limits under load.
   • Misconfigured Client-Side Pooling: Application-level connection pools (e.g., in ORMs like ActiveRecord, SQLAlchemy) might be misconfigured (e.g., pool_size too high, timeout too short/long) or not effectively shared across application instances.
   • Lack of Server-Side Pooling (PgBouncer): For high-traffic applications, a dedicated connection pooler like PgBouncer is crucial. Without it, each application process/thread maintains its own direct connection to PostgreSQL, which quickly consumes max_connections.
4. Sudden Traffic Spikes: An unexpected surge in user traffic or background jobs can temporarily overwhelm the existing connection capacity if not adequately scaled.
5. Long-Running or Blocking Queries: Queries that take an unusually long time to execute can hold connections open, preventing other processes from acquiring one. This can be exacerbated by locks, poor query plans, or lack of proper indexing.
6. Excessive Idle Connections: Connections that are open but not actively performing any operations can still count against max_connections. While idle in transaction connections are problematic, even idle connections (no active transaction) consume a slot.

Step-by-Step Resolution

Addressing this issue typically involves a combination of diagnostics, configuration adjustments, and potentially application-level changes.

1. Diagnose Current Connection Usage

First, determine the current connection state and identify potential culprits.

Connect to your PostgreSQL database using psql as a superuser (e.g., postgres user):

sudo -u postgres psql

Then, run the following queries:

-- Count current active connections
SELECT count(*) FROM pg_stat_activity;

-- Show current max_connections setting
SHOW max_connections;

-- List all current connections, their state, and query
SELECT pid, datname, usename, client_addr, application_name, backend_start, state, state_change, query
FROM pg_stat_activity
ORDER BY backend_start DESC;

-- Identify connections that are 'idle in transaction' (often problematic)
SELECT pid, datname, usename, client_addr, application_name, backend_start, state, state_change, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY backend_start DESC;

-- Find long-running queries
SELECT pid, datname, usename, application_name, client_addr, backend_start, xact_start, state,
       current_timestamp - xact_start AS transaction_duration,
       current_timestamp - query_start AS query_duration,
       query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_duration DESC;

Use htop or top on the database server to observe the number of postgres processes. Each active connection generally corresponds to a postgres process.

2. Increase max_connections (Temporary or as a First Step)

If max_connections is clearly too low for your immediate needs and you need a quick fix, you can increase it.

Increasing max_connections directly increases the amount of RAM PostgreSQL will reserve, as each connection consumes memory (typically 2-4MB per connection for work_mem and other buffers). A value too high for your server's RAM can lead to swapping and severe performance degradation. Always monitor your server's memory usage after increasing this value.

1. Edit postgresql.conf: The main configuration file is usually located at /etc/postgresql/14/main/postgresql.conf for PostgreSQL 14 on Ubuntu 22.04.

   sudo vim /etc/postgresql/14/main/postgresql.conf
   

2. Locate and modify max_connections: Change the value to a higher number. A common starting point for a moderately busy server might be 100-200.

   # Connections
   max_connections = 200             # (or a suitable value based on your RAM and PgBouncer strategy)
   

3. Restart PostgreSQL:

   sudo systemctl restart postgresql@14-main
   

4. Verify: Connect with psql again and SHOW max_connections; to confirm the change.

3. Implement or Optimize PgBouncer for Server-Side Connection Pooling

For robust, high-performance web applications, a dedicated connection pooler like PgBouncer is almost always a necessity. It acts as a proxy, maintaining a smaller set of connections to PostgreSQL while handling a much larger number of client connections.

1. Install PgBouncer:

   sudo apt update
   sudo apt install pgbouncer
   

2. Configure PgBouncer: Edit the main configuration file, typically /etc/pgbouncer/pgbouncer.ini.

   sudo vim /etc/pgbouncer/pgbouncer.ini
   

   Here's a sample configuration for a common use case:

   [databases]
   # Format: <name> = host=<> port=<> dbname=<> user=<> password=<>
   app_db = host=127.0.0.1 port=5432 dbname=app_db user=pgbouncer_user password=YOUR_PGBOUNCER_PASSWORD
   
   [pgbouncer]
   listen_addr = 0.0.0.0
   listen_port = 6432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
   
   # Client connections
   max_client_conn = 1000          ; Max client connections to PgBouncer
   default_pool_size = 20          ; How many server connections to allow per user/db pair
   pool_mode = session             ; transaction or session (session is safer, transaction is more efficient but requires careful app design)
   reserve_pool_size = 5           ; Extra connections to reserve for emergencies
   reserve_pool_timeout = 5.0      ; seconds until a connection in reserve_pool is given out
   
   # Server connections (to PostgreSQL)
   max_db_connections = 0          ; Max connections per database. 0 means infinite.
                                   ; Best practice: Let default_pool_size manage it.
   max_user_connections = 0        ; Max connections per user. 0 means infinite.
   
   # Timeouts
   server_lifetime = 3600          ; If a server connection has been idle for this long, it's closed (seconds)
   server_reset_query = DISCARD ALL; SQL to run when connection is returned to pool
   server_check_query = SELECT 1   ; Health check query
   

3. Create PgBouncer User (Authentication): PgBouncer needs a userlist.txt for authentication. You'll also need a dedicated PostgreSQL user that PgBouncer will use to connect to your database.

   First, create a user in PostgreSQL for PgBouncer to connect with:

   sudo -u postgres psql -c "CREATE USER pgbouncer_user WITH PASSWORD 'YOUR_PGBOUNCER_PASSWORD';"
   sudo -u postgres psql -c "GRANT CONNECT ON DATABASE app_db TO pgbouncer_user;"
   

   Next, edit /etc/pgbouncer/userlist.txt:

   sudo vim /etc/pgbouncer/userlist.txt
   

   Add your application users and the pgbouncer_user. For the pgbouncer_user, you'll use its plain text password or its MD5 hash (recommended for security). To get the MD5 hash:

   echo -n "YOUR_PGBOUNCER_PASSWORD" | md5sum
   

   (Note: This is a client-side MD5, not the PostgreSQL scram-sha-256 password hash. For auth_type = md5 in pgbouncer.ini, the userlist.txt requires the MD5 hash of username + password). For example: "pgbouncer_user" "md5c9944f2d5a3c631b34e55e0946258d4d" (replace with your actual hash) "app_user" "plain_app_user_password" (if auth_type allows plain, or use MD5 hash for app_user as well)

   For auth_type = md5 in PgBouncer, the userlist.txt should contain the username and the MD5 hash of the password as it would be stored in pg_authid if the user's password was MD5(password+username). If your application users connect directly to PgBouncer, their credentials need to match what PgBouncer expects. A simpler auth_type for initial setup might be any or trust but these are insecure. hba_file is the most secure, delegating auth to PostgreSQL's pg_hba.conf. For auth_type = md5, the userlist.txt requires the MD5 hash of password concatenated with username. Example calculation: md5(password + username). Or for auth_type = plain, just "username" "password". The pgbouncer_user in the [databases] section is what PgBouncer uses to connect to PostgreSQL. The users listed in userlist.txt are the users that connect to PgBouncer.

4. Start/Restart PgBouncer:

   sudo systemctl restart pgbouncer
   sudo systemctl enable pgbouncer
   

5. Configure Applications to use PgBouncer: Update your application's database connection string to point to PgBouncer's listening address and port (e.g., localhost:6432).

   For example, in a Rails database.yml:

   production:
     adapter: postgresql
     encoding: unicode
     pool: 5 # Application-level pool, usually set lower when using PgBouncer
     host: localhost
     port: 6432 # Point to PgBouncer
     database: app_db
     username: app_user
     password: <%= ENV['DATABASE_PASSWORD'] %>
   

   When using PgBouncer, ensure your application's connection pool size (pool in Rails) is set to a reasonable, smaller number (e.g., 5-10). The primary pooling is now handled by PgBouncer.

4. Optimize Application Code and Query Performance

This is often the most impactful long-term solution.

• Ensure Proper Connection Closure: Verify that your application framework or ORM properly closes connections, especially in asynchronous or multithreaded environments.
• Review Transaction Management:
  • Minimize the duration of transactions.
  • Avoid idle in transaction states by ensuring all BEGIN; statements are paired with COMMIT; or ROLLBACK;.
  • Use SET LOCAL statement_timeout = '30s'; to automatically cancel long-running queries within a session.
• Optimize Queries:
  • Indexing: Ensure proper indexes are in place for frequently queried columns. Use EXPLAIN ANALYZE to check query plans.
  • Avoid N+1 Queries: Especially common in ORMs.
  • Batch Operations: Group multiple small operations into larger batches where possible.
• Refactor Bottlenecks: Identify and refactor parts of your application that are connection-heavy.

5. Monitor and Alert

Proactive monitoring is key to preventing future occurrences.

• PostgreSQL Metrics:
  • pg_stat_activity: Continuously monitor connection states.
  • pg_stat_database: Track active queries, transaction counts.
  • Use tools like Prometheus/Grafana or Datadog to visualize pg_stat_activity metrics over time.
• PgBouncer Metrics:
  • PgBouncer has its own admin console. Connect using psql -p 6432 -U pgbouncer pgbouncer (the pgbouncer database and user are special).
  • SHOW STATS;
  • SHOW POOLS;
• System Metrics: Monitor CPU, RAM, I/O, and network usage on both your application and database servers. High I/O or CPU on the DB server can indicate slow queries, leading to connections backing up.
• Alerting: Set up alerts for high connection counts (e.g., when connections reach 80% of max_connections or max_client_conn), long-running queries, and application error rates.

By systematically diagnosing, configuring, and optimizing, you can effectively manage PostgreSQL connections and ensure the stability and performance of your applications.