How to fix FATAL: terminating connection due to idle_session_timeout in PostgreSQL

First, verify what idle_session_timeout is set to on your PostgreSQL server:

SELECT name, setting FROM pg_settings WHERE name = 'idle_session_timeout';

The result shows the timeout value in milliseconds. A value of 0 means the timeout is disabled. If the setting is non-zero, it's actively terminating idle sessions.

If you don't need idle session timeout, disable it by setting it to 0:

ALTER SYSTEM SET idle_session_timeout = 0;

Then reload the configuration:

SELECT pg_reload_conf();

This disables the timeout globally. Verify the change took effect by running the pg_settings query again.

Best practice: don't apply idle_session_timeout globally. Instead, set it only for interactive login accounts:

ALTER ROLE interactive_user SET idle_session_timeout = 600000; -- 10 minutes

Avoid setting this for service or application accounts, as connection poolers and application frameworks may not handle unexpected disconnections gracefully.

If you need idle_session_timeout for interactive users, increase the duration to a reasonable value:

ALTER ROLE interactive_user SET idle_session_timeout = 1800000; -- 30 minutes

Timeout values are in milliseconds. Common durations:
- 600000 = 10 minutes
- 1200000 = 20 minutes
- 1800000 = 30 minutes

Choose a value that matches your expected user behavior.

Prevent frozen connections on the client side by adding a connection timeout to your connection string:

postgresql://user:password@host:5432/database?connect_timeout=5

Or set the PGCONNECT_TIMEOUT environment variable:

export PGCONNECT_TIMEOUT=5

This ensures clients don't hang indefinitely on network failures.

Configure TCP keepalive to detect and clean up dead connections:

ALTER SYSTEM SET tcp_keepalives_idle = 120;      -- 2 minutes
ALTER SYSTEM SET tcp_keepalives_interval = 30;   -- 30 seconds
ALTER SYSTEM SET tcp_keepalives_count = 5;       -- 5 probes

Then reload:

SELECT pg_reload_conf();

TCP keepalive helps detect network issues without relying on idle_session_timeout.

If your application experiences frequent disconnections, use a connection pooler like PgBouncer to manage connections and reduce the impact of timeouts:

sudo apt-get install pgbouncer

Configure PgBouncer in your connection pool to:
- Reuse connections across multiple application processes
- Implement connection pooling strategies (transaction or session mode)
- Handle reconnection logic automatically

This allows the pooler to manage timeouts while keeping application connections stable.