PostgreSQL Insight Monitor pgstat
pgstat 是一个连接到数据库并获取数据库的活动状态的命令行工具。
PostgreSQL有许多状态：
archiver for pg_stat_archiver
bgwriter for pg_stat_bgwriter
connection for connections by type
database for pg_stat_database
table for pg_stat_all_tables
tableio for pg_statio_all_tables
index for pg_stat_all_indexes
function for pg_stat_user_function
tatement for pg_stat_statements
pbpools for pgBouncer pools statistics
pbstats for pgBouncer general statistics
安装pgstat
[root@node1 soft_bak]# git clone https://github.com/gleu/pgstats
[root@node1 soft_bak]# cd pgstats/
[root@node1 pgstats]# ls
License Makefile pgcsvstat.c pgstat.c
[root@node1 pgstats]# make PG_CONFIG=/usr/local/pg945/bin/pg_config
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I/usr/local/pg945/include -I. -I./ -I/usr/local/pg945/include/postgresql/server -I/usr/local/pg945/include/postgresql/internal -D_GNU_SOURCE -c -o pgcsvstat.o pgcsvstat.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 pgcsvstat.o -L/usr/local/pg945/lib -lpgcommon -lpgport -L/usr/local/pg945/lib -lpq -L/usr/local/pg945/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pg945/lib',--enable-new-dtags -o pgcsvstat
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I/usr/local/pg945/include -I. -I./ -I/usr/local/pg945/include/postgresql/server -I/usr/local/pg945/include/postgresql/internal -D_GNU_SOURCE -c -o pgstat.o pgstat.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 pgstat.o -L/usr/local/pg945/lib -lpgcommon -lpgport -L/usr/local/pg945/lib -lpq -L/usr/local/pg945/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pg945/lib',--enable-new-dtags -o pgstat
查看pgstat 帮助
[postgres@node3 pgstats]$ ./pgstat --help
pgstat gathers statistics from a PostgreSQL database.
Usage:
pgstat [OPTIONS] [delay [count]]
General options:
-f FILTER include only this object
-H display human-readable values
-n do not redisplay header
-s STAT stats to collect
-v verbose
-?|--help show this help, then exit
-V|--version output version information, then exit
Connection options:
-h HOSTNAME database server host or socket directory
-p PORT database server port number
-U USER connect as specified database user
-d DBNAME database to connect to
The default stat is pg_stat_bgwriter, but you can change it with the -s command line option,
and one of its value (STAT):
* archiver for pg_stat_archiver
* bgwriter for pg_stat_bgwriter
* connection (only for > 9.1)
* database for pg_stat_database
* table for pg_stat_all_tables
* tableio for pg_statio_all_tables
* index for pg_stat_all_indexes
* function for pg_stat_user_function
* statement for pg_stat_statements (needs the extension)
* xlog for xlog writes (only for > 9.2)
* tempfile for temporary file usage
* pbpools for pgBouncer pools statistics
* pbstats for pgBouncer statistics
查看数据库连接相关信息
可以看到这个数据库有好多连接，需要连接池
查看一个数据库pgbench测试信息
查看表级别的信息
查看指定的表状态信息(通过-f来过滤)
对pg_stat_statements的支持
[postgres@node3 pgstats]$ ./pgstat -s statement -d postgres
pgstat: Cannot find the pg_stat_statements extension.
修改
shared_preload_libraries = 'pg_stat_statements'
# Add settings for extensions here
pg_stat_statements.max = 10000
pg_stat_statements.track = all
[postgres@node3 data]$ cd ../bin/
[postgres@node3 bin]$ ./psql
psql (9.4.5)
Type "help" for help.
postgres=# create extension pg_stat_statements ;
CREATE EXTENSION
[postgres@node3 bin]$ ./pg_ctl -D ../data/ stop -m fast
waiting for server to shut down..... done
server stopped
[postgres@node3 bin]$ ./pg_ctl -D ../data/ start
server starting
[postgres@node3 bin]$ LOG: database system was shut down at 2016-01-15 14:51:09 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
[postgres@node3 bin]$ ./psql
psql (9.4.5)
Type "help" for help.
postgres=# select pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 row)
在数据库运行当中，WAL发生了 多少写操作
以可读的方式显示
查看临时文件和临时文件的大小
也可以通过pg_stat_database查看
更多的使用可以查看pgstat.c文件。