编译安装的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 installmake -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)