PostgreSQL学习系列（二）

[root@localhost contrib]# cd pg_stat_statements/
[root@localhost pg_stat_statements]# ll
total 476
-rw-r--r--. 1 1107 1107    687 Mar 29 04:07 Makefile
-rw-r--r--. 1 1107 1107   1246 Mar 29 04:07 pg_stat_statements--1.0--1.1.sql
-rw-r--r--. 1 1107 1107   1336 Mar 29 04:07 pg_stat_statements--1.1--1.2.sql
-rw-r--r--. 1 1107 1107   1454 Mar 29 04:07 pg_stat_statements--1.2--1.3.sql
-rw-r--r--. 1 1107 1107   1399 Mar 29 04:07 pg_stat_statements--1.3.sql
-rw-r--r--. 1 1107 1107    449 Mar 29 04:07 pg_stat_statements--unpackaged--1.0.sql
-rw-r--r--. 1 1107 1107  87326 Mar 29 04:07 pg_stat_statements.c
-rw-r--r--. 1 1107 1107    191 Mar 29 04:07 pg_stat_statements.control
-rw-r--r--. 1 root root 223496 May 14 06:41 pg_stat_statements.o
-rwxr-xr-x. 1 root root 143100 May 14 06:41 pg_stat_statements.so
[root@localhost pg_stat_statements]# make install

---1)到数据库中以超级用户执行create extension
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
---2)到配置参数文件中增加参数值
postgres@localhost-> vi postgresql.conf 
shared_preload_libraries = 'pg_stat_statements' 
pg_stat_statements.max = 10000
pg_stat_statements.track = all
....
---3)重启pg，测试
postgres@localhost-> pg_ctl restart -m fast
waiting for server to shut down.... done
postgres@localhost-> psql
psql (9.5.2)
Type "help" for help.

postgres=# select * from pg_stat_statements;
 userid | dbid | queryid | query | calls | total_time | min_time | max_time | 
mean_time | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_b
lks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_b
lks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_re
ad_time | blk_write_time 
--------+------+---------+-------+-------+------------+----------+----------+-
----------+-------------+------+-----------------+------------------+---------
------------+---------------------+----------------+-----------------+--------
------------+--------------------+----------------+-------------------+-------
--------+----------------
(0 rows)

postgres=# select * from pg_stat_statements;
 userid | dbid  |  queryid   |               query               | calls | tot
al_time | min_time | max_time | mean_time | stddev_time | rows | shared_blks_h
it | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks
_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_r
ead | temp_blks_written | blk_read_time | blk_write_time 
--------+-------+------------+-----------------------------------+-------+----
--------+----------+----------+-----------+-------------+------+--------------
---+------------------+---------------------+---------------------+-----------
-----+-----------------+--------------------+--------------------+------------
----+-------------------+---------------+----------------
     10 | 13252 | 4291696289 | select * from pg_stat_statements; |     1 |    
  1.012 |    1.012 |    1.012 |     1.012 |           0 |    0 |              
 0 |                0 |                   0 |                   0 |           
   0 |               0 |                  0 |                  0 |            
  0 |                 0 |             0 |              0
(1 row)

2）点击snapshot，如图2：

3）把安装包拷贝到contrib目录中
[root@localhost ~]# ls
anaconda-ks.cfg            postgresql-9.5.2
pldebugger-14c6caf.tar.gz  postgresql-9.5.2.tar.bz2
[root@localhost ~]# tar -zxvf pldebugger-14c6caf.tar.gz 
pldebugger-14c6caf/
......
pldebugger-14c6caf/settings.projinc
pldebugger-14c6caf/uninstall_pldbgapi.sql
[root@localhost ~]# ll
total 18076
-rw-------. 1 root root     1089 May 12 00:28 anaconda-ks.cfg
drwxrwxr-x. 2 root root     4096 Mar  1 05:33 pldebugger-14c6caf
-rw-r--r--. 1 root root    47764 May 19 00:17 pldebugger-14c6caf.tar.gz
drwxrwxrwx. 6 1107 1107     4096 May 14 06:36 postgresql-9.5.2
-rw-r--r--. 1 root root 18446616 May 13 17:56 postgresql-9.5.2.tar.bz2
[root@localhost ~]# cd pldebugger-14c6caf/
[root@localhost pldebugger-14c6caf]# ll
total 212
-rw-rw-r--. 1 root root  2585 Mar  1 05:33 Makefile
-rw-rw-r--. 1 root root  3711 Mar  1 05:33 README.pldebugger
......
-rw-rw-r--. 1 root root  1387 Mar  1 05:33 uninstall_pldbgapi.sql
[root@localhost pldebugger-14c6caf]# vi README.pldebugger 
PostgreSQL pl/pgsql Debugger API
================================

This module is a set of shared libraries which implement an API for debugging
pl/pgsql functions on PostgreSQL 8.4 and above. The pgAdmin III project
(http://www.pgadmin.org/) provides a client user interface as part of pgAdmin
III v1.10.0 and above.

If you wish to debug functions on PostgreSQL 8.2 or 8.3, please use v0.93 of
the debugger plugin, or checkout the PRE_8_4_SERVER branch from CVS.


Installation
------------

- Copy this directory to contrib/ in your PostgreSQL source tree.

- Run 'make; make install'

- Edit your postgresql.conf file, and modify the shared_preload_libraries config
  option to look like:

  shared_preload_libraries = '$libdir/plugin_debugger'

- Restart PostgreSQL for the new setting to take effect.

- Run the following command in the database or databases that you wish to
  debug functions in:

  CREATE EXTENSION pldbgapi;

  (on server versions older than 9.1, you must instead run the pldbgapi--1.0.sql
  script directly using psql).
[root@localhost ~]# mv pldebugger-14c6caf postgresql-9.5.2/contrib/

2） 点击进入，如图5：

3）点击进入，如图6：

4）解压，点击pgadmin3.msi进行安装，一路next即可