PostgreSQL DBA Part 10: Connections, Sessions and pg_stat_activity

This is Part 10 of the PostgreSQL DBA series. In Part 9 we covered tablespaces, which decide where PostgreSQL stores data on disk. This part is about the people and programs using that data: the connections and sessions open on your server right now.

The short answer to the most searched question in this area: to kill a PostgreSQL session, find its process ID in pg_stat_activity and run SELECT pg_terminate_backend(pid);. If you only want to stop the running query and keep the session, use pg_cancel_backend(pid) instead. By the end of this part you will also be able to check max_connections and how many slots are free, read every important column of pg_stat_activity, find blocked and long idle sessions, limit connections per role and per database, keep emergency slots for DBAs, and set timeouts so forgotten sessions clean themselves up.

What you will learn

• How PostgreSQL turns each connection into its own server process, and what max_connections really limits
• How to list sessions with pg_stat_activity and read the state and wait columns
• The difference between pg_cancel_backend and pg_terminate_backend, and why you never use kill -9
• How to disconnect everyone from one database before maintenance
• How to set connection limits per role and per database, and reserve slots with reserved_connections
• How to use idle_in_transaction_session_timeout, statement_timeout and the PostgreSQL 18 log_connections options

Prerequisites

• Parts 1 to 9 of this series. You need Part 5 (reload vs restart), Part 6 (pg_hba.conf) and Part 7 (roles and GRANT) most.
• One Ubuntu server with PostgreSQL 18 from the PGDG repository and the cluster 18 main running on port 5432, as installed in Part 2.
• A user with sudo rights, so you can run commands as the postgres operating system user.
• Four or five terminal windows (or tmux panes) on the server. Each one will act as a different application user.

How it works

PostgreSQL uses one operating system process per connection. When a client connects, the main server process (the postmaster, which you met in Part 1) starts a new child process just for that client. The documentation calls this child a backend. A session is the life of that connection, from login until it disconnects.

A simple comparison: the postmaster is the receptionist at a bank, and each backend is a teller who serves exactly one customer for as long as that customer stays at the counter. More customers means more tellers, and every teller needs a desk and some memory.

That is why there is a hard limit. max_connections is the number of connection slots (desks). The default is 100. It can only be changed with a server restart, because PostgreSQL sizes some shared memory from it when it starts. Part of those slots are kept back:

With the defaults, normal users can open 97 connections. When those are used up, the next normal login fails, but a superuser can still log in and fix the problem. Background processes such as the checkpointer and the PostgreSQL 18 I/O workers are not client connections; they show up in pg_stat_activity with a different backend_type.

Every session is always in one state. These are the values the state column of pg_stat_activity can show:

Finally, there are two ways to stop someone. pg_cancel_backend(pid) sends the SIGINT signal: the current query stops with an error, but the session stays connected. pg_terminate_backend(pid) sends SIGTERM: the whole session ends, and any open transaction is rolled back. Think of cancel as tapping the teller on the shoulder to stop the current task, and terminate as closing that teller's counter.

Checking connection limits and usage

Step 1: Check the connection settings

Where: on the database server, as the postgres operating system user (through sudo). This query reads the three settings from the pg_settings view you used in Part 5.

sudo -u postgres psql -c "SELECT name, setting, context FROM pg_settings WHERE name IN ('max_connections','superuser_reserved_connections','reserved_connections') ORDER BY name;"

              name              | setting |  context
--------------------------------+---------+------------
 max_connections                | 100     | postmaster
 reserved_connections           | 0       | postmaster
 superuser_reserved_connections | 3       | postmaster
(3 rows)

These are the defaults. The context value postmaster means the setting can only change at server start, so a reload is not enough. You can also see the lines in /etc/postgresql/18/main/postgresql.conf:

max_connections = 100			# (change requires restart)
#reserved_connections = 0		# (change requires restart)
#superuser_reserved_connections = 3	# (change requires restart)

Check: SHOW max_connections; in psql returns the same 100.

Step 2: Count how many connections are in use

