概要
PostgreSQLの統計情報を取得できるようにpgstatsinfoインストール方法をサクッと記載する
前提条件
・PostgreSQL v11インストールがインストールされていること
・postgresql11-contribパッケージがインストールされていること
・PostgreSQLのPGDATA領域を「/data_v11」とする
・下記、ディレクトがオーナ：postgres、グループ：postgres、パーミッション：755であること
/data_v11/pgarch
/data_v11/pgdata
/data_v11/pgxlog
※上記は、PostgreSQL v11インストール手順(シングル構成)を実施すれば設定できます。
pg_statsinfoのパッケージURL
下記にpg_statsinfoのパッケージがあります。
https://sourceforge.net/projects/pgstatsinfo/files/pg_statsinfo/
pg_statsinfoログディレクトリ作成
# mkdir /var/log/postgres
# chown postgres:postgres /var/log/postgres
PostgreSQL v11版pg_statsinfoインストール
# wget https://sourceforge.net/projects/pgstatsinfo/files/pg_statsinfo/11.0/pg_statsinfo-11.0-1.pg11.rhel7.x86_64.rpm
# rpm -ivh pg_statsinfo-11.0-1.pg11.rhel7.x86_64.rpm
準備しています... ################################# [100%]
更新中 / インストール中...
1:pg_statsinfo-11.0-1.pg11.rhel7 ################################# [100%]
pg_statsinfo用定義反映
# vi /data_v11/pgdata/postgresql.conf
～変更～
log_directory = '/var/log/postgres' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名を指定する
log_file_mode = 0644 # creation mode for log files,
log_min_messages = log # values in order of decreasing detail:
track_functions = 'all' # ストアドプロシージャの呼び出しに関する統計情報を収集する
log_checkpoints = on # チェックポイントを記録
shared_preload_libraries = 'pg_statsinfo, pg_stat_statements'
～末尾に追記～
pg_statsinfo.snapshot_interval = 10min
pg_statsinfo.repolog_min_messages = disable # ログ蓄積機能の設定
pg_statsinfo.textlog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) ' # log_line_prefixと同じ形式で指定する。
pg_statsinfo.textlog_permission = 0644 # ログ権限指定
pg_statsinfo.repository_server = 'host=192.168.56.30 user=stats dbname=stats' # pgstatinfoのDB指定
pg_statsinfo.repolog_min_messages = disable # ログ蓄積機能の設定
#pg_statsinfo.long_lock_threshold = 30s # ロック競合情報に記録する対象の条件(閾値)を指定す
# 毎日0時2分に7日間の保持期間を過ぎたスナップショットを自動削除する
pg_statsinfo.enable_maintenance = 'snapshot, repolog'
pg_statsinfo.maintenance_time = '00:02:00' # 自動メンテナンス実行時刻設定
pg_statsinfo.repository_keepday = 7 # スナップショットの保持期間設定
pg_statsinfoのDBユーザとDB作成
# su - postgres
-bash-4.2$ psql
psql (11.11)
"help" でヘルプを表示します。
postgres=# CREATE ROLE stats LOGIN PASSWORD 'stats';
CREATE ROLE
postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+----------------------------------------------------------------------------+--------------
infra | | {}
postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
repl | ログインできません, レプリケーション可 | {}
stats | | {}
postgres=#
postgres=# \q
-bash-4.2$ createdb -O stats stats
-bash-4.2$ psql
psql (11.11)
"help" でヘルプを表示します。
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
postgres | postgres | UTF8 | C | C |
stats | stats | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行)
postgres=# \q
statsへのパスワードなしログイン設定
$ vi .pgpass
192.168.56.30:5432:stats:stats:stats
$ chmod 600 .pgpass
$ exit
pg_statsinfoの設定反映
# systemctl restart postgresql-11
確認
# tail /var/log/postgres/pg_statsinfo.log
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-1 0 LOG: ending log output to stderr
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-1 0 HINT: Future log output will go to log destination "csvlog".
2021-05-07 17:18:09 JST 11510 6094f7c1.2cf6-1 0 LOG: database system was shut down at 2021-05-07 17:18:09 JST
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-2 0 LOG: database system is ready to accept connections
2021-05-07 17:18:09 JST 11516 6094f7c1.2cfc-1 0 LOG: pg_statsinfo launcher started
2021-05-07 17:18:09 JST 11518 - (pg_statsinfod, , , pg_statsinfod) LOG: pg_statsinfo: start
クエリの統計情報を取得設定
# su - postgres
-bash-4.2$ psql -d postgres -c "CREATE EXTENSION pg_stat_statements"
CREATE EXTENSION
スナップショットの一覧を表示
-bash-4.2$ pg_statsinfo -l -h 192.168.56.30 -d stats -p 5432 -U stats
----------------------------------------
Snapshot List
----------------------------------------
SnapshotID InstanceID Host Port Timestamp Comment Execute Time Size
-----------------------------------------------------------------------------------------------------------------------------------------
1 1 postgres.s.srv 5432 2021-05-07 17:20:00 00:00:01 360 KiB
# mkdir /var/log/postgres
# chown postgres:postgres /var/log/postgres
# wget https://sourceforge.net/projects/pgstatsinfo/files/pg_statsinfo/11.0/pg_statsinfo-11.0-1.pg11.rhel7.x86_64.rpm
# rpm -ivh pg_statsinfo-11.0-1.pg11.rhel7.x86_64.rpm
準備しています... ################################# [100%]
更新中 / インストール中...
1:pg_statsinfo-11.0-1.pg11.rhel7 ################################# [100%]
pg_statsinfo用定義反映
# vi /data_v11/pgdata/postgresql.conf
～変更～
log_directory = '/var/log/postgres' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名を指定する
log_file_mode = 0644 # creation mode for log files,
log_min_messages = log # values in order of decreasing detail:
track_functions = 'all' # ストアドプロシージャの呼び出しに関する統計情報を収集する
log_checkpoints = on # チェックポイントを記録
shared_preload_libraries = 'pg_statsinfo, pg_stat_statements'
～末尾に追記～
pg_statsinfo.snapshot_interval = 10min
pg_statsinfo.repolog_min_messages = disable # ログ蓄積機能の設定
pg_statsinfo.textlog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) ' # log_line_prefixと同じ形式で指定する。
pg_statsinfo.textlog_permission = 0644 # ログ権限指定
pg_statsinfo.repository_server = 'host=192.168.56.30 user=stats dbname=stats' # pgstatinfoのDB指定
pg_statsinfo.repolog_min_messages = disable # ログ蓄積機能の設定
#pg_statsinfo.long_lock_threshold = 30s # ロック競合情報に記録する対象の条件(閾値)を指定す
# 毎日0時2分に7日間の保持期間を過ぎたスナップショットを自動削除する
pg_statsinfo.enable_maintenance = 'snapshot, repolog'
pg_statsinfo.maintenance_time = '00:02:00' # 自動メンテナンス実行時刻設定
pg_statsinfo.repository_keepday = 7 # スナップショットの保持期間設定
pg_statsinfoのDBユーザとDB作成
# su - postgres
-bash-4.2$ psql
psql (11.11)
"help" でヘルプを表示します。
postgres=# CREATE ROLE stats LOGIN PASSWORD 'stats';
CREATE ROLE
postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+----------------------------------------------------------------------------+--------------
infra | | {}
postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
repl | ログインできません, レプリケーション可 | {}
stats | | {}
postgres=#
postgres=# \q
-bash-4.2$ createdb -O stats stats
-bash-4.2$ psql
psql (11.11)
"help" でヘルプを表示します。
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
postgres | postgres | UTF8 | C | C |
stats | stats | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行)
postgres=# \q
statsへのパスワードなしログイン設定
$ vi .pgpass
192.168.56.30:5432:stats:stats:stats
$ chmod 600 .pgpass
$ exit
pg_statsinfoの設定反映
# systemctl restart postgresql-11
確認
# tail /var/log/postgres/pg_statsinfo.log
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-1 0 LOG: ending log output to stderr
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-1 0 HINT: Future log output will go to log destination "csvlog".
2021-05-07 17:18:09 JST 11510 6094f7c1.2cf6-1 0 LOG: database system was shut down at 2021-05-07 17:18:09 JST
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-2 0 LOG: database system is ready to accept connections
2021-05-07 17:18:09 JST 11516 6094f7c1.2cfc-1 0 LOG: pg_statsinfo launcher started
2021-05-07 17:18:09 JST 11518 - (pg_statsinfod, , , pg_statsinfod) LOG: pg_statsinfo: start
クエリの統計情報を取得設定
# su - postgres
-bash-4.2$ psql -d postgres -c "CREATE EXTENSION pg_stat_statements"
CREATE EXTENSION
スナップショットの一覧を表示
-bash-4.2$ pg_statsinfo -l -h 192.168.56.30 -d stats -p 5432 -U stats
----------------------------------------
Snapshot List
----------------------------------------
SnapshotID InstanceID Host Port Timestamp Comment Execute Time Size
-----------------------------------------------------------------------------------------------------------------------------------------
1 1 postgres.s.srv 5432 2021-05-07 17:20:00 00:00:01 360 KiB
# su - postgres
-bash-4.2$ psql
psql (11.11)
"help" でヘルプを表示します。
postgres=# CREATE ROLE stats LOGIN PASSWORD 'stats';
CREATE ROLE
postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+----------------------------------------------------------------------------+--------------
infra | | {}
postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
repl | ログインできません, レプリケーション可 | {}
stats | | {}
postgres=#
postgres=# \q
-bash-4.2$ createdb -O stats stats
-bash-4.2$ psql
psql (11.11)
"help" でヘルプを表示します。
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
postgres | postgres | UTF8 | C | C |
stats | stats | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行)
postgres=# \q
$ vi .pgpass
192.168.56.30:5432:stats:stats:stats
$ chmod 600 .pgpass
$ exit
pg_statsinfoの設定反映
# systemctl restart postgresql-11
確認
# tail /var/log/postgres/pg_statsinfo.log
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-1 0 LOG: ending log output to stderr
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-1 0 HINT: Future log output will go to log destination "csvlog".
2021-05-07 17:18:09 JST 11510 6094f7c1.2cf6-1 0 LOG: database system was shut down at 2021-05-07 17:18:09 JST
2021-05-07 17:18:09 JST 11506 6094f7c1.2cf2-2 0 LOG: database system is ready to accept connections
2021-05-07 17:18:09 JST 11516 6094f7c1.2cfc-1 0 LOG: pg_statsinfo launcher started
2021-05-07 17:18:09 JST 11518 - (pg_statsinfod, , , pg_statsinfod) LOG: pg_statsinfo: start
クエリの統計情報を取得設定
# su - postgres
-bash-4.2$ psql -d postgres -c "CREATE EXTENSION pg_stat_statements"
CREATE EXTENSION
スナップショットの一覧を表示
-bash-4.2$ pg_statsinfo -l -h 192.168.56.30 -d stats -p 5432 -U stats
----------------------------------------
Snapshot List
----------------------------------------
SnapshotID InstanceID Host Port Timestamp Comment Execute Time Size
-----------------------------------------------------------------------------------------------------------------------------------------
1 1 postgres.s.srv 5432 2021-05-07 17:20:00 00:00:01 360 KiB
# su - postgres
-bash-4.2$ psql -d postgres -c "CREATE EXTENSION pg_stat_statements"
CREATE EXTENSION
-bash-4.2$ pg_statsinfo -l -h 192.168.56.30 -d stats -p 5432 -U stats
----------------------------------------
Snapshot List
----------------------------------------
SnapshotID InstanceID Host Port Timestamp Comment Execute Time Size
-----------------------------------------------------------------------------------------------------------------------------------------
1 1 postgres.s.srv 5432 2021-05-07 17:20:00 00:00:01 360 KiB
次回はGUIで「pg_statsinfo」をPostgreSQL統計情報を確認します！
参考リンク
pg_statsinfoでサーバの統計情報を取得する（PostgreSQL11）
ありがとうございます
コメント