The story of setting up an Oracle V $ -like thing from 0 on PostgreSQL: 12 of the official Docker image

Developers with Oracle experience and no Postgres experience Look for PostgreSQL's ORACLE V $ series, It is a story set up so that it can be used with PostgreSQL: 12 of the official Docker image.

I hope it will shorten the time for those who will catch up with Postgres from now on.

If you want to use PostgreSQL with V $ -like title installed on Docker Compose immediately here

PostgreSQL view summary corresponding to ORACLE V $ system

pg_stat_statements and pg_store_plans required additional steps in addition to building the official image. From the next section, build the official image and set it up so that all of the above is available.

I was asked to fork during the above investigation SQL Summary

Launch PostgreSQL: 12 official Docker image

Official Docker image PostgreSQL: 12

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

Check connection with VSCode SQL Tools

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

Confirmation of V \ $ VERSION-like version information

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

Confirmation of V $ SYSTEM_PARAMETER-like setting information

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

Checking V $ SESSION-like connection information

SELECT *  FROM pg_stat_activity

Output result is omitted

You can build and connect in less than 5 minutes. Great convenience! !!

Install pg_stat_statements

Edit /var/lib/postgresql/data/postgresql.conf in the container

//Log in to the container
docker exec -it postgres_12 /bin/bash 
// postgresql.Edit 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 
//Log out of container
exit;

Changes in postgresql.conf after the above execution

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

For details on the setting parameters, click here (https://www.postgresql.jp/document/12/html/pgstatstatements.html)

Container restart

docker restart postgres_12

Execute the CREATE EXTENSION statement from SQL Tools

CREATE EXTENSION pg_stat_statements;

Now you can see V $ SQL stats! !!

select * from pg_stat_statements;

Output result is omitted

Install pg_store_plans

Since it is necessary to build from source, various installations in the container

//Log in to the container
postgres_12 /bin/bash 
//Install various things required for build
apt-get update
apt-get install -y apt-utils wget build-essential postgresql-server-dev-12
//Get the source
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
//Compile and install
make USE_PGXS=1
make USE_PGXS=1 install 

Added pg_store_plans to shared_preload_libraries in postgresql.conf

• Edit with vi /var/lib/postgresql/data/postgresql.conf etc.

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

Container restart

docker restart postgres_12

Execute the CREATE EXTENSION statement from SQL Tools

CREATE EXTENSION pg_store_plans;

Now you can check the execution plan like V \ $ SQL_PLAN! !!

select * from pg_store_plans

Output result is omitted

Put together in Dockerfile and Docker-Compose

After docker-compose up, we will put all the steps together in a Dockerfile and an initial executable so that the above work is completed.

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

Regarding the above COPY part, if you place sh or sql in /docker-entrypoint-initdb.d in the container, it will be executed at the first startup, so the following files are placed.

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

If you execute it with the * RUN command, it will fail because postgresql.conf does not exist yet, so execute it here.

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

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_store_plans;

Since it is more often used with others, create the following so that it works with docker-compose.

Folder structure

.
├── 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

to start.

docker-compose up

Try each SQL.

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

All can be used immediately! !! I uploaded the above file to GitHub. You can get it from here.

That's it! !!