Where: on the database server, in psql as the postgres user (sudo -u postgres psql). The first query compares used slots with what is left for normal users.

SELECT count(*) AS used,
       current_setting('max_connections')::int AS max_conn,
       current_setting('superuser_reserved_connections')::int
     + current_setting('reserved_connections')::int AS reserved,
       current_setting('max_connections')::int
     - current_setting('superuser_reserved_connections')::int
     - current_setting('reserved_connections')::int
     - count(*) AS free_for_normal_users
  FROM pg_stat_activity
 WHERE backend_type = 'client backend';

 used | max_conn | reserved | free_for_normal_users
------+----------+----------+-----------------------
    1 |      100 |        3 |                    96
(1 row)

Only your own psql session is connected, so 96 slots are free for normal users. The filter backend_type = 'client backend' keeps out background processes. This is a good query to put in a monitoring script: alert when free_for_normal_users gets close to zero.

To see where connections come from, group them by database and user:

SELECT datname, usename, count(*)
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
 GROUP BY 1, 2
 ORDER BY 3 DESC;

 datname  | usename  | count
----------+----------+-------
 postgres | postgres |     1
(1 row)

Seeing who is connected with pg_stat_activity

Step 3: Create a test database and open four sessions

To have something to look at, create an application role, a database and a small orders table. Where: on the server, as the postgres user.

sudo -u postgres psql -c "CREATE ROLE app_user LOGIN PASSWORD 'App#2026pass';"
sudo -u postgres psql -c "CREATE DATABASE shopdb OWNER app_user;"

CREATE ROLE
CREATE DATABASE

Now connect as app_user over TCP to localhost (the default Ubuntu pg_hba.conf from Part 6 allows scram-sha-256 there) and create the table:

psql -h localhost -U app_user -d shopdb

CREATE TABLE orders (
  order_id    integer PRIMARY KEY,
  customer    text NOT NULL,
  amount      numeric(10,2) NOT NULL,
  status      text NOT NULL DEFAULT 'NEW'
);
INSERT INTO orders VALUES
  (1, 'Asha',  1200.00, 'NEW'),
  (2, 'Ravi',   850.50, 'PAID'),
  (3, 'Meena', 4300.00, 'NEW');

CREATE TABLE
INSERT 0 3

Next, open four terminals. In each one, start psql with a different application name so you can tell the sessions apart. The PGAPPNAME environment variable sets application_name for that connection.

# Terminal 1: a long running query
PGAPPNAME=billing psql -h localhost -U app_user -d shopdb
shopdb=> SELECT pg_sleep(600);

# Terminal 2: a transaction that is left open
PGAPPNAME=report psql -h localhost -U app_user -d shopdb
shopdb=> BEGIN;
shopdb=*> UPDATE orders SET status = 'PAID' WHERE order_id = 1;

# Terminal 3: an update of the same row (it will wait)
PGAPPNAME=checkout psql -h localhost -U app_user -d shopdb
shopdb=> UPDATE orders SET amount = 1250.00 WHERE order_id = 1;

# Terminal 4: a session that just sits connected
PGAPPNAME=webapp psql -h localhost -U app_user -d shopdb

Terminal 2 holds a row lock on order 1 because its transaction is still open. Terminal 3 wants the same row, so it hangs. This is exactly the kind of situation a DBA is called for.

Step 4: List the client sessions

Where: a fifth terminal, in psql as the postgres user. A superuser sees every column for every session.

SELECT pid, usename, datname, application_name, client_addr,
       state, wait_event_type, wait_event,
       date_trunc('second', now() - state_change) AS in_state_for,
       left(query, 40) AS query
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
   AND pid <> pg_backend_pid()
 ORDER BY backend_start;

 pid  | usename  | datname | application_name | client_addr |        state        | wait_event_type |  wait_event   | in_state_for |                  query
