PostgreSQL service on Rocky Linux: configuring StopWhenUnneeded to control idle connections

Direct answers

Does enabling StopWhenUnneeded cause PostgreSQL to close all active connections immediately upon service stop? No. Systemd still sends SIGTERM to the postmaster, which triggers a smart shutdown: PostgreSQL stops accepting new connections and waits for existing clients to disconnect. It does not kill them outright unless the shutdown mode is forced to fast/immediate or systemd’s TimeoutStopSec elapses and a SIGKILL is sent.

Will the combination of StopWhenUnneeded=true and idle_in_transaction_session_timeout>0 provide a safe idle‑connection cleanup mechanism? It helps, but idle_in_transaction_session_timeout only ends sessions that are idle while holding an open transaction. To close completely idle sessions (no transaction) you need idle_in_session_timeout (available in PostgreSQL 14+). When either timeout is active, idle connections are terminated before a systemd stop, allowing StopWhenUnneeded to see the service as unneeded and shut down cleanly.

Are there any side‑effects on service reliability or failover behavior when StopWhenUnneeded is set? Yes. StopWhenUnneeded lets systemd stop the unit as soon as no other active unit depends on it. If a backup script, monitoring tool, or replication client connects without being expressed as a systemd dependency (Wants/Requires), systemd may deem the service unneeded and stop it while those external processes are still running, causing interrupted jobs. Also, setting the session timeout too low can disconnect legitimate idle idle sessions, leading to application errors.

Confirmed facts

• StopWhenUnneeded only influences when systemd may stop the unit; it does not alter the shutdown signal.
• PostgreSQL’s response to SIGTERM is a smart shutdown that waits for client disconnection.
• Idle‑session termination is controlled by idle_in_session_timeout (PG 14+) or idle_in_transaction_session_timeout for idle‑in‑transaction sessions.

Likely explanation

If the PostgreSQL unit has StopWhenUnneeded=true and no other unit depends on it, systemd will consider it unneeded as soon as the last dependent unit stops. However, as long as any client connection remains, the postmaster stays active, so systemd sees the service as still needed and does not stop it. By enabling a session timeout, idle connections are closed automatically, removing the last reason for the service to stay active and allowing StopWhenUnneeded to trigger a clean shutdown.

Implementation steps for Rocky Linux

1. Set PostgreSQL session timeouts in /var/lib/pgsql/data/postgresql.conf (adjust path if your cluster uses a different data directory):

   # Example values – adjust to your workload
   idle_in_transaction_session_timeout = 300   # 5 minutes
   # For PostgreSQL 14+ you can also set:
   idle_in_session_timeout = 600              # 10 minutes
   

   Then reload: systemctl reload postgresql.
2. Enable StopWhenUnneeded via a systemd drop‑in:

   sudo systemctl edit postgresql
   

   In the editor add:

   [Unit]
   StopWhenUnneeded=true
   

   Save and exit.
3. Apply changes:

   sudo systemctl daemon-reload
       sudo systemctl restart postgresql
   

Verification

• Check the drop‑in: systemctl show postgresql -p StopWhenUnneeded should return StopWhenUnneeded=true.
• Confirm timeout: connect with psql and run SHOW idle_in_session_timeout; (or SHOW idle_in_transaction_session_timeout;).
• To see the timeout in action, open a psql session, remain idle longer than the configured value, then watch the server log for “idle session timeout exceeded” and verify the connection disappears from pg_stat_activity.
• Finally, stop the service: systemctl stop postgresql and run SELECT * FROM pg_stat_activity; before the stop completes; you should see zero client connections.

Missing diagnostic detail

Please confirm the exact PostgreSQL version you are running (e.g., 13, 14, 15). This determines whether idle_in_session_timeout is available; if you are on a version prior to 14, rely solely on idle_in_transaction_session_timeout and consider setting a low value for idle‑in‑transaction sessions only.