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



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

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

タイトルのV$的なものをインストールしたPostgreSQLをDocker Composeですぐ使いたい人は[こちら](https://github.com/kobain-jp/docker-postgres)

# 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まとめ](https://gist.github.com/anvk/475c22cbca1edc5ce94546c871460fdd)

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

[公式DockerイメージのPostgreSQL:12](https://hub.docker.com/_/postgres)

```
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
```
設定パラメータの詳細は[ここから](https://www.postgresql.jp/document/12/html/pgstatstatements.html)

コンテナ再起動

```
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にアップしました。
[こちら](https://github.com/kobain-jp/docker-postgres)から取得できます。

以上になります！！