------+----------+---------+------------------+-------------+---------------------+-----------------+---------------+--------------+------------------------------------------
 3207 | app_user | shopdb  | billing          | 127.0.0.1   | active              | Timeout         | PgSleep       | 00:00:42     | SELECT pg_sleep(600);
 3212 | app_user | shopdb  | report           | 127.0.0.1   | idle in transaction | Client          | ClientRead    | 00:00:41     | UPDATE orders SET status = 'PAID' WHERE
 3216 | app_user | shopdb  | checkout         | 127.0.0.1   | active              | Lock            | transactionid | 00:00:40     | UPDATE orders SET amount = 1250.00 WHERE
 3221 | app_user | shopdb  | webapp           | 127.0.0.1   | idle                | Client          | ClientRead    | 00:00:40     |
(4 rows)

Read it row by row:

• 3207 billing is active. The wait event Timeout / PgSleep shows it is sleeping, as asked.
• 3212 report is idle in transaction. It ran its UPDATE 41 seconds ago and is now waiting for the client (ClientRead) while holding the row lock. For an idle session, query shows the last statement, not a running one.
• 3216 checkout is active but waiting on Lock / transactionid: it is blocked by another transaction.
• 3221 webapp is plain idle. An idle session costs a slot and some memory, but holds no locks.

The pid is the operating system process ID of the backend. You can see the same processes with ps; PostgreSQL writes the user, database, client and current activity into the process title:

ps -o pid,cmd -u postgres | grep -E "app_user|PID"

  PID CMD
 3207 postgres: 18/main: app_user shopdb 127.0.0.1(40632) SELECT
 3212 postgres: 18/main: app_user shopdb 127.0.0.1(40636) idle in transaction
 3216 postgres: 18/main: app_user shopdb 127.0.0.1(40640) UPDATE waiting
 3221 postgres: 18/main: app_user shopdb 127.0.0.1(40646) idle

The state count gives a quick overview. Your own monitoring session is included, which is why there are three active sessions:

SELECT state, count(*)
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
 GROUP BY state
 ORDER BY count(*) DESC;

        state        | count
---------------------+-------
 active              |     3
 idle                |     1
 idle in transaction |     1
(3 rows)

Step 5: Find long transactions and blocked sessions

Where: the same postgres psql session. Long open transactions are the sessions a DBA worries about most. xact_start is when the current transaction began:

SELECT pid, application_name, state,
       date_trunc('second', now() - xact_start) AS xact_age,
       left(query, 40) AS query
  FROM pg_stat_activity
 WHERE xact_start IS NOT NULL
   AND backend_type = 'client backend'
   AND pid <> pg_backend_pid()
 ORDER BY xact_start;

 pid  | application_name |        state        | xact_age |                  query
------+------------------+---------------------+----------+------------------------------------------
 3207 | billing          | active              | 00:00:42 | SELECT pg_sleep(600);
 3212 | report           | idle in transaction | 00:00:41 | UPDATE orders SET status = 'PAID' WHERE
 3216 | checkout         | active              | 00:00:40 | UPDATE orders SET amount = 1250.00 WHERE
(3 rows)

Now ask who is blocking whom. The function pg_blocking_pids(pid) returns the process IDs that hold the lock a session is waiting for:

SELECT pid, application_name,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 40) AS query
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;

 pid  | application_name | blocked_by |                  query
------+------------------+------------+------------------------------------------
 3216 | checkout         | {3212}     | UPDATE orders SET amount = 1250.00 WHERE
(1 row)

checkout (3216) is waiting for report (3212), the idle-in-transaction session. Locks get a full part later in the series (Part 29); for the case where two sessions wait for each other, see PostgreSQL deadlock detected: causes and fixes.

Step 6: Let a support role see and stop sessions without superuser

A normal role sees only its own sessions in full. For other users' sessions the query is hidden. Where: as a login role support_dba that is not a superuser (created with CREATE ROLE support_dba LOGIN PASSWORD ... as in Part 7):

SELECT pid, usename, state, query
  FROM pg_stat_activity
 WHERE usename = 'app_user';
SELECT pg_terminate_backend(3293);

 pid  | usename  | state |          query
------+----------+-------+--------------------------
 3293 | app_user |       | <insufficient privilege>
