主从环境安装扩展pg_stat_statements：

主从环境安装扩展pg_stat_statements：


主：
vi postgresql.conf


shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.track_utility = true
pg_stat_statements.save = true


pg_ctl restart -m fast


主、从：
在源码包里安装
cd /soft/postgresql-9.4.4/contrib/pg_stat_statements
 make && make install


gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_stat_statements.o pg_stat_statements.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -shared -o pg_stat_statements.so pg_stat_statements.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/postgres/lib',--enable-new-dtags  
/bin/mkdir -p '/opt/postgres/lib'
/bin/mkdir -p '/opt/postgres/share/extension'
/bin/mkdir -p '/opt/postgres/share/extension'
/usr/bin/install -c -m 755  pg_stat_statements.so '/opt/postgres/lib/pg_stat_statements.so'
/usr/bin/install -c -m 644 pg_stat_statements.control '/opt/postgres/share/extension/'
/usr/bin/install -c -m 644 pg_stat_statements--1.2.sql pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql '/opt/postgres/share/extension/'






cd /opt/pgsql/share/extension
ls pg_sta*


主：
加载pg_stat_statements模块


psql -f /opt/postgres/share/extension/pg_stat_statements--1.2.sql -p 5432
psql -p 5432
CREATE EXTENSION pg_stat_statements;




测试：
SELECT pg_stat_statements_reset();  
SELECT count(*) FROM pg_stat_statements where total_time/calls>5;






查看插件：
SELECT
  pg_proc.proname AS "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
    JOIN pg_type
   ON (pg_proc.prorettype = pg_type.oid)
WHERE
  pg_type.typname != 'void'
  AND pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91975/viewspace-1806480/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-1806480/