【pg数据库状态监控相关参数展示】

1、最近项目中做了一个postgresql数据库监控状态功能的需求，需求如下图

2、研究好久，终于在pg数据库的官方文档中找到了相关视图参数，
文档连接如下：
数据库基础信息：
http://www.postgres.cn/docs/9.3/functions-info.html

数据库相关状态视图：
http://www.postgres.cn/docs/9.6/monitoring-stats.html#PG-STAT-DATABASE-VIEW

3、相关参数的sql如下：
3.1 查询数据库基本信息的sql:

select 
current_database() as "数据库名",
current_schema() as "当前模式名",
current_user as "当前用户名",
inet_client_addr() as "远程的客户端连接地址",
inet_client_port() as "远程的客户端连接端口",
inet_server_addr() as "本地的服务端连接地址",
inet_server_port() as "本地的服务端连接端口",
pg_conf_load_time() as "配置载入时间",
pg_postmaster_start_time() as "服务器启动时间",
version() as "版本信息";

3.2 查询所有索引占用磁盘空间大小的sql：

#这里的size单位是字节
SELECT  pg_relation_size(relid) as indexSize, * FROM pg_stat_user_indexes WHERE schemaname = 'public'

3.3 查询数据库占用占用磁盘空间大小的sql：

#这里单位是M,保留两位小数
SELECT ROUND( ( pg_catalog.pg_database_size ( '数据库名' ) / 1048576 :: NUMERIC ), 2 )

3.4 查询数据库用户连接数sql：

 SELECT COUNT ( * ) FROM ( SELECT usename, COUNT ( * ) FROM pg_stat_activity WHERE where datname = '数据库名称' GROUP BY usename ) T

3.5 查询数据库后台连接数sql：

select numbackends from pg_stat_database where datname = '数据库名称'

3.6 查询数据库中被读取的磁盘块的数量和磁盘块被发现已经在缓冲区中的次数的sql

select blks_read,blks_hit from pg_stat_database where datname = '数据库名称'

3.7 查询数据库死锁数量sql

select deadlocks from pg_stat_database where datname = '数据库名称'

3.8 查询数据库等待线程数量sql：

select count(*) from pg_stat_activity where datname = '数据库名称' and waiting = 't'

4、综合8个参数查询sql如下：

SELECT
	datid AS "数据库id",
	datname AS "数据库名称",
	numbackends AS "数据库后台连接数量",
	( SELECT COUNT ( * ) FROM ( SELECT usename, COUNT ( * ) FROM pg_stat_activity WHERE datname = '数据库名称' GROUP BY usename ) T ) AS "数据库后台连接数量",
	blks_read AS "数据库中被读取的磁盘块的数量",
	blks_hit AS "磁盘块被发现已经在缓冲区中的次数",
	deadlocks AS "数据库中被检测到的死锁数",
	( SELECT COUNT ( * ) FROM pg_stat_activity WHERE datname = '数据库名称' AND waiting = 't' ) AS "数据库中等待线程数量",
	( SELECT ROUND( ( pg_catalog.pg_database_size ( '数据库名称' ) / 1048576 :: NUMERIC ), 2 ) ) AS "数据库占用磁盘大小,单位M,保留两位小数",
	(
	SELECT
		ROUND( ( SUM ( i.pg_relation_size ) / 1048576 :: NUMERIC ), 2 ) 
	FROM
		( SELECT indexrelname, pg_relation_size ( relid ) FROM pg_stat_user_indexes WHERE schemaname = 'public' ) i 
	) AS "索引占用磁盘大小,单位M,保留两位小数" 
FROM
	pg_stat_database 
WHERE
	datname = '数据库名称'

执行结果如下：

5、成果展示，如下图：

6、每日一语：但行好事，莫问前程。