(1 row)

ERROR:  permission denied to terminate process
DETAIL:  Only roles with privileges of the role whose process is being terminated or with privileges of the "pg_signal_backend" role may terminate this process.

Two predefined roles solve this. pg_read_all_stats lets a role read all pg_stat_* views in full, and pg_signal_backend lets it cancel or terminate other non-superuser sessions. Where: as the postgres superuser:

GRANT pg_read_all_stats, pg_signal_backend TO support_dba;

GRANT ROLE

Check: run the same two statements again as support_dba. The state is now visible (3293 was an idle session with no query yet, so query is empty) and the terminate works:

 pid  | usename  | state | query
------+----------+-------+-------
 3293 | app_user | idle  |
(1 row)

 pg_terminate_backend
----------------------
 t
(1 row)

Superuser sessions stay protected. When support_dba tries to terminate a postgres session it gets:

ERROR:  permission denied to terminate process
DETAIL:  Only roles with the SUPERUSER attribute may terminate processes of roles with the SUPERUSER attribute.

Undo: REVOKE pg_read_all_stats, pg_signal_backend FROM support_dba;. PostgreSQL 18 also adds pg_signal_autovacuum_worker, which allows signalling autovacuum workers only.

Cancelling and terminating sessions

Step 7: Cancel a running query with pg_cancel_backend

Start with the gentle option. Where: postgres psql session. The billing session (3207) is running a long query:

SELECT pg_cancel_backend(3207);

 pg_cancel_backend
-------------------
 t
(1 row)

t means the signal was sent. In terminal 1 the user sees their query fail:

ERROR:  canceling statement due to user request

Check: the session is still there, now idle:

SELECT pid, application_name, state, left(query, 30) AS query
  FROM pg_stat_activity WHERE pid = 3207;

 pid  | application_name | state |         query
------+------------------+-------+-----------------------
 3207 | billing          | idle  | SELECT pg_sleep(600);
(1 row)

If the query was inside a transaction, that transaction is now in the aborted state and the application must roll it back. Cancel is the right first move for a runaway report or a forgotten query: nobody loses their connection.

Step 8: Terminate a session with pg_terminate_backend

Cancelling does not help the report session (3212): it is not running anything, it is just holding a lock. Terminating ends the session and rolls its transaction back. The function takes two arguments:

SELECT pg_terminate_backend(3212, 5000);

 pg_terminate_backend
----------------------
 t
(1 row)

Because a timeout was given, t here means the process really ended. The server log (/var/log/postgresql/postgresql-18-main.log) records it:

2026-11-02 13:02:46.252 UTC [3212] app_user@shopdb FATAL:  terminating connection due to administrator command

The client of a terminated session sees the same FATAL message the next time it sends a command, and psql reports that the connection was lost. Here is a script-driven psql session that ran SELECT 1; after being terminated:

FATAL:  terminating connection due to administrator command
SSL connection has been closed unexpectedly
connection to server was lost

Check: the lock is gone, so the checkout UPDATE in terminal 3 finished on its own and printed UPDATE 1. Only two sessions remain, and the report session's status change was rolled back:

 pid  | application_name | state
------+------------------+-------
 3207 | billing          | idle
 3221 | webapp           | idle
(2 rows)

 order_id | status | amount
----------+--------+---------
        1 | NEW    | 1250.00
(1 row)

