PostgreSQL下安装pg_stat_statements

发布时间：2020-12-13 17:22:21 所属栏目：百科来源：网络整理

导读：安装过程如下：进入安装包的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/

安装过程如下：进入安装包的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

shared_preload_libraries = 'pg_stat_statements'，表示要在启动时导入pg_stat_statements 动态库。

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=#

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!