How to close idle connections in PostgreSQL automatically?
For those who are interested, here is the solution I came up with, inspired from Craig Ringer's comment:
(...) use a cron job to look at when the connection was last active (see pg_stat_activity) and use pg_terminate_backend to kill old ones.(...)
The chosen solution comes down like this:
- First, we upgrade to Postgresql 9.2.
- Then, we schedule a thread to run every second.
- When the thread runs, it looks for any old inactive connections.
- A connection is considered inactive if its state is either
idle
,idle in transaction
,idle in transaction (aborted)
ordisabled
. - A connection is considered old if its state stayed the same during more than 5 minutes.
- A connection is considered inactive if its state is either
- There are additional threads that do the same as above. However, those threads connect to the database with different user.
- We leave at least one connection open for any application connected to our database. (
rank()
function)
This is the SQL query run by the thread:
WITH inactive_connections AS (
SELECT
pid,
rank() over (partition by client_addr order by backend_start ASC) as rank
FROM
pg_stat_activity
WHERE
-- Exclude the thread owned connection (ie no auto-kill)
pid <> pg_backend_pid( )
AND
-- Exclude known applications connections
application_name !~ '(?:psql)|(?:pgAdmin.+)'
AND
-- Include connections to the same database the thread is connected to
datname = current_database()
AND
-- Include connections using the same thread username connection
usename = current_user
AND
-- Include inactive connections only
state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND
-- Include old connections (found with the state_change field)
current_timestamp - state_change > interval '5 minutes'
)
SELECT
pg_terminate_backend(pid)
FROM
inactive_connections
WHERE
rank > 1 -- Leave one connection for each application connected to the database
How to close idle connection on PostgreSQL database
You can safely terminate your own idle backend processes using pg_terminate_backend(pid int).
If you try to terminate a process you haven't access to, you'll get an error message and nothing special will happen. Don't try to terminate active processes.
Per the documentation:
pg_terminate_backend(pid int)
Terminate a backend. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend, however only superusers can terminate superuser backends.
Is there a timeout for idle PostgreSQL connections?
It sounds like you have a connection leak in your application because it fails to close pooled connections. You aren't having issues just with <idle> in transaction
sessions, but with too many connections overall.
Killing connections is not the right answer for that, but it's an OK-ish temporary workaround.
Rather than re-starting PostgreSQL to boot all other connections off a PostgreSQL database, see: How do I detach all other users from a postgres database? and How to drop a PostgreSQL database if there are active connections to it? . The latter shows a better query.
For setting timeouts, as @Doon suggested see How to close idle connections in PostgreSQL automatically?, which advises you to use PgBouncer to proxy for PostgreSQL and manage idle connections. This is a very good idea if you have a buggy application that leaks connections anyway; I very strongly recommend configuring PgBouncer.
A TCP keepalive won't do the job here, because the app is still connected and alive, it just shouldn't be.
In PostgreSQL 9.2 and above, you can use the new state_change
timestamp column and the state
field of pg_stat_activity
to implement an idle connection reaper. Have a cron job run something like this:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'regress'
AND pid <> pg_backend_pid()
AND state = 'idle'
AND state_change < current_timestamp - INTERVAL '5' MINUTE;
In older versions you need to implement complicated schemes that keep track of when the connection went idle. Do not bother; just use pgbouncer.
How to close inactive connections in Postgres
Check out this post Is there a timeout for idle PostgreSQL connections?
And consider of using pgbouncer in front of your database.
I would like to set idle_in_transaction_session_timeout for a Heroku Postgres database
You can change it for your own user:
alter user current_user set idle_in_transaction_session_timeout = '5min';
How long for Postgres to drop idle user
Well, for your actual question:
if a user reaches his connection limit, and without me doing anything, what is the default setting for Postgres 9.1 to drop the connection on its own?
If the connections you are talking about are active connections to real clients (i.e. the client is still around, not crashed and exited, and can read and write to its connection socket), Postgres will not "drop the connection on its own". See this question if you're interested in ways to prune such idle clients.
On the other hand, it is possible for a Postgres backend to be nominally connected to a client which has really crashed or otherwise exited, as you alluded to earlier. In that case, Postgres may not notice the client is gone and not just in an idle state for some time. I believe the exact time would be controlled by a "TCP keepalive time" OS setting, or you could even fiddle with this setting in your client via connection parameters: see the various keepalives-related settings.
Related Topics
Sql Query to Join Two Tables With No Repeated Values
Select Count of Total Products as Well as Out of Stock Products from Table
Get the Number of Digits After the Decimal Point of a Float (With or Without Decimal Part)
Custom Aggregate Function (Concat) in SQL Server
How to Remove Line Feed Characters When Selecting Data from SQL Server
Spark - Query Dataframe Based on Values from a Column in Another Dataframe
Insert If Not Exists Else Update
Adding $ Dollar Sign on My Total Cost in SQL Server
Sql to Generate a List of Numbers from 1 to 100
Sql - Return a Default Value When My Search Returns No Results Along With Search Criteria
Sql Query to Return Only First Occurance of One Column Value
How to Include Results of SQL Count If Count=0
Sql 0 Results for 'Not In' and 'In' When Row Does Exist
How to Calculate Percentage Between Two Numbers Using SQL on Bigquery
Sql: Select All Rows If Parameter Is Null, Else Only Select Matching Rows