When working with PostgreSQL in data-intensive applications that spawn multiple processes (like your data-mining scenario), connection management becomes critical. The error FATAL: remaining connection slots are reserved for non-replication superuser connections typically occurs when:

• Client applications fail to properly close connections
• Connection pooling isn't implemented
• The max_connections setting is too low for the workload
• Idle connections aren't being terminated

First, check current connection status:

SELECT 
    datname, 
    usename, 
    application_name, 
    client_addr, 
    state, 
    now() - query_start AS duration 
FROM 
    pg_stat_activity 
ORDER BY 
    duration DESC;

To identify abandoned connections:

SELECT 
    pid, 
    now() - state_change AS idle_duration 
FROM 
    pg_stat_activity 
WHERE 
    state = 'idle' 
ORDER BY 
    idle_duration DESC;

1. Enforcing Connection Timeouts

Add these to postgresql.conf:

idle_in_transaction_session_timeout = '10min'  # Kills idle transactions
tcp_keepalives_idle = '60'                 # TCP keepalive settings
tcp_keepalives_interval = '10'
tcp_keepalives_count = '3'

2. Proper Connection Handling in Application Code

Always use context managers (Python example):

import psycopg2
from contextlib import closing

with closing(psycopg2.connect(dbname='mydb')) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM large_table")
        # Processing happens here
# Both cursor and connection automatically close

3. Advanced Connection Pooling with PgBouncer

Configure pgbouncer.ini properly:

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
reserve_pool_size = 5
server_idle_timeout = 60

For the shared memory error when increasing max_connections:

# Add to /etc/sysctl.conf
kernel.shmmax = 68719476736  # 64GB
kernel.shmall = 4294967296   # 4GB pages

# Then run:
sudo sysctl -p

Create a maintenance function to clean up stale connections:

CREATE OR REPLACE FUNCTION kill_idle_connections(min_idle_minutes integer) 
RETURNS void AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle' 
        AND now() - state_change > (min_idle_minutes * interval '1 minute')
    LOOP
        EXECUTE format('SELECT pg_terminate_backend(%s)', rec.pid);
        RAISE NOTICE 'Terminated idle connection with PID: %', rec.pid;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

• Implement retry logic with exponential backoff in your application
• Consider read replicas for reporting queries
• Evaluate moving to PostgreSQL 12+ for improved connection scalability
• Implement proper connection health checks in your pool

---

The "no more connections allowed" error in PostgreSQL typically occurs when your application isn't properly closing database connections, leading to connection pool exhaustion. Even after killing the application, these connections may persist because PostgreSQL maintains them until explicitly closed or timed out.

First, let's examine active connections:

SELECT * FROM pg_stat_activity WHERE state != 'idle';

For more detailed analysis:

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

PostgreSQL does have timeout settings, but they're not enabled by default. Add these to postgresql.conf:

idle_in_transaction_session_timeout = 60000  # 1 minute in milliseconds
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10

Here's how to properly handle connections in Python using context managers:

import psycopg2
from contextlib import closing

with closing(psycopg2.connect("dbname=test user=postgres")) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM my_table")
        for record in cur:
            print(record)

For immediate cleanup, terminate idle connections:

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
AND now() - state_change > interval '5 minutes';

When using PGBouncer, set these parameters in pgbouncer.ini:

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
reserve_pool_size = 5
server_idle_timeout = 30

For shared memory issues, adjust kernel parameters:

# Add to /etc/sysctl.conf
kernel.shmmax = 67108864
kernel.shmall = 4294967296

Set up monitoring to prevent future occurrences:

SELECT 
    max_conn,
    used,
    max_conn - used AS res_conn,
    (used * 100) / max_conn AS percentage_used
FROM 
    (SELECT setting::float AS max_conn FROM pg_settings WHERE name = 'max_connections') AS max_conn,
    (SELECT count(*) AS used FROM pg_stat_activity) AS used;