postgresql12开启pg_stat_statements扩展

读在最前

本文基于pg12，不同版本可在下面文档连接中类似的操作
https://www.postgresql.org/do...
镜像使用的是postgres:12-alpine

postgresql-contrib介绍

postgresql-contrib也叫contrib模块，这是非pg核心包，大多数是一些统计，性能和同步数据的工具
引用postgres官网中的一句：
These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system,
如果是用apt/yum安装pg，会使用下面的命令

sudo apt update
sudo apt install postgresql postgresql-contrib

pg12中已经包含postgresql-contrib的内容,可在下面文档中查看：
https://www.postgresql.org/do...

开启pg_stat_statments统计插件

1. 修改postgresql.conf

   shared_preload_libraries = 'pg_stat_statements'
   
   pg_stat_statements.max = 10000
   pg_stat_statements.track = all

2. contrib中的插件必须使用superuser,每个数据库都需要执行一次
   This registers the new SQL objects in the current database only

   CREATE EXTENSION module_name;

   本例中，则是

   CREATE EXTENSION pg_stat_statements;

   pg_stat_statements这个插件特殊，会统计所有库的sql执行情况
   When pg_stat_statements is loaded, it tracks statistics across all databases of the server. To access and manipulate these statistics, the module provides a view, pg_stat_statements, and the utility functions pg_stat_statements_reset and pg_stat_statements.

3. pg_stat_statements_reset函数会清空统计信息

   select pg_stat_statements_reset()

docker或者docker-compsose开启

1. docker hub上镜像构建脚本是这样的

   ENV PG_MAJOR=12
   ENV PG_VERSION=12.10-1.pgdg110+1
   /bin/sh -c set -ex
   export PYTHONDONTWRITEBYTECODE=1
   dpkgArch="$(dpkg --print-architecture)"
   aptRepo="[ signed-by=/usr/local/share/keyrings/postgres.gpg.asc ] http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main $PG_MAJOR"
   case "$dpkgArch" in amd64 | arm64 | ppc64el)
    echo "deb $aptRepo" >/etc/apt/sources.list.d/pgdg.list
    apt-get update
    ;;
   *)
    echo "deb-src $aptRepo" >/etc/apt/sources.list.d/pgdg.list
    savedAptMark="$(apt-mark showmanual)"
    tempDir="$(mktemp -d)"
    cd "$tempDir"
    apt-get update
    apt-get install -y --no-install-recommends dpkg-dev
    echo "deb [ trusted=yes ] file://$tempDir ./" >/etc/apt/sources.list.d/temp.list
    _update_repo() {
        dpkg-scanpackages . >Packages
        apt-get -o Acquire::GzipIndexes=false update
    }
    _update_repo
    nproc="$(nproc)"
    export DEB_BUILD_OPTIONS="nocheck parallel=$nproc"
    apt-get build-dep -y postgresql-common pgdg-keyring
    apt-get source --compile postgresql-common pgdg-keyring
    _update_repo
    apt-get build-dep -y "postgresql-$PG_MAJOR=$PG_VERSION"
    apt-get source --compile "postgresql-$PG_MAJOR=$PG_VERSION"
    apt-mark showmanual | xargs apt-mark auto >/dev/null
    apt-mark manual $savedAptMark
    ls -lAFh
    _update_repo
    grep '^Package: ' Packages
    cd /
    ;;
   esac
   apt-get install -y --no-install-recommends postgresql-common
   sed -ri 's/#(create_main_cluster) .*$/\1 = false/' /etc/postgresql-common/createcluster.conf
   apt-get install -y --no-install-recommends "postgresql-$PG_MAJOR=$PG_VERSION"
   rm -rf /var/lib/apt/lists/*
   if [ -n "$tempDir" ]; then
    apt-get purge -y --auto-remove
    rm -rf "$tempDir" /etc/apt/sources.list.d/temp.list
   fi
   find /usr -name '*.pyc' -type f -exec bash -c 'for pyc; do dpkg -S "$pyc" &> /dev/null || rm -vf "$pyc"; done' -- '{}' +
   postgres --version

   阅读docker镜像构建脚本可以发现repo地址是

   http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main

   重点在这行，
   apt-get install -y --no-install-recommends "postgresql-$PG_MAJOR=$PG_VERSION" ,构建时环境变量如果是12，12.10-1.pgdg110+1,则表示

   apt-get install -y --no-install-recommends "postgresql-12=12.10-1.pgdg110+1" 

   所以已经带了contrib模块，直接修改postgres.conf然后create extension即可使用这个view

2. docker compose场景

     db:
    image: postgres:12-alpine
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "5"
    command: postgres  -c config_file=/etc/postgresql/postgresql.conf -c max_connections=${POSTGRES_MAX_CONNECTIONS}
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - ./postgresql/data:/var/lib/postgresql/data
      - ./postgresql/init:/docker-entrypoint-initdb.d
      - ./postgresql/conf/:/etc/postgresql
   

3. docker部署场景

   #获取默认配置
   docker run -i --rm postgres cat /usr/share/postgresql/postgresql.conf.sample > my-postgres.conf
   docker run -d --name some-postgres -v "$PWD/my-postgres.conf":/etc/postgresql/postgresql.conf -e POSTGRES_PASSWORD=mysecretpassword postgres -c 'config_file=/etc/postgresql/postgresql.conf'

   要修改其他参数，可以通过

   docker run -d --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword postgres -c shared_buffers=256MB -c max_connections=200

   更详细使用，阅读https://hub.docker.com/_/post...

   使用和输出

   select *  from pg_stat_statements;

   如果报view不存在，则执行

   CREATE EXTENSION pg_stat_statements;

   视图各列介绍
   
   输出