【PostgreSQL】学習用環境構築のすゝめ

初めに

昨今PostgreSQLの需要が高まっているので、何かとPostgreSQLに触る人も多いのではないでしょうか。

PostgteSQLとはオープンソースソフトウェア（OSS）のRDBMSであるため、各企業（ベンダー）が提供するデータベースとは異なり、使用にあたってライセンス費用が掛からないメリットがあります。ライセンス費用が掛からないためベンダーロックインの脱却などの観点から他のDBからPostgreSQLへ移行するパターンが多いです。

ただし、移行するにあたって移行元となった他のDBにはある機能が、PostgreSQLにはない場合があります。
例えばOracleでいうAWRレポートみたいなものが該当します。
PostgreSQLにおいてAWRレポートに相当するものとして、pg_statsinfoが指定した一定の間隔でスナップショットとして統計情報などを収集してくれます。
そのため、pg_statsinfoはPostgreSQLを利用するうえではかなりメジャーな拡張モジュールです。ただし、標準で備わっている機能ではなく、後から追加する機能（拡張モジュール）となっており、導入が手間だったり、設定が分かりづらかったりすることが多々あります。

今回の記事ではPostgreSQLに興味があるが触ったことがない人向けに学習用の環境構築手順を紹介します。
併せて、すでに触りなれている人向けにpg_statsinfoに関して疑問に思った部分の検証と調査をした内容をおまけとして執筆していきます。

構築する環境

今回は下記の端末、OS、ソフトを用いて環境を構築しました。

端末：RasberyPi 4B ※1
OS：Ubuntu Server 22.04.2 LTS
DB：PostgreSQL 15.3

※今回利用する端末のRasberyPiとはワンボードマイコンと呼ばれる小さなPCであり、もともとは電子工作やプログラム制作などの教育目的として開発された安価なPC。Linux系OSを入れることも可能であるため簡易なサーバとしても使えます。

環境構築手順

PostgreSQLのインストール（1台目、２台目に必要）

インストール方法は「ソースコードからのインストール」と「rpmパッケージによるインストール」の２パターンありますが、今回は「ソースコードからのインストール」で構築します。理由はrpmパッケージの場合、既に各OSごとにコンパイルされているものであるため、OSによる手順の差異が生まれやすいためです。
この記事ではだれでも環境構築できることを目指すものなので、OSの差異による手順の差異を極力排除する意図があります。

初めにPostgreSQL用にpostgresユーザを作成します

$ sudo useradd -m postgres
$ sudo passwdsswd postgres

併せて、この後のPostgreSQLパッケージのビルドに必要な下記パッケージもインストールしておきます

$ sudo apt-get install build-essential

PostgreSQLをインストールするために公式サイトから下記コマンドでパッケージをダウンロードし解凍します

$ sudo wget https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.gz
$ sudo tar zxf postgresql-1.3.tar.gz

インストール先ディレクトリを作成しておきます

$ sudo mkdir /home/postgres/pgsql

下記手順でパッケージのビルドとインストールをします

$ su - postgres
$ cd /usr/local/src/postgresql-15.3
$ ./configure --prefix=/home/postgres/pgsql
$ make
$ sudo make install
# exit

postgresqlユーザーの「.profaile」に下記を書き込み、反映させます

$ vi /home/postgres/.profile
export PATH=/home/postgres/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/home/postgres/pgsql/lib:$LD_LIBRARY_PATH
export PGDATA=/home/postgres/data

$ source .profile

データベースクラスタ用のディレクトリを作成しデータベースを起動します

$ mkdir /home/postgres/data
$ initdb -D /home/postgres/data --no-locale
$ pg_ctl start

1台目端末のPostgreSQLに任意のデータベースを作成

$ createdb examdb

2台目端末のPostgreSQLに任意のデータベースを作成

 $ createdb repo

pg_statsinfoの導入（1台目に必要）

続いて拡張モジュールであるpg_statsinfoを導入します。
なお、拡張モジュールは基本的にPostgreSQLのソース配下のcontoribディレクトリに資材を配置して導入します。

初めに公式などからダウンロードしてきた資材（pg_statsinfo-15.3）をcontoribディレクトリに配置します。

$ cp /home/postgres/15.1.tar.gz postgresql-15.3/contrib/

下記手順でビルドとインストールをします

$ cd postgresql-15.3/contrib/pg_statsinfo-15.1/
$ make
$ sudo make install

postgresql.confに下記設定を追加する（公式より抜粋）

vi /var/lib/pgsql/data/postgresql.conf

#最小設定
shared_preload_libraries = 'pg_statsinfo'       # 事前ロードを行う
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名を指定する

#推奨設定
shared_preload_libraries = 'pg_statsinfo'       # 事前ロードを行う
pg_statsinfo.snapshot_interval = 1min          # スナップショットの取得間隔pg_statsinfo.enable_maintenance = 'on'          # 自動メンテナンス設定
pg_statsinfo.maintenance_time = '00:02:00'      # 自動メンテナンス実行時刻設定
pg_statsinfo.repolog_min_messages = disable     # ログ蓄積機能の設定
pg_statsinfo.repository_server = 'host=192.168.11.104 port=5432 dbname=repo user=postgres' #リポ
トリDB設定。接続文字列を指定する。
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名を指定する
log_min_messages = 'log'        # ログへ出力するメッセージレベル。
pg_statsinfo.syslog_min_messages = 'error'      # syslogに出力するログレベルを指定する
pg_statsinfo.textlog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) '
# pg_statsinfoがテキストログファイルに出力する際、各行の先頭に追加される書式を指定する
log_line_prefixと同じ形式で指定する。
pg_statsinfo.syslog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) '
# pg_statsinfoがsyslog経由でログを出力する際、各行の先頭に追加される書式を指定する。
track_functions = 'all'                        # ストアドプロシージャの呼び出しに関する統計情報を収集する
log_checkpoints = on                           # チェックポイントを記録
log_autovacuum_min_duration = 0              # 自動バキュームを記録
#pg_statsinfo.long_lock_threshold = 30s         # ロック競合情報に記録する対象の条件(閾値)を指定する

pg_statsinfo.repository_serverはスナップショットを保存するデータベース（リポジトリDB）を指定します。

筆者の場合はスナップショット専用に端末ごと別環境を用意していますが、同一データベースクラスタ内にスナップショット用のデータベースを用意した場合は「host=localhost」にするとよいでしょう。

postgres再起動します

$ pg_ctl restart

下記コマンドを実行し、スナップショットの一覧が表示されれば導入成功となります

$ pg_statsinfo -h 192.168.11.104 -d repo -l     
8780           2  unit3                                 5432   2025-02-27 20:18:00                            00:00:01    48 KiB
8781           2  unit3                                 5432   2025-02-27 20:19:00                            00:00:01  8192 bytes
8782           2  unit3                                 5432   2025-02-27 20:20:00                            00:00:01    16 KiB

※pg_statsinfoのスナップショット取得間隔の設定（ 設定パラメータの「pg_statsinfo.snapshot_interval」）次第では直後にコマンド実行をしてもまだスナップショットが生成されておらず一覧に何も表示されない場合があるので、その時はしばらくしてから再度コマンドを実行してみるとよいです。

これでpg_statsinfoの導入は完了です。

▪pg_stats_reporter（２台目に必要）

続いてpg_stats_reporterの導入をするが、実はこれの導入は単純で公式からダウンロードしたパッケージ（Source code(tar.gz)）に同行されているINSTALL.jaファイルの内容に従えばインストール可能であり、その他の細かい手順も難しいところはなく公式マニュアルを参照すれば問題ないため割愛します。
ちなみにwebで表示するためのapacheも同梱されているため別途apache HTTP Serverを導入する必要はないです。

手順を割愛する代わりにpg_stats_reporterの説明を簡単にすると、pg_statsinfoで取得できるスナップショットをブラウザで見やすくしてくれるツールとなっています。

下記に示すように通常は文字列ベースで大量に出力されるものが、pg_stats_reporterを利用すれば下記のように参照可能となります。

$ pg_statsinfo -h localhost -d repo -r sumary -B 2025-02-27

---------------------------------------------
STATSINFO Report (host: unit3, port: 5432)
---------------------------------------------

----------------------------------------
/* Summary */
----------------------------------------
Database System ID   : 7286459308591875856
Host                 : unit3
Port                 : 5432
PostgreSQL Version   : 15.3
Snapshot Begin       : 2025-02-27 20:18:00
Snapshot End         : 2025-02-27 21:17:00
Snapshot Duration    : 00:59:00
Total Database Size  : 79 MiB
Total Commits        : 1597
Total Rollbacks      : 0

以上、ここまでがpostgreSQL初学者向けの環境構築手順です。

今回の構築は端末２台での想定ですが、１台だけで構築する場合は上記手順のインストール作業を１台ですべて実施することで可能です（構成は数のイメージ）。

pg_statsinfo設定時に気になったポイント

初学者向けということで学習用PostgreSQL環境構築手順を記載しましたが、筆者が実際に構築した環境を触っていて、いくつか疑問を感じた部分があります。
以下はその疑問について、構築した環境を利用して検証・調査を行ったものを記載します。

なお検証時のDBの状態は下記のものとします。

パラメータの「pg_statsinfo.target_server」は何を指定するのか

pg_statsinfoを導入したときに、公式マニュアルを参照しながら推奨設定として定められているパラメータの設定を実施しました。

その後、pg_statsinfoのスナップショットを確認すると、examdbとpostgresの２つのデータベースの情報が取得されていることが確認できたましたが、特にどこかで指定したわけでもないのにtemplate0とtemplate1は取得から除外されていることから、どうやって監視対象とするデータベースを判別しているのか、できるのかが気になりました。

公式マニュアルを確認すると、上記のpostgresql.confで設定したパラメータ以外にpg_statsinfo.target_serverというパラメータがあることがわかり、下記のように説明されていました。

