编译安装 pg_stat_statements

编译安装的PG，可能之前没有编译 pg_stat_statements，需要到源码目录下，重新编译下

➜  pg_stat_statements ll /usr/local/pgsql/share/extension 
total 8.0K
-rw-r--r-- 1 root root 658 Jul 21 09:04 plpgsql--1.0.sql
-rw-r--r-- 1 root root 193 Jul 21 09:04 plpgsql.control

找到pg_stat_statements源码的目录

➜  pg_stat_statements pwd
/workspace/postgresql-16.9/contrib/pg_stat_statements
➜  pg_stat_statements ll
total 152K
drwxrwxrwx 2 1107 1107 4.0K May  5 20:44 expected
-rw-r--r-- 1 1107 1107 1.3K May  5 20:30 Makefile
-rw-r--r-- 1 1107 1107 1.7K May  5 20:30 meson.build
-rw-r--r-- 1 1107 1107 1.3K May  5 20:30 pg_stat_statements--1.0--1.1.sql
-rw-r--r-- 1 1107 1107 1.4K May  5 20:30 pg_stat_statements--1.1--1.2.sql
-rw-r--r-- 1 1107 1107 1.5K May  5 20:30 pg_stat_statements--1.2--1.3.sql
-rw-r--r-- 1 1107 1107  345 May  5 20:30 pg_stat_statements--1.3--1.4.sql
-rw-r--r-- 1 1107 1107  305 May  5 20:30 pg_stat_statements--1.4--1.5.sql
-rw-r--r-- 1 1107 1107 1.4K May  5 20:30 pg_stat_statements--1.4.sql
-rw-r--r-- 1 1107 1107  376 May  5 20:30 pg_stat_statements--1.5--1.6.sql
-rw-r--r-- 1 1107 1107  806 May  5 20:30 pg_stat_statements--1.6--1.7.sql
-rw-r--r-- 1 1107 1107 1.8K May  5 20:30 pg_stat_statements--1.7--1.8.sql
-rw-r--r-- 1 1107 1107 2.1K May  5 20:30 pg_stat_statements--1.8--1.9.sql
-rw-r--r-- 1 1107 1107 2.1K May  5 20:30 pg_stat_statements--1.9--1.10.sql
-rw-r--r-- 1 1107 1107  83K May  5 20:30 pg_stat_statements.c
-rw-r--r-- 1 1107 1107   48 May  5 20:30 pg_stat_statements.conf
-rw-r--r-- 1 1107 1107  205 May  5 20:30 pg_stat_statements.control
drwxrwxrwx 2 1107 1107 4.0K May  5 20:44 sql

编译

➜  pg_stat_statements make && make install

make -C ../../src/backend generated-headers
make[1]: Entering directory '/workspace/postgresql-16.9/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/workspace/postgresql-16.9/src/backend/catalog'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/workspace/postgresql-16.9/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory '/workspace/postgresql-16.9/src/backend/nodes'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/workspace/postgresql-16.9/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/workspace/postgresql-16.9/src/backend/utils'
make[2]: Nothing to be done for 'distprep'.
make -C adt jsonpath_gram.h
make[3]: Entering directory '/workspace/postgresql-16.9/src/backend/utils/adt'
make[3]: 'jsonpath_gram.h' is up to date.
make[3]: Leaving directory '/workspace/postgresql-16.9/src/backend/utils/adt'
make[2]: Leaving directory '/workspace/postgresql-16.9/src/backend/utils'
make[1]: Leaving directory '/workspace/postgresql-16.9/src/backend'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o pg_stat_statements.o pg_stat_statements.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pg_stat_statements.so  pg_stat_statements.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags -lm -fvisibility=hidden 
make -C ../../src/backend generated-headers
make[1]: Entering directory '/workspace/postgresql-16.9/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/workspace/postgresql-16.9/src/backend/catalog'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/workspace/postgresql-16.9/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory '/workspace/postgresql-16.9/src/backend/nodes'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/workspace/postgresql-16.9/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/workspace/postgresql-16.9/src/backend/utils'
make[2]: Nothing to be done for 'distprep'.
make -C adt jsonpath_gram.h
make[3]: Entering directory '/workspace/postgresql-16.9/src/backend/utils/adt'
make[3]: 'jsonpath_gram.h' is up to date.
make[3]: Leaving directory '/workspace/postgresql-16.9/src/backend/utils/adt'
make[2]: Leaving directory '/workspace/postgresql-16.9/src/backend/utils'
make[1]: Leaving directory '/workspace/postgresql-16.9/src/backend'
/usr/bin/mkdir -p '/usr/local/pgsql/lib'
/usr/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql/share/extension'
/usr/bin/install -c -m 755  pg_stat_statements.so '/usr/local/pgsql/lib/pg_stat_statements.so'
/usr/bin/install -c -m 644 ./pg_stat_statements.control '/usr/local/pgsql/share/extension/'
/usr/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.9--1.10.sql ./pg_stat_statements--1.8--1.9.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql  '/usr/local/pgsql/share/extension/'

