PostgreSQLに接続中のセッション情報を経過時間付きで確認するには？不要な接続を検出・切断する技も解説

PostgreSQLに接続中のセッション情報を経過時間付きで確認するには？ Database

PostgreSQLデータベースに接続中のセッション一覧の表示、現在SQLを実行中のセッション情報確認、接続の状態毎のセッションの数、切断出来る可能性のあるセッション一覧を表示、切断しても問題ないと思われる接続を全て切断するクエリーなど、PostgreSQLの接続に関するクエリーを集めました。

PostgreSQLデータベースに現在接続しているセッションで実行中のSQLを確認し、不要な接続を切断したりすることはデータベースを管理する上で非常に重要です。PostgreSQLの各バージョン毎にSQLが異なる点についても、各バージョン毎のSQLを記載しています。

接続中のセッション情報一覧表示

PostgreSQLで現在接続中のセッション一覧を表示するSQLです。PostgreSQL-10以上とPostgreSQL-10未満のバージョンでは列が異なるため、バージョンに応じて2つのSQLを使い分ける必要があります。

--
-- PostgreSQL-10以上
--
SELECT
    pid,
    usename,
    client_addr,
    client_hostname,
    client_port,
    backend_start,
    query_start,
    state,
    backend_type
FROM
    pg_stat_activity
ORDER BY
    backend_type;

--
-- PostgreSQL-10未満
--
SELECT
    pid,
    usename,
    client_addr,
    client_hostname,
    client_port,
    backend_start,
    query_start,
    state
FROM
    pg_stat_activity;

SQL実行中のセッション情報表示

現在SQL実行中のセッション情報を表示するには次のSQLを実行します。このSQLでは、”age”という列を表示しており、クエリー実行開始日時から現在までの経過時間も表示するようにしています。PostgreSQL-9.2バージョン以上の場合と、PostgreSQL-9.2未満のバージョンではSQLが異なります。

--
-- PostgreSQL-9.2以上
--
SELECT
    pid,
    age(clock_timestamp(), query_start),
    usename,
    application_name,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY
    query_start DESC;

+---------+-----------------+----------+------------------+-----------------------------------------+
|   pid   |       age       | usename  | application_name |                query                    |
+---------+-----------------+----------+------------------+-----------------------------------------+
| 3086246 | 00:00:08.000499 | testuser | psql             | FETCH 100 FROM c1234                    |
| 3086307 | 00:00:01.892104 | testuser | psql             | select * from test_table where id = 100 |
+---------+-----------------+---------+-------------------+-----------------------------------------+
(2 rows)


--
-- PostgreSQL-9.2未満
--
SELECT
    procpid,
    age(clock_timestamp(), query_start),
    usename,
    current_query
FROM
    pg_stat_activity
WHERE
    current_query != '<IDLE>'
      AND
    current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY
    query_start DESC;

+---------+-----------------------+----------+-------------------------+
| procpid |           age         | usename  |      current_query      |
+---------+-----------------------+----------+-------------------------+
|   30458 | 1 day 03:23:00.000734 | testuser | SELECT * FROM customers |
+---------+-----------------------+----------+-------------------------+
(1 row)

接続の状態毎のセッションの数を調べる

現在SQL実行中のセッション、アイドル状態のセッション(SQL実行中ではない)、トランザクション実行中のセッション等の状態毎の接続数を調べるSQLです。

SELECT
    state,
    count(*)
FROM
    pg_stat_activity
GROUP BY
    state
ORDER BY
    state;

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

切断対象である可能性のあるセッション一覧を表示

「SQL実行から10分間以上経過したセッション」または「SQLが実行されず10分間以上経過したセッション」の一覧を表示するSQLです。経過時間が長いセッションからランク付けをしています。PostgreSQL-9.2以上で検証済みです。

--
-- PostgreSQL-9.2以上
--
SELECT
    RANK() OVER (PARTITION BY client_addr ORDER BY BACKEND_START) AS rank,
    pid,
    backend_start,
    query_start,
    state_change,
    datname,
    usename,
    client_addr
FROM pg_stat_activity
WHERE
-- 自分の接続は除外する
pid <> pg_backend_pid()
-- 実行中のSQLがないセッション
AND query IN ('')
AND (
    -- SQL実行から10分間以上経過したセッション、または
    ((current_timestamp - query_start) > interval '10 minutes')
    OR
    -- SQLが実行されず10分間以上経過したセッション
    (query_start IS NULL AND (current_timestamp - backend_start) > interval '10 minutes')
    );

切断しても問題ないと思われる接続を全て切断するクエリー

現在接続中のデータベースのセッションで、現在SQL実行中ではなく、10分間以上何も実行されていないセッションで、postgresユーザー以外のユーザーのセッションを全て切断するクエリーです。「pg_terminate_backend」という関数にプロセスID(pid)を渡すことでセッションを切断できます。SQL中の条件は、適時コメントに従って変更してください。PostgreSQL-9.2以上で動作検証済みです。

--
-- PostgreSQL-9.2以上
--
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE
-- 自分の接続は除外する
pid <> pg_backend_pid()
-- 実行中のSQLがないセッション
AND query IN ('')
-- postgresユーザーの接続は除外
AND usename NOT IN ('postgres')
-- 現在接続中のデータベースの接続のみ対象
AND datname = current_database()
-- セッション状態が次のもののみ対象
AND state IN ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
-- セッションの状態が最後に変わってから10分以上経過した接続
AND (state_change < current_timestamp - interval '10 minutes')
AND (
    -- SQL実行から10分間以上経過したセッション、または
    ((current_timestamp - query_start) > interval '10 minutes')
    OR
    -- SQLが実行されず10分間以上経過したセッション
    (query_start IS NULL AND (current_timestamp - backend_start) > interval '10 minutes')
    );

まとめ

PostgreSQLデータベースのセッションの確認、必要に応じてセッション切断をするSQLを各バージョン毎に記載しました。データベースを管理する上で大変重要ですので、ここで紹介したSQLを利用してデータベース管理を楽にしていただければと思います。

タイトルとURLをコピーしました