Is there a timeout for idle PostgreSQL connections?

by

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.

More Related Contents:

  1. What is the format for the PostgreSQL connection string / URL?
  2. How do I change a Crystal Report’s ODBC database connection at runtime?
  3. What to do with null values when modeling and normalizing?
  4. Postgres: INSERT if does not exist already
  5. postgres default timezone
  6. How to change PostgreSQL user password?
  7. How to get the count of current month Sunday’s in psql?
  8. quick random row selection in Postgres
  9. Restore a postgres backup file using the command line?
  10. Copy a table from one database to another in Postgres
  11. User defined variables in PostgreSQL
  12. PostgreSQL query very slow with limit 1
  13. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  14. psql: FATAL: database “” does not exist
  15. Difference between timestamps with/without time zone in PostgreSQL
  16. Postgres FOR LOOP
  17. Storing Images in PostgreSQL
  18. Psql could not connect to server: No such file or directory, 5432 error?
  19. Strange behaviour in Postgresql
  20. Postgres: Distinct but only for one column
  21. In PostgreSQL, how to insert data with COPY command?
  22. Cursor based records in PostgreSQL
  23. Maximum characters in labels (table names, columns etc)
  24. Configuration parameter work_mem in PostgreSQL on Linux
  25. How to work with PGpoint for Geolocation using PostgreSQL?
  26. How to delete an enum type value in postgres?
  27. Why unsigned integer is not available in PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment