公式DockerイメージのPostgreSQL:12にOracleのV$的なものを0からセットアップした話

Oracle経験有、Postgres経験なしの開発者が、
PostgreSQLのORACLEV$系的なものを探し、
Docker公式イメージのPostgreSQL:12にて利用できるようにセットアップした話です。

これからPostgresをキャッチアップしていく人の時短になれば幸いです。

タイトルのV$的なものをインストールしたPostgreSQLをDocker Composeですぐ使いたい人はこちら

ORACLEのV$系に対応するPostgreSQLのビューまとめ

DB	バージョン番号	設定情報	統計情報	実行計画	セッション情報
Oracle	V$VERSION	V$SYSTEM_PARAMETER	V$SQL V$SQLSTATS	V$SQL_PLAN	V$SESSION
Postgres	SELECT version();	pg_settings	pg_stat_statements	pg_store_plans	pg_stat_activity
pg_stat_statementsとpg_store_plansは公式イメージのビルドに加えて別途手順が必要でした。次のセクションから公式イメージのビルドから、上記の全てが利用できるように設定していきます。

上記の調査をしている際にforkさせてもらったSQLまとめ

公式DockerイメージのPostgreSQL:12を起動

公式DockerイメージのPostgreSQL:12

docker run --name postgres_12 -e POSTGRES_DB=developer -e POSTGRES_USER=developer -e POSTGRES_PASSWORD=developer -dp 5432:5432 postgres:12 

VSCode SQLToolsで接続確認

Server Address*:localhost
Port:5432
Database:developer
Username:developer
Password:developer

V$VERSION的なバージョン情報の確認

SELECT version();

PostgreSQL 12.5 (Debian 12.5-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

V$SYSTEM_PARAMETER的な設定情報の確認

SELECT name, setting FROM  pg_settings  where name in ('max_connections','shared_buffers','effective_cache_size','work_mem,maintenance_work_mem','checkpoint_segments','checkpoint_completion_target','fsync,synchronous_commit','random_page_cost','effective_io_concurrency') 

checkpoint_completion_target:0.5
effective_cache_size:524288
effective_io_concurrency:1
max_connections:100
random_page_cost:4
shared_buffers:16384

V$SESSION的な接続情報の確認

SELECT *  FROM pg_stat_activity

出力結果は割愛

5分もかからず構築から接続までできますね。すごい便利！！

pg_stat_statementsのインストール

コンテナ内の/var/lib/postgresql/data/postgresql.confを編集

// コンテナにログイン
docker exec -it postgres_12 /bin/bash 
// postgresql.conf を編集
sed -i -e "s/#shared_preload_libraries = ''.*/shared_preload_libraries = 'pg_stat_statements'	# (change requires restart)\npg_stat_statements.max = 10000\npg_stat_statements.track = all\n/g" /var/lib/postgresql/data/postgresql.conf 
// コンテナからログアウト
exit;

上記実行後のpostgresql.confの変更点

shared_preload_libraries = 'pg_stat_statements'	# (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

設定パラメータの詳細はここから

コンテナ再起動

docker restart postgres_12

SQLToolsからCREATE EXTENSION文を実行

CREATE EXTENSION pg_stat_statements;

これでV$SQL的な統計情報が確認できるようになりました！！

select * from pg_stat_statements;

出力結果は割愛

pg_store_plansのインストール

ソースからビルドする必要があるため、コンテナ内でいろいろインストール

// コンテナにログイン
postgres_12 /bin/bash 
// ビルドに必要なものをいろいろインストール
apt-get update
apt-get install -y apt-utils wget build-essential postgresql-server-dev-12
// ソースを取得
wget https://github.com/ossc-db/pg_store_plans/archive/1.4.tar.gz 
tar xvzf 1.4.tar.gz
cd pg_store_plans-1.4
// コンパイルとインストール
make USE_PGXS=1
make USE_PGXS=1 install 

postgresql.confのshared_preload_librariesにpg_store_plansを追加
*vi /var/lib/postgresql/data/postgresql.confなどで編集

shared_preload_libraries = 'pg_stat_statements,pg_store_plans'	# (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

コンテナ再起動

docker restart postgres_12

SQLToolsからCREATE EXTENSION文を実行

CREATE EXTENSION pg_store_plans;

これでV$SQL_PLAN的な実行計画の確認ができるようになりました！！

select * from pg_store_plans

出力結果は割愛

#DockerfileとDocker-Composeにまとめる

docker-compose upしたら上記の作業が完了しているように、全ての手順をDockerfileと初期実行ファイルにまとめていきます。

Dockerfile

FROM postgres:12

RUN apt-get update && apt-get install -y \
  apt-utils \
  wget \
  build-essential \
  postgresql-server-dev-12 && \
  wget https://github.com/ossc-db/pg_store_plans/archive/1.4.tar.gz && \
  tar xvzf 1.4.tar.gz && \
  cd pg_store_plans-1.4 && \
  make USE_PGXS=1 && \
  make USE_PGXS=1 install 
  
COPY ./docker-entrypoint-initdb.d /docker-entrypoint-initdb.d

上記COPYの箇所ですが、コンテナ内の/docker-entrypoint-initdb.dにshやsqlを配置しておくと初回起動時に実行してくれるので、以下のファイルを配置しています。

docker-entrypoint-initdb.d/00.init.sh

sed -i -e "s/#shared_preload_libraries = ''.*/shared_preload_libraries = 'pg_stat_statements,pg_store_plans'	# (change requires restart)\npg_stat_statements.max = 10000\npg_stat_statements.track = all\n/g" /var/lib/postgresql/data/postgresql.conf

*RUNコマンドで実施すると、まだpostgresql.confがないので、失敗するのでこちらで実行します。

docker-entrypoint-initdb.d/01.init.sql

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_store_plans;

他と一緒に使うことの方が多いのでdocker-composeで動くように以下を作成する。

フォルダ構造

.
├── docker-compose.yml
└── postgres12
    ├── Dockerfile
    └── docker-entrypoint-initdb.d
        ├── 00init.sh
        └── 01init.sql

docker-compose.yml

version: "3.7"
services:

  postgres12:
    container_name: docker_postgre_12
    build: ./postgres12
    ports:
     - 5432:5432
    environment:
      POSTGRES_DB: develop
      POSTGRES_USER: developer
      POSTGRES_PASSWORD: developer
    volumes:
      - ./postgre12/data:/var/lib/postgresql/data

起動する。

docker-compose up

各SQLを試してみる。

select version();
select * from pg_setting;
select * from pg_stat_activity;
select * from pg_stat_statements;
select * from pg_store_plans

全部すぐ使える！！
上記のファイルをGitHubにアップしました。
こちらから取得できます。

以上になります！！