AMG Solution

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

feels

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

目次

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

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

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

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

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

使用可能言語を確認する

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

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

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

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

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

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

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

ロック情報を表示する

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

関数の一覧を表示する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

まとめ

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

feels

IIZUKA'S BLOG

飯塚の記事

飯塚の記事の最新情報をお届けいたします。

SAME CATEGORY BLOG

この記事と同様のカテゴリー記事

日報で業務改善
はじめました。
LOADING