更新はメンバー自ら。AMG Solutionからのお知らせや、日々の業務でのアウトプットを行なっていきます。

# SQL

2020-7.28-Tue

PostgreSQLの運用で押さえておきたい！運用、監視で役立つSQL

こんにちは、飯塚です。

業務でPostgreSQLを知ってから約３年が経ちましたが、単純にSQLを組むだけでなく、運用の知識も必要な場面が出てくるようになりました。運用上よく使ったSQLや、知っておくと便利なSQLを勉強と整理のためにまとめました。

PostgreSQLのバージョンを確認する
postgresql.conf(設定ファイル)の内容をSQLで確認する
使用可能言語を確認する
ユーザーの一覧を表示する
テーブルの一覧を表示する
インデックスの一覧を表示する
ロック情報を表示する
関数の一覧を表示する
トリガーの一覧を表示する
DBごとの統計情報を表示する
テーブルごとの統計情報を表示する
プロセスの統計情報を表示する
応用編１ロックがかかっているプロセスとそのSQLを表示する
応用編２テーブルごとのディスク容量ランキングを確認する

PostgreSQLのバージョンを確認する

select version();

1	select version();

サクッとSQLでバージョンを確認できるのが地味に便利です。
【PostgreSQL 12.0文書】システム情報関数

postgresql.conf(設定ファイル)の内容をSQLで確認する

select * from pg_settings;
select * from pg_settings where name = 'max_connections'; --（例）最大接続数を確認する

1 2	select * from pg_settings; select * from pg_settings where name = 'max_connections'; --（例）最大接続数を確認する

こちらもSQLで見れるのが便利です。
【PostgreSQL 12.0文書】pg_settings

使用可能言語を確認する

select * from pg_language;

1	select * from pg_language;

PL/pgSQLを使用できるか念のため確認するときに使いました。
【PostgreSQL 12.0文書】pg_language

ユーザーの一覧を表示する

select * from pg_user;

1	select * from pg_user;

ユーザー情報を確認できます。ロールを管理する際に見ることがあります。
【PostgreSQL 12.0文書】pg_user

テーブルの一覧を表示する

select * from pg_tables;

1	select * from pg_tables;

テーブルの一覧を取得できます。テーブル数の確認や、全テーブルに一括で流すSQLの作成、「pg_xxx」系テーブル名を思い出すときにも使えます。
【PostgreSQL 12.0文書】pg_tables

インデックスの一覧を表示する

select * from pg_stat_user_indexes;

1	select * from pg_stat_user_indexes;

インデックス作成時の確認などに使います。
【PostgreSQL 12.0文書】統計情報コレクタ

ロック情報を表示する

select * from pg_locks;

1	select * from pg_locks;

トランザクションのロック情報を取得します。
【PostgreSQL 12.0文書】pg_locks

関数の一覧を表示する

select * from pg_proc;

1	select * from pg_proc;

自作関数を作った際の作成確認や、既存関数の確認に使えます。
【PostgreSQL 12.0文書】pg_proc

トリガーの一覧を表示する

select * from information_schema.triggers;

1	select * from information_schema.triggers;

パフォーマンス調整時に地味に便利ですよね。
【PostgreSQL 12.0文書】triggers

DBごとの統計情報を表示する

select * from pg_stat_database;

1	select * from pg_stat_database;

statはstatistic(統計)の略なのでしょう。DBごとのバックエンド数やコミットされたトランザクションの総数などが表示されます。
【PostgreSQL 12.0文書】統計情報コレクタ

テーブルごとの統計情報を表示する

select * from pg_stat_user_tables;

1	select * from pg_stat_user_tables;

シーケンシャルスキャン、インデックススキャン、各スキャンごとの行の取得、挿入・更新・削除の総数、最終VACUUM日時や、最終ANALYZE日時も分かります。挿入件数や削除件数は監視に、最終VACUUM日時、最終ANALYZE日時パフォーマンス検証時のチェックに役立ちそうです。
【PostgreSQL 12.0文書】統計情報コレクタ

プロセスの統計情報を表示する

select * from pg_stat_activity;

1	select * from pg_stat_activity;

バックエンドプロセスのPIDや、開始日時、状態、クエリの内容などを確認できます。古いSQLのコネクションが残り続けている事象の発覚や、プロセスの発生から終了までのタイミングの監視に役立ちます。
【PostgreSQL 12.0文書】統計情報コレクタ

応用編１ロックがかかっているプロセスとそのSQLを表示する。

上記のテーブルなどを組み合わせると、調査や監視に便利なSQLを作成できます。

