【数据库】PG数据库状态巡检

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

以下为PG数据库状态检测常用的方法，未包括安全相关内容，后续持续完善

---

一、环境变量

请将以下变量修改为与当前环境一致, 并且确保使用这个配置连接任何数据库都不需要输入密码

export PGHOST=127.0.0.1
export PGPORT=54321
export PGDATABASE=test
export PGUSER=system
export PGPASSWORD=12345678ab
export PGDATA=/home/jp/database/ES/data
export PGHOME=/opt/pgsql

export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
# 记住当前目录
PWD=`pwd`

# 获取postgresql日志目录
pg_log_dir=`grep '^\ *[a-z]' $PGDATA/postgresql.conf|awk -F "#" '{print $1}'|grep log_directory|awk -F "=" '{print $2}'`

二、数据库信息

2.1 基本信息

"--数据库版本: "
select version();

"--用户已安装的插件版本: "
select current_database(),* from pg_extension;

"--用户使用了多少种数据类型: "
select current_database(),b.typname,count(*) from pg_attribute a,pg_type b where a.atttypid=b.oid and a.attrelid in (select oid 
from pg_class 
where relnamespace not in 
(select oid from pg_namespace where nspname ~ $$^pg_$$ or nspname=$$information_schema$$)) 
group by 1,2 order by 3 desc;

"--用户创建了多少对象: "
select current_database(),rolname,nspname,relkind,count(*) 
from pg_class a,pg_authid b,pg_namespace c 
where a.relnamespace=c.oid and a.relowner=b.oid and nspname !~ $$^pg_$$ and nspname<>$$information_schema$$ 
group by 1,2,3,4 order by 5 desc;

"-- 用户对象占用空间的柱状图: "
select current_database(),buk this_buk_no,cnt rels_in_this_buk,pg_size_pretty(min) buk_min,pg_size_pretty(max) buk_max from( select row_number() over (partition by buk order by tsize),tsize,buk,min(tsize) over (partition by buk),max(tsize) over (partition by buk),count(*) over (partition by buk) cnt 
from ( select pg_relation_size(a.oid) tsize, width_bucket(pg_relation_size(a.oid),tmin-1,tmax+1,10) buk 
from (select min(pg_relation_size(a.oid)) tmin,max(pg_relation_size(a.oid)) tmax 
from pg_class a,pg_namespace c where a.relnamespace=c.oid and nspname !~ $$^pg_$$ and nspname<>$$information_schema$$) t, pg_class a,pg_namespace c where a.relnamespace=c.oid and nspname !~ $$^pg_$$ and nspname<>$$information_schema$$ ) t)t 
where row_number=1;

 "--当前用户的操作系统定时任务: "
echo "I am `whoami`"
crontab -l
"建议: "
"    仔细检查定时任务的必要性, 以及定时任务的成功与否的评判标准, 以及监控措施. "
"    请以启动数据库的OS用户执行本脚本. "

2.2 配置文件信息

"--获取pg_hba.conf md5值: "
md5sum $PGDATA/pg_hba.conf
"建议: "
"    主备md5值一致(判断主备配置文件是否内容一致的一种手段, 或者使用diff)."

"--获取pg_hba.conf配置: "
grep '^\ *[a-z]' $PGDATA/pg_hba.conf
"建议: "
"    主备配置尽量保持一致, 注意trust和password认证方法的危害(password方法 验证时网络传输密码明文, 建议改为md5), 建议除了unix socket可以使用trust以外, 其他都使用md5或者LDAP认证方法."
"    建议先设置白名单(超级用户允许的来源IP, 可以访问的数据库), 再设置黑名单(不允许超级用户登陆, reject), 再设置白名单(普通应用), 参考pg_hba.conf中的描述. "

"--获取postgresql.conf md5值: "
md5sum $PGDATA/postgresql.conf
"建议: "
"    主备md5值一致(判断主备配置文件是否内容一致的一种手段, 或者使用diff)."

"--获取postgresql.conf配置: "
grep '^\ *[a-z]' $PGDATA/postgresql.conf|awk -F "#" '{print $1}'

"--用户或数据库级别定制参数: "
select * from pg_db_role_setting;
"建议: "
"    定制参数需要关注, 优先级高于数据库的启动参数和配置文件中的参数, 特别是排错时需要关注. "

2.3 数据库错误日志分析

2.3.1 错误日志信息

cat *.csv | grep -E "^[0-9]" | grep -E "WARNING|ERROR|FATAL|PANIC" | awk -F "," '{print $12" , "$13" , "$14}'|sort|uniq -c|sort -rn

"建议: "
"    参考 http://www.postgresql.org/docs/current/static/errcodes-appendix.html ."

2.3.2 连接请求情况

find . -name "*.csv" -type f -mtime -28 -exec grep "connection authorized" {
   
   } +|awk -F "," '{print $2,$3,$5}'|sed 's/\:[0-9]*//g'|sort|uniq -c|sort -n -r
"建议: "
"    连接请求非常多时, 请考虑应用层使用连接池, 或者使用pgbouncer连接池. "

2.3.3 获取认证失败情况

find . -name "*.csv" -type f -mtime -28 -exec grep "password authentication failed" {
   
   } +|awk -F "," '{print $2,$3,$5}'|sed 's/\:[0-9]*//g'|sort|uniq -c|sort -n -r
"建议: "
"    认证失败次数很多时, 可能是有用户在暴力破解, 建议使用auth_delay插件防止暴力破解. "

2.4 数据库慢SQL日志分析

2.4.1 慢查询统计

cat *.csv|awk -F "," '{print $1" "$2" "$3" "$8" "$14}' |grep "duration:"|grep -v "plan:"|awk '{print $1" "$4" "$5" "$6}'|sort|uniq -c|sort -rn

"建议: "
"    输出格式(条数,日期,用户,数据库,QUERY,耗时ms). "
"    慢查询反映执行时间超过log_min_duration_statement的SQL, 可以根据实际情况分析数据库或SQL语句是否有优化空间. "

2.4.2 慢查询分布10条的执行时间, ms

"--慢查询分布头10条的执行时间, ms: "
cat *.csv|awk -F "," '{print $1" "$2" "$3" "$8" "$14}' |grep "duration:"|grep -v "plan:"