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 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文件。