When diagnosing connection leaks in PostgreSQL 8.4, start by examining active connections through the system view pg_stat_activity:

SELECT 
    datname, 
    procpid, 
    usename, 
    application_name, 
    client_addr,
    current_query 
FROM pg_stat_activity 
WHERE datname = 'your_database';

Key columns to monitor:

• procpid: Process ID (equivalent to PID in later versions)
• current_query: Currently executing SQL (replaced with query in v9.2+)
• state (in later versions): Shows connection status

For deeper investigation of idle transactions:

SELECT 
    psa.procpid,
    psa.current_query,
    psa.query_start,
    psa.xact_start,
    psa.waiting,
    ptx.virtualtransaction
FROM pg_stat_activity psa
JOIN pg_locks pl ON psa.procpid = pl.pid
JOIN pg_stat_get_backend_idset() ptx ON psa.procpid = ptx.backendid
WHERE psa.datname = 'your_database' 
AND psa.current_query = '<IDLE> in transaction';

Track connection utilization patterns:

SELECT 
    datname,
    count(*) as total_connections,
    sum(case when current_query = '<IDLE>' then 1 else 0 end) as idle_connections,
    sum(case when current_query != '<IDLE>' then 1 else 0 end) as active_connections
FROM pg_stat_activity
GROUP BY datname;

To kill problematic connections (admin privilege required):

-- Single connection termination
SELECT pg_terminate_backend(procpid) 
FROM pg_stat_activity 
WHERE datname = 'your_database' 
AND procpid != pg_backend_pid();

-- Bulk termination of idle transactions
SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE current_query = '<IDLE> in transaction'
AND (now() - xact_start) > interval '5 minutes';

Implement connection pooling with pgBouncer and set these parameters in postgresql.conf:

tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 3

Configure statement_timeout at user/database level:

ALTER DATABASE your_database SET statement_timeout TO '300s';
ALTER ROLE app_user SET statement_timeout TO '180s';

When troubleshooting database performance issues or connection leaks in PostgreSQL 8.4, the first step is to examine currently active connections. The pg_stat_activity view is your primary tool for this:

SELECT * FROM pg_stat_activity;

This returns all active connections with details like:

• datname - Database name
• usename - Username
• client_addr - Client IP address
• query_start - When query began
• current_query - The executing SQL

To focus on connections to a particular database:

SELECT * FROM pg_stat_activity 
WHERE datname = 'your_database_name';

This query helps find queries running longer than 5 minutes:

SELECT now() - query_start as runtime, 
       usename, datname, current_query
FROM pg_stat_activity
WHERE now() - query_start > interval '5 minutes'
ORDER BY runtime DESC;

To kill a specific connection (use with caution):

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid = 12345; -- replace with actual process ID

If you prefer a GUI approach:

1. Connect to your server in pgAdmin
2. Right-click the server → Server Status
3. View the Connections tab

Track total connections over time with:

SELECT count(*) as connection_count, 
       datname 
FROM pg_stat_activity 
GROUP BY datname;