Order 1 still has status NEW (the terminated transaction's UPDATE was undone) and the new amount 1250.00 from checkout. If you pass a PID that is not a PostgreSQL backend, you get WARNING: PID 99999 is not a PostgreSQL backend process and the result f.

Step 9: Disconnect everyone from one database

Before some maintenance (renaming or dropping a database, or a restore into it) you need it empty. Terminating sessions alone is not enough, because applications reconnect at once. So first block new connections, then terminate. Where: postgres psql session, connected to the postgres database, not to shopdb.

ALTER DATABASE shopdb ALLOW_CONNECTIONS false;

SELECT pid, application_name,
       pg_terminate_backend(pid, 5000) AS terminated
  FROM pg_stat_activity
 WHERE datname = 'shopdb'
   AND backend_type = 'client backend'
   AND pid <> pg_backend_pid();

ALTER DATABASE
 pid  | application_name | terminated
------+------------------+------------
 3207 | billing          | t
 3221 | webapp           | t
(2 rows)

Check: no sessions are left, the flag is off, and a new login is refused, even for a superuser:

SELECT count(*) AS sessions_left
  FROM pg_stat_activity
 WHERE datname = 'shopdb';

SELECT datname, datallowconn, datconnlimit
  FROM pg_database WHERE datname = 'shopdb';

 sessions_left
---------------
             0
(1 row)

 datname | datallowconn | datconnlimit
---------+--------------+--------------
 shopdb  | f            |           -1
(1 row)

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  database "shopdb" is not currently accepting connections

Undo: when the maintenance is done, open the database again and test a login:

ALTER DATABASE shopdb ALLOW_CONNECTIONS true;

ALTER DATABASE

If the goal is to drop the database, DROP DATABASE shopdb WITH (FORCE); (PostgreSQL 13 and later) terminates the connections for you. The documentation notes it does not do so when prepared transactions, active logical replication slots or subscriptions exist in that database.

Limiting connections

Step 10: Set limits per role and per database

One badly configured application can use up every slot and lock out the others. A CONNECTION LIMIT caps one role or one database. Where: postgres psql session.

ALTER ROLE app_user CONNECTION LIMIT 2;
ALTER DATABASE shopdb CONNECTION LIMIT 50;

SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = 'app_user';
SELECT datname, datconnlimit FROM pg_database WHERE datname = 'shopdb';

ALTER ROLE
ALTER DATABASE
 rolname  | rolconnlimit
----------+--------------
 app_user |            2
(1 row)

 datname | datconnlimit
---------+--------------
 shopdb  |           50
(1 row)

Check: with two app_user sessions already open, a third login fails:

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  too many connections for role "app_user"

A database limit gives the matching error FATAL: too many connections for database "shopdb". Keep in mind what the documentation says about these limits: they are enforced only approximately when two logins arrive at the same moment, the database limit is not enforced against superusers, and only normal connections count towards the role limit.

Undo: -1 means no limit, which is the default.

ALTER ROLE app_user CONNECTION LIMIT -1;
ALTER DATABASE shopdb CONNECTION LIMIT -1;

Step 11: Change max_connections and reserve slots for DBAs

To see the reserved slots in action, this step uses a deliberately tiny limit on a test server. Where: on the server, as a sudo user, editing /etc/postgresql/18/main/postgresql.conf.

# Before
max_connections = 100			# (change requires restart)
#reserved_connections = 0		# (change requires restart)
#superuser_reserved_connections = 3	# (change requires restart)

# After (test only)
max_connections = 10			# (change requires restart)
reserved_connections = 2		# (change requires restart)
#superuser_reserved_connections = 3	# (change requires restart)

A reload is not enough. If you try, the setting shows pending_restart = t and the log says parameter "max_connections" cannot be changed without restarting the server. Restart the cluster and check:

sudo systemctl restart postgresql@18-main
pg_lsclusters
sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('max_connections','reserved_connections','superuser_reserved_connections') ORDER BY name;"

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
              name              | setting
--------------------------------+---------
 max_connections                | 10
 reserved_connections           | 2
 superuser_reserved_connections | 3
(3 rows)

Now give the support role access to the reserved slots with the predefined role pg_use_reserved_connections:

GRANT pg_use_reserved_connections TO support_dba;

With 10 slots, 3 for superusers and 2 reserved, normal roles get 5. This is what happened as sessions were opened one by one:

FATAL:  remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
FATAL:  sorry, too many clients already

In production you would keep max_connections at a value your memory can support, and set reserved_connections to a small number so your monitoring and support roles can always log in, without handing them superuser.

Undo: put the file back to the "Before" lines and run sudo systemctl restart postgresql@18-main again, then confirm SHOW max_connections; returns 100.

Timeouts and connection logging

Step 12: Set session timeouts for an application role

Terminating sessions by hand does not scale. These settings let PostgreSQL do it for you. All of them default to 0 (disabled), take milliseconds when no unit is given, and can be set per role or per database:

The documentation advises against setting statement_timeout and transaction_timeout in postgresql.conf, because they would then hit every session, including your maintenance jobs. Set them on the application role instead. Where: postgres psql session. First a 5-second test value so you can watch it work:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5s';

ALTER ROLE

Role settings apply to new sessions. Open a new app_user session, run BEGIN; and an UPDATE, wait more than 5 seconds, then run another command:

BEGIN
UPDATE 1
FATAL:  terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly
connection to server was lost

Now set realistic values. Agree the numbers with the application team; the ones below are examples, not recommendations from the documentation:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10min';
ALTER ROLE app_user SET statement_timeout = '30s';

Check: per-role settings are stored in pg_db_role_setting, and a new session shows them with SHOW. A query that runs too long now fails with:

ERROR:  canceling statement due to statement timeout

Undo: ALTER ROLE app_user RESET ALL; removes every per-role setting, or ALTER ROLE app_user RESET statement_timeout; removes one.

Step 13: Log connections and disconnections

When users say "I keep getting disconnected", the server log is your evidence. PostgreSQL 18 turned log_connections from on/off into a list of options. Where: on the server, as a sudo user, in /etc/postgresql/18/main/postgresql.conf:

# Before (default)
#log_connections = '' # log aspects of connection setup
#log_disconnections = off

# After
log_connections = 'authorization,setup_durations' # log aspects of connection setup
log_disconnections = on

The old values on and off still work; on means receipt, authentication and authorization. Both settings take effect for new sessions after a reload, no restart needed:

sudo systemctl reload postgresql@18-main
sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('log_connections','log_disconnections');"

        name        |            setting
--------------------+-------------------------------
 log_connections    | authorization,setup_durations
 log_disconnections | on
(2 rows)

Check: connect once as app_user and look at the end of the log:

sudo tail -3 /var/log/postgresql/postgresql-18-main.log

2026-11-02 13:00:41.149 UTC [3002] app_user@shopdb LOG:  connection authorized: user=app_user database=shopdb application_name=billing SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
2026-11-02 13:00:41.150 UTC [3002] app_user@shopdb LOG:  connection ready: setup total=14.460 ms, fork=0.713 ms, authentication=6.646 ms
2026-11-02 13:00:41.152 UTC [3002] app_user@shopdb LOG:  disconnection: session time: 0:00:00.015 user=app_user database=shopdb host=127.0.0.1 port=52904

You can see who connected, from where, how long the login took, and how long the session lasted. On a busy server these lines add up quickly, so watch the log size. Undo: comment the two lines out again and reload.

Common mistakes and how to fix them

FATAL: sorry, too many clients already

Every slot is in use. Log in as a superuser (the 3 reserved slots exist for this), run the Step 4 query and look for the source: usually one application with hundreds of idle sessions. Terminate the idle ones, fix the application's pool size, and only then think about raising max_connections (restart needed).

FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute

Normal slots are full and only superuser slots are left. Same fix as above. To keep a non-superuser support account working in this situation, use reserved_connections with pg_use_reserved_connections (Step 11).

pg_terminate_backend returns true but the session is still there

Without a timeout, t only means the signal was sent. Use pg_terminate_backend(pid, 5000) to wait for the exit, then check pg_stat_activity again. Applications with a connection pool also reconnect at once, which looks like the session "came back" with a new PID; block logins first as in Step 9.

ERROR: permission denied to terminate process

The caller is not a superuser, not a member of the session's role, and does not have pg_signal_backend. Grant pg_signal_backend to the support role (Step 6). Superuser sessions can only be terminated by a superuser.

You terminated your own session

A query like SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'shopdb'; run while connected to shopdb also kills your own connection. Always add AND pid <> pg_backend_pid(), and run mass terminations from the postgres database.

Cancelling an idle-in-transaction session does nothing

pg_cancel_backend stops a running query. An idle-in-transaction session is not running anything, so there is nothing to cancel and its locks stay. Use pg_terminate_backend and set idle_in_transaction_session_timeout so it does not happen again.

Practice exercise

1. Write one query that terminates every session that has been idle in transaction (including the aborted variant) for more than 5 minutes, and shows the PID, user, application and idle time of each.
2. Make sure the role app_user can never hold more than 20 connections, and confirm the value from the catalog.

Quick reference

# Show the connection slot settings
sudo -u postgres psql -c "SELECT name, setting, context FROM pg_settings WHERE name IN ('max_connections','superuser_reserved_connections','reserved_connections');"

# Count client connections against max_connections
sudo -u postgres psql -c "SELECT count(*) AS used, current_setting('max_connections') AS max_conn FROM pg_stat_activity WHERE backend_type = 'client backend';"

# List sessions with state, wait event and query
sudo -u postgres psql -c "SELECT pid, usename, datname, application_name, client_addr, state, wait_event_type, wait_event, now() - state_change AS in_state_for, left(query, 40) AS query FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid() ORDER BY backend_start;"

# Show backends as Linux processes
ps -o pid,cmd -u postgres

# Find blocked sessions and their blockers
sudo -u postgres psql -c "SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 40) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;"

# Let a support role see all sessions and signal them
sudo -u postgres psql -c "GRANT pg_read_all_stats, pg_signal_backend TO support_dba;"

# Cancel the current query of one session (session stays)
sudo -u postgres psql -c "SELECT pg_cancel_backend(3207);"

# Terminate one session and wait up to 5 seconds
sudo -u postgres psql -c "SELECT pg_terminate_backend(3212, 5000);"

# Close a database and disconnect everyone from it
sudo -u postgres psql -c "ALTER DATABASE shopdb ALLOW_CONNECTIONS false;"
sudo -u postgres psql -c "SELECT pg_terminate_backend(pid, 5000) FROM pg_stat_activity WHERE datname = 'shopdb' AND backend_type = 'client backend' AND pid <> pg_backend_pid();"
sudo -u postgres psql -c "ALTER DATABASE shopdb ALLOW_CONNECTIONS true;"

# Per-role and per-database limits (-1 = no limit)
sudo -u postgres psql -c "ALTER ROLE app_user CONNECTION LIMIT 20;"
sudo -u postgres psql -c "ALTER DATABASE shopdb CONNECTION LIMIT 50;"

# After editing max_connections / reserved_connections in postgresql.conf
sudo systemctl restart postgresql@18-main
sudo -u postgres psql -c "GRANT pg_use_reserved_connections TO support_dba;"

# Timeouts for the application role
sudo -u postgres psql -c "ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10min';"
sudo -u postgres psql -c "ALTER ROLE app_user SET statement_timeout = '30s';"

# After editing log_connections / log_disconnections
sudo systemctl reload postgresql@18-main
sudo tail -3 /var/log/postgresql/postgresql-18-main.log

Summary and what's next

• Each connection is its own backend process; max_connections (default 100) caps them and needs a restart to change
• superuser_reserved_connections (3) and reserved_connections (0) keep slots back for emergencies
• pg_stat_activity shows every session: filter on backend_type = 'client backend' and read state, wait_event and xact_start
• pg_blocking_pids(pid) tells you who is blocking whom
• pg_cancel_backend stops a query; pg_terminate_backend ends the session and rolls back its transaction
• Never kill -9 a backend: it restarts every session and triggers crash recovery
• Use ALLOW_CONNECTIONS false before terminating everyone in a database
• Use CONNECTION LIMIT per role or database, and role-level timeouts such as idle_in_transaction_session_timeout
• Grant pg_read_all_stats, pg_signal_backend and pg_use_reserved_connections instead of superuser to support staff

In Part 11 we move into storage and maintenance with MVCC for DBAs: tuples, xmin/xmax and row visibility, which explains why those idle-in-transaction sessions are so harmful.