PostgreSQL下安装pg_stat_statements

安装过程如下：进入安装包的contrib/pg_stat_statements目录，执行编译和安装动作：

[root@server pg_stat_statements]# pwd /soft/postgresql-9.1.2/contrib/pg_stat_statements [root@server pg_stat_statements]# make && make install make: `all' 
/bin/mkdir -p '/usr/local/pgsql/lib' /bin/mkdir -p '/usr/local/pgsql/share/extension' /bin/sh ../../config/install-sh -c -m 755  pg_stat_statements.so '/usr/local/pgsql/lib/pg_stat_statements.so' /bin/sh ../../config/install-sh -c -m 644 ./pg_stat_statements.control '/usr/local/pgsql/share/extension/' /bin/sh ../../config/install-sh -c -m 644 ./pg_stat_statements--1.0.sql ./pg_stat_statements--unpackaged--1.0.sql  '/usr/local/pgsql/share/extension/' [root@server pg_stat_statements]#

然后，由于pg_stat_statements 既使用了hook，同时又是以 extension 形式被使用，所以需要再作两件事情：

A: 在postgresql.conf中，进行配置：

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

custom_variable_classes = 'pg_stat_statements'，是为了其下面的几个配置项。
pg_stat_statements.max = 1000，表示监控的语句最多为1000句。
pg_stat_statements.track = top，表示不监控嵌套的sql语句。
pg_stat_statements.track_utility = true，表示对 INSERT/UPDATE/DELETE/SELECT 之外的sql动作也作监控。
pg_stat_statements.save = true，表示当postgresql停止时，把信息存入磁盘文件以备下次启动时再使用。

B: 重新启动 postgresql，然后生成 extension:

[root@server ~]# su - postgres postgres@server ~]$ pwd /home/postgres ]$ cd /usr/local/pgsql postgres@server pgsql]$ ./bin/psql -d postgres psql (9.1.2) Type "help" for help. postgres=# create extension pg_stat_statements; CREATE EXTENSION postgres

postgresselect count(*) from pg_stat_statements; count ------- 2 (1 row) postgres=# \x Expanded display is on. postgresselect * from pg_stat_statements; - RECORD 1 ]-----+----------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------- userid | 10 dbid 12699 query | from pg_stat_statements; calls 1 total_time 5.4e-05 rows 1 shared_blks_hit 0 shared_blks_read 0 shared_blks_written 0 local_blks_hit 0 local_blks_read 0 local_blks_written 0 temp_blks_read 0 temp_blks_written 0 RECORD 2 SELECT n.nspname as "Schema",| c.relname as "Name",128); font-family:'Courier New'!important; line-height:1.5!important">| CASE c.relkind WHEN 'r' THEN tablevviewiindexSsequencesspecialfforeign tableEND as "T ype",128); font-family:'Courier New'!important; line-height:1.5!important">| pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (','') | AND n.nspname <> pg_catalog' information_schemaAND n.nspname !~ ^pg_toast| AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; calls 0.00081 rows 0 shared_blks_hit 564 shared_blks_read 1 shared_blks_written RECORD 3 -----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ userid create extension pg_stat_statements; calls 0.199004 rows 489 shared_blks_read 105 shared_blks_written 0 postgres=#

PostgreSQL下安装pg_stat_statements

猜你在找的Postgre SQL相关文章