➜  pg_stat_statements ll /usr/local/pgsql/share/extension 
total 56K
-rw-r--r-- 1 root root 1.3K Jul 29 01:23 pg_stat_statements--1.0--1.1.sql
-rw-r--r-- 1 root root 1.4K Jul 29 01:23 pg_stat_statements--1.1--1.2.sql
-rw-r--r-- 1 root root 1.5K Jul 29 01:23 pg_stat_statements--1.2--1.3.sql
-rw-r--r-- 1 root root  345 Jul 29 01:23 pg_stat_statements--1.3--1.4.sql
-rw-r--r-- 1 root root  305 Jul 29 01:23 pg_stat_statements--1.4--1.5.sql
-rw-r--r-- 1 root root 1.4K Jul 29 01:23 pg_stat_statements--1.4.sql
-rw-r--r-- 1 root root  376 Jul 29 01:23 pg_stat_statements--1.5--1.6.sql
-rw-r--r-- 1 root root  806 Jul 29 01:23 pg_stat_statements--1.6--1.7.sql
-rw-r--r-- 1 root root 1.8K Jul 29 01:23 pg_stat_statements--1.7--1.8.sql
-rw-r--r-- 1 root root 2.1K Jul 29 01:23 pg_stat_statements--1.8--1.9.sql
-rw-r--r-- 1 root root 2.1K Jul 29 01:23 pg_stat_statements--1.9--1.10.sql
-rw-r--r-- 1 root root  205 Jul 29 01:23 pg_stat_statements.control
-rw-r--r-- 1 root root  658 Jul 21 09:04 plpgsql--1.0.sql
-rw-r--r-- 1 root root  193 Jul 21 09:04 plpgsql.control

验证动态库

➜  pg_stat_statements ll /usr/local/pgsql/lib/pg_stat_statements.so 
-rwxr-xr-x 1 root root 56K Jul 29 01:23 /usr/local/pgsql/lib/pg_stat_statements.so

修改配置文件

➜  pg_stat_statements vi /workspace/pgsql/data/postgresql.conf 

增加如下配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.track_utility = on

重启 PostgreSQL 服务​

➜  pg_stat_statements su - postgres
postgres@ws-bughka-0:~$ /usr/local/pgsql/bin/pg_ctl -D /workspace/pgsql/data -l logfile start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started

创建，验证扩展状态​

postgres@ws-bughka-0:~$ /usr/local/pgsql/bin/psql ctmd -E
psql (16.9)
Type "help" for help.

ctmd=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
ctmd=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
        name        | default_version | installed_version |                                comment                                 
--------------------+-----------------+-------------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10            | 1.10              | track planning and execution statistics of all SQL statements executed
(1 row)

ctmd=# 

查看 TOP 10 耗时 SQL​

ctmd=# SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
               query               | calls | total_exec_time | mean_exec_time 
-----------------------------------+-------+-----------------+----------------
 SELECT pg_stat_statements_reset() |     1 |        0.095128 |       0.095128
(1 row)

重置统计信息​

ctmd=# SELECT pg_stat_statements_reset();
 pg_stat_statements_reset 
--------------------------
 
(1 row)