「監視対象DBへの接続文字列 (*4)。pg_statsinfoは統計情報収集などのために監視対象DBへ接続します。 デフォルトではこの接続にDBクラスタ作成時の初期ユーザおよび初期データベース(postgres)が使用されます。 この接続設定を変更する必要がある場合には当該パラメータを設定します。なお、ユーザを指定する場合はスーパユーザを指定する必要があることに注意してください。」

これだけ見るとなんだか監視対象とするデータベースを設定してくれそうに見えたため、試しに任意のデータベースを適当に設定してみましたが、ここに設定したデータベースにかかわらず、データベースクラスタ内のすべてのデータベースのスナップショットが取得されました。

つまりpg_statsinfo.target_serverは監視対象とするデータベースの指定とは関係がないということになります。

そこでpg_statsinfo.target_serverでの指定は何を意味するのかが気になり、設定されたデータベースに何かしら変化はないのかということで検証を行いました。

検証はtestという名前のデータベースを作成し、pg_statsinfo.target_serverに指定される前後で違いが生じるかを確認しました。

初めに検証用にtestという名前のデータベースを作成し、状態を確認します。

正常に作成されたのでpostgresql.confでpg_statsinfo.target_serverに作成したデータベースを指定

postgres@ubuntu:~$ vim data/postgresql.conf
pg_statsinfo.target_server = “test”

※設定変更後は変更内容を反映させるために再起動します

再起動後に設定が反映されていることを確認します

対象のデータベースの状態を確認するとstatsinfoスキーマが追加されています

この検証からpg_statsinfo.target_serverに指定されたデータベースにはstatsinfoが作成されることがわかります。

ではこのstatsinfoスキーマは何なのでしょうか。

実はpg_statsinfoがスナップショットを取得するために必要な関数などが含まれるスキーマです。

つまり、pg_statsinfo.target_serverはpg_statsinfoがスナップショットを取得するために必要な関数があるstatsinfoスキーマをどのデータベースに用意するのかを指定してあげるパラメータとなっています。

なお、公式マニュアル記載の通り、pg_statsinfo.target_serverのデフォルトはpostgresデータベースであるため、指定しない場合、postgresデータベース内にstatsinfoスキーマが作成されます。

ちなみに下記のコマンドで手動でスナップショットを取得することもできますが、-dオプションで接続先を指定する部分がpg_statsinfo.target_serverパラメータで指定するデータベースに相当します。

psql -d repo -c "select statsinfo.snapshot()"

pg_statsinfoの監視対象って結局どこなのか

pg_statsinfo.target_server が監視対象とするデータベースを選択するパラメータではないことが明らかとなったので、先の検証で記載したようにpg_statsinfoがデータベースクラス全体の情報を取得してくるというのはデフォルトの挙動のようであることがわかります。

別にそれは結構なことではあるのですが、運用次第では毎度大量の情報を持ってきたり、pg_statsinfo用に用意したリポジトリ自身の情報まで持ってきてしまうことにもなるので、場合によってはpg_statsinfo自体がDBの性能に影響を与える可能性がでてきます。

何かしら指定する方法がないものか確認してみると公式マニュアルの設定ファイルの説明の中に追加項目設定としてpg_statsinfo.excluded_dbnamesというパラメータの記載がありました。

これの説明は下記のとおりです。

監視対象から除外するデータベース名

つまり、先ほど記載したようにデータベースクラス全体の情報を取得してくる挙動がデフォルト※であり、pg_statsinfo.excluded_dbnamesで対象を絞るということらしいです。

言い換えるとホワイトリスト形式ではなくブラックリスト形式で指定するイメージになります。

※template0,template1はデフォルトで除外されます。

こっちについてはマニュアルを最初からちゃんと読んでいればという気もしますが、pg_statsinfo.target_serverの名前と説明が紛らわしすぎるせいにしておきたいです。

まとめ

今回の内容は以下の通りです。

・ベンダーロックイン脱却などの観点からPostgreSQLの需要が高まっている。
・学習用PostgreSQL環境（pg_statsinfoとpg_statsre_porter含む）のソースコードパッケージからの構築方法。
・pg_statsinfoはPostgreSQLの様々な性能情報を取得してくれる（OracleのAWRレポートに相当する）。
・pg_stats_reporterはpg_statsinfoが取得した情報をweb形式で可視化してくれる。
・pg_statsinfo.target_serverはpg_statsinfoが動くための関数などを格納するためのスキーマを生成するデータベースを指定するための関数であること。
・pg_statsinfoはデフォルトでtemplate0,template1以外のデータベースクラスタ内のデータベースすべての情報を取得対象とし、対象を指定したいときは取得したくない対象をpg_statsinfo.excluded_dbnamesに指定して除外することで実現できる。

最後に

皆様いかかがでしたでしょうか。
今回の環境構築手順はだれでも学習用の環境を構築できることを念頭にまとめてみました。
実際に環境を作ってみることで知識としては知っていても意外と気づいていない不明点が浮き彫りになることがよくあります。
特に拡張モジュールなんかは公式とは別で提供されているものなので実際に構築することで得られる学びは多います。
今回のPostgreSQLに限らず、興味があるものは実際に触ってみるといいかもしれないですね。

以上、「PostgreSQL環境構築のすゝめ」でした。