pg_stat_statements扩展安装步骤说明

太过爱你忘了你带给我的痛 2022-01-11 01:07 919阅读 0赞

一.安装 pg\_stat\_statements

1.安装插件pg\_stat\_statements

\[root@localhost soft\]\# cd postgresql-9.3.0

\[root@localhost postgresql-9.3.0\]\# ls

aclocal.m4 config.log configure contrib doc GNUmakefile.in INSTALL README

config config.status configure.in COPYRIGHT GNUmakefile HISTORY Makefile src

\[root@localhost postgresql-9.3.0\]\# cd contrib/

\[root@localhost contrib\]\# ls

adminpack cube hstore pageinspect pg\_standby pg\_xlogdump tablefunc xml2

auth\_delay dblink intagg passwordcheck pg\_stat\_statements postgres\_fdw tcn

auto\_explain dict\_int intarray pg\_archivecleanup pgstattuple README test\_parser

btree\_gin dict\_xsyn isn pgbench pg\_test\_fsync seg tsearch2

btree\_gist dummy\_seclabel lo pg\_buffercache pg\_test\_timing sepgsql unaccent

chkpass earthdistance ltree pgcrypto pg\_trgm spi uuid-ossp

citext file\_fdw Makefile pg\_freespacemap pg\_upgrade sslinfo vacuumlo

contrib-global.mk fuzzystrmatch oid2name pgrowlocks pg\_upgrade\_support start-scripts worker\_spi

\[root@localhost contrib\]\# cd pg\_stat\_statements/

\[root@localhost pg\_stat\_statements\]\# ls

Makefile pg\_stat\_statements--1.1.sql pg\_stat\_statements.control

pg\_stat\_statements--1.0--1.1.sql pg\_stat\_statements.c pg\_stat\_statements--unpackaged--1.0.sql

\[root@localhost pg\_stat\_statements\]\# make

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D\_GNU\_SOURCE -c -o pg\_stat\_statements.o pg\_stat\_statements.c

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o pg\_stat\_statements.so pg\_stat\_statements.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/pg/9.3/lib',--enable-new-dtags

\[root@localhost pg\_stat\_statements\]\# make install

/bin/mkdir -p '/opt/pg/9.3/lib/postgresql'

/bin/mkdir -p '/opt/pg/9.3/share/postgresql/extension'

/usr/bin/install -c -m 755 pg\_stat\_statements.so '/opt/pg/9.3/lib/postgresql/pg\_stat\_statements.so'

/usr/bin/install -c -m 644 ./pg\_stat\_statements.control '/opt/pg/9.3/share/postgresql/extension/'

/usr/bin/install -c -m 644 ./pg\_stat\_statements--1.1.sql ./pg\_stat\_statements--1.0--1.1.sql ./pg\_stat\_statements--unpackaged--1.0.sql '/opt/pg/9.3/share/postgresql/extension/'

\[root@localhost pg\_stat\_statements\]\#

2.修改配置文件

vi postgresql.conf

shared\_preload\_libraries = 'pg\_stat\_statements'

pg\_stat\_statements.max = 10000

pg\_stat\_statements.track = all

3.重新启动数据库

二、测试

1.建立测试数据库bench

\[postgres@localhost data\]$ createdb bench

2.重置计数

psql bench