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

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

目次

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

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

select version();

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

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

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

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

使用可能言語を確認する

select * from pg_language;

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

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

select * from pg_user;

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

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

select * from pg_tables;

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

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

select * from pg_stat_user_indexes;

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

ロック情報を表示する

select * from pg_locks;

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

関数の一覧を表示する

select * from pg_proc;

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

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

select * from information_schema.triggers;

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

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

select * from pg_stat_database;

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

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

select * from pg_stat_user_tables;

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

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

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%'; --テーブル名を絞る場合は解放

デッドロックが発生した場合の調査に使えます。どのテーブルのどのクエリで起きているのかといった情報を取得します。
【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;

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

まとめ

いかがでしょうか。
 
コマンドを使わずにSQLを流すだけで監視や運用で使える情報を得られるのはハードルが低くて、かなり便利ですよね。実際に運用をしているといかに素早く対応できるか、安定した仕組みにできるかは知識量にかかっていると感じました。
 
PostgreSQLの特徴として公式の日本語ドキュメントが充実しているので、今後も読んで勉強していきたいです。
 
 
 
 
《関連記事》

• 実務でよく使う！ PostgreSQLで日時を扱うTIPS７選