WITH pg_stats AS ( 
  SELECT
      backendid
      , pg_stat_get_backend_pid(s.backendid) AS procpid
      , pg_stat_get_backend_activity_start(s.backendid) AS 
    START
      , pg_stat_get_backend_activity(s.backendid) AS current_query 
    FROM
      (SELECT pg_stat_get_backend_idset() AS backendid) AS s
) 
SELECT
    pd.datname
    , pc.relname
    , lc.locktype
    , lc.pid
    , lc.mode
    , ps.* 
  FROM
    pg_locks lc 
    INNER JOIN pg_class pc 
      ON pc.oid = lc.relation 
    INNER JOIN pg_database pd 
      ON pd.oid = lc.database 
    INNER JOIN pg_stats ps 
      ON lc.pid = ps.procpid
  WHERE
    mode like '%ExclusiveLock%'; --排他ロックのみ指定
    --AND relname LIKE '%tablename%'; --テーブル名を絞る場合は解放

WITH pg_stats AS (

SELECT

backendid

, pg_stat_get_backend_pid(s.backendid) AS procpid

, pg_stat_get_backend_activity_start(s.backendid) AS

START

, pg_stat_get_backend_activity(s.backendid) AS current_query

FROM

(SELECT pg_stat_get_backend_idset() AS backendid) AS s

)

SELECT

pd.datname

, pc.relname

, lc.locktype

, lc.pid

, lc.mode

, ps.*

FROM

pg_locks lc

INNER JOIN pg_class pc

ON pc.oid = lc.relation

INNER JOIN pg_database pd

ON pd.oid = lc.database

INNER JOIN pg_stats ps

ON lc.pid = ps.procpid

WHERE

mode like '%ExclusiveLock%'; --排他ロックのみ指定

--AND relname LIKE '%tablename%'; --テーブル名を絞る場合は解放

デッドロックが発生した場合の調査に使えます。どのテーブルのどのクエリで起きているのかといった情報を取得します。
【PostgreSQL 12.0文書】統計情報コレクタ
 【PostgreSQL 7.2文書】ロックとテーブル（※バージョン7.2しかドキュメントがない）

応用編２テーブルごとのディスク容量ランキングを確認する

他にもこんなこともできるようです。

SELECT
    *
    , pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE 
  FROM
    ( 
      SELECT
          *
          , total_bytes - index_bytes - coalesce(toast_bytes, 0) AS table_bytes 
        FROM
          ( 
            SELECT
                c.oid
                , nspname AS table_schema
                , relname AS table_name
                , c.reltuples AS row_estimate
                , pg_total_relation_size(c.oid) AS total_bytes
                , pg_indexes_size(c.oid) AS index_bytes
                , pg_total_relation_size(reltoastrelid) AS toast_bytes 
              FROM
                pg_class c 
                LEFT JOIN pg_namespace n 
                  ON n.oid = c.relnamespace 
              WHERE
                relkind = 'r' 
                AND nspname NOT IN ('pg_catalog', 'information_schema')
          ) a
    ) a 
  ORDER BY
    table_bytes DESC;

SELECT

, pg_size_pretty(total_bytes) AS total

, pg_size_pretty(index_bytes) AS INDEX

, pg_size_pretty(toast_bytes) AS toast

, pg_size_pretty(table_bytes) AS TABLE

FROM

(

SELECT

, total_bytes - index_bytes - coalesce(toast_bytes, 0) AS table_bytes

FROM

(

SELECT

c.oid

, nspname AS table_schema

, relname AS table_name

, c.reltuples AS row_estimate

, pg_total_relation_size(c.oid) AS total_bytes

, pg_indexes_size(c.oid) AS index_bytes

, pg_total_relation_size(reltoastrelid) AS toast_bytes

FROM

pg_class c

LEFT JOIN pg_namespace n

ON n.oid = c.relnamespace

WHERE

relkind = 'r'

AND nspname NOT IN ('pg_catalog', 'information_schema')

) a

ORDER BY

table_bytes DESC;

ディスク容量を逼迫しているテーブルを割り出すのに使用できますね。
【PostgreSQL Wiki】Disk Usage
【PostgreSQL 12.0文書】システム管理関数

まとめ

いかがでしょうか。

コマンドを使わずにSQLを流すだけで監視や運用で使える情報を得られるのはハードルが低くて、かなり便利ですよね。実際に運用をしているといかに素早く対応できるか、安定した仕組みにできるかは知識量にかかっていると感じました。

PostgreSQLの特徴として公式の日本語ドキュメントが充実しているので、今後も読んで勉強していきたいです。

《関連記事》