
こんにちは、飯塚です。
業務でPostgreSQLを知ってから約3年が経ちましたが、単純にSQLを組むだけでなく、運用の知識も必要な場面が出てくるようになりました。運用上よく使ったSQLや、知っておくと便利なSQLを勉強と整理のためにまとめました。
目次
- PostgreSQLのバージョンを確認する
- postgresql.conf(設定ファイル)の内容をSQLで確認する
- 使用可能言語を確認する
- ユーザーの一覧を表示する
- テーブルの一覧を表示する
- インデックスの一覧を表示する
- ロック情報を表示する
- 関数の一覧を表示する
- トリガーの一覧を表示する
- DBごとの統計情報を表示する
- テーブルごとの統計情報を表示する
- プロセスの統計情報を表示する
- 応用編1 ロックがかかっているプロセスとそのSQLを表示する
- 応用編2 テーブルごとのディスク容量ランキングを確認する
PostgreSQLのバージョンを確認する
1 |
select version(); |
サクッとSQLでバージョンを確認できるのが地味に便利です。
【PostgreSQL 12.0文書】システム情報関数
postgresql.conf(設定ファイル)の内容をSQLで確認する
1 2 |
select * from pg_settings; select * from pg_settings where name = 'max_connections'; --(例)最大接続数を確認する |
こちらもSQLで見れるのが便利です。
【PostgreSQL 12.0文書】pg_settings
使用可能言語を確認する
1 |
select * from pg_language; |
PL/pgSQLを使用できるか念のため確認するときに使いました。
【PostgreSQL 12.0文書】pg_language
ユーザーの一覧を表示する
1 |
select * from pg_user; |
ユーザー情報を確認できます。ロールを管理する際に見ることがあります。
【PostgreSQL 12.0文書】pg_user
テーブルの一覧を表示する
1 |
select * from pg_tables; |
テーブルの一覧を取得できます。テーブル数の確認や、全テーブルに一括で流すSQLの作成、「pg_xxx」系テーブル名を思い出すときにも使えます。
【PostgreSQL 12.0文書】pg_tables
インデックスの一覧を表示する
1 |
select * from pg_stat_user_indexes; |
インデックス作成時の確認などに使います。
【PostgreSQL 12.0文書】統計情報コレクタ
ロック情報を表示する
1 |
select * from pg_locks; |
トランザクションのロック情報を取得します。
【PostgreSQL 12.0文書】pg_locks
関数の一覧を表示する
1 |
select * from pg_proc; |
自作関数を作った際の作成確認や、既存関数の確認に使えます。
【PostgreSQL 12.0文書】pg_proc
トリガーの一覧を表示する
1 |
select * from information_schema.triggers; |
パフォーマンス調整時に地味に便利ですよね。
【PostgreSQL 12.0文書】triggers
DBごとの統計情報を表示する
1 |
select * from pg_stat_database; |
statはstatistic(統計)の略なのでしょう。DBごとのバックエンド数やコミットされたトランザクションの総数などが表示されます。
【PostgreSQL 12.0文書】統計情報コレクタ
テーブルごとの統計情報を表示する
1 |
select * from pg_stat_user_tables; |
シーケンシャルスキャン、インデックススキャン、各スキャンごとの行の取得、挿入・更新・削除の総数、最終VACUUM日時や、最終ANALYZE日時も分かります。挿入件数や削除件数は監視に、最終VACUUM日時、最終ANALYZE日時パフォーマンス検証時のチェックに役立ちそうです。
【PostgreSQL 12.0文書】統計情報コレクタ
プロセスの統計情報を表示する
1 |
select * from pg_stat_activity; |
バックエンドプロセスのPIDや、開始日時、状態、クエリの内容などを確認できます。古いSQLのコネクションが残り続けている事象の発覚や、プロセスの発生から終了までのタイミングの監視に役立ちます。
【PostgreSQL 12.0文書】統計情報コレクタ
応用編1 ロックがかかっているプロセスとそのSQLを表示する。
上記のテーブルなどを組み合わせると、調査や監視に便利なSQLを作成できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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しかドキュメントがない)
応用編2 テーブルごとのディスク容量ランキングを確認する
他にもこんなこともできるようです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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の特徴として公式の日本語ドキュメントが充実しているので、今後も読んで勉強していきたいです。
《関連記事》

