Postgres数据库运行状态监控及元数据查询方法

Z时代
2024-01-10
分类：综合

database

数据库空间占用

SELECT
	pg_database.datname,
	pg_size_pretty ( pg_database_size ( pg_database.datname ) ) AS SIZE 
FROM
	pg_database 
WHERE
	pg_database.datistemplate = FALSE

查询结果示例：

water_ommp 8633 kB water_workflow 10137 kB mdmres 10017 kB water_all_dev 2098 MB

表空间占用

# 1）查询表空间
SELECT
	spcname 
FROM
	pg_tablespace;
#结果示例：
pg_default
pg_global
#2）查询某个表空间大小
SELECT
	pg_size_pretty ( pg_tablespace_size ( "pg_default" ) );
#结果示例：
25 GB

当前连接数

SELECT COUNT
	( 1 ) 
FROM
	pg_stat_activity;

最大连接数

SHOW max_connections;

慢SQL查询

需要安装插件pg_stat_statements，安装方式可参见： https://blog.csdn.net/pg_hgdb/article/details/79594775

SELECT SUBSTRING
	( query, 1, 1000 ) AS short_query,
	round( total_time :: NUMERIC, 2 ) AS total_time,
	calls,
	round( ( 100 * total_time / SUM ( total_time :: NUMERIC ) OVER ( ) ) :: NUMERIC, 2 ) AS percentage_cpu 
FROM
	pg_stat_statements 
WHERE
	LOWER ( query ) LIKE"select%" 
ORDER BY
	total_time DESC 
	LIMIT 5;

元数据采集

数据库信息

SELECT
	oid,
	datname 
FROM
	pg_database 
WHERE
	datistemplate = FALSE;

Schema信息

SELECT SCHEMA_NAME 
FROM
	information_schema.schemata 
WHERE
	CATALOG_NAME = "water_all_dev" 
	AND SCHEMA_NAME NOT LIKE"pg_%" 
	AND SCHEMA_NAME != "information_schema";

表信息

SELECT
	tablename 
FROM
	pg_tables 
WHERE
	schemaname = "sda";

字段信息

SELECT
	attname,
	attnum,
	TYPE.typname,
CASE
		WHEN attlen < 0 THEN
		atttypmod ELSE attlen 
	END AS attrlen,
	attnotnull,
CASE
		WHEN atthasdef THEN
		def.adsrc ELSE NULL 
	END attdef,
CASE
	WHEN con.contype = "p" THEN
TRUE ELSE FALSE 
	END attrpri 
FROM
	pg_class
	C INNER JOIN pg_attribute attr ON C.oid = attr.attrelid
	INNER JOIN pg_type TYPE ON TYPE.oid = attr.atttypid
	INNER JOIN pg_namespace nps ON nps.oid = C.relnamespace
	LEFT JOIN pg_attrdef def ON ( def.adrelid = C.oid AND def.adnum = attr.attnum )
	LEFT JOIN pg_constraint con ON ( con.conrelid = C.oid AND attr.attnum = con.conkey [ 1 ] ) 
WHERE
	relname = "sda_dict_t" 
	AND nps.nspname = "sda" 
	AND attnum > 0 
ORDER BY
	attnum;