点击下方名片，设为星标！
回复“1024”获取2TB学习资源！
前面介绍了 PostgreSQL 基于 pgpool 实现读写分离实践、数据库备份与恢复、主从数据目录同步工具 pg_rewind、数据库作业调度工具、性能优化等相关的知识点，今天我将详细的为大家介绍 PostgreSQL 数据库日志与日常巡检相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！
PostgreSQL 服务器日志
开启审计日志
编辑 $PGDATA/postgresql.conf文件
vi $PGDATA/postgresql.conf
# 做以下修改，下面2部分未提及的全部备注掉
# - Where to Log -
log_destination = 'csvlog' # 日志输出格式
logging_collector = on # 日志收集器，打开后某些不会出现在审计日志中的日志会被重定向到审计日志
log_directory = 'pg_log' # 相对于 $PGDATA 的相对路径，全路径即为 $PGDATA/pg_log
# 保留近7天的审计日志，轮询替换
log_filename = 'postgresql.%a' #日志名称
log_file_mode = 0600 # 只有postgres有读写权限
log_truncate_on_rotation = on # 覆盖同名日志
log_rotation_size = 0 # 不限制日志大小
log_min_messages = warning # 控制哪些消息级别被写入到审计日志
log_min_error_statement = error # 控制哪些导致一个错误情况的 SQL 语句被记录在服务器日志中
log_min_duration_statement = 0 # 记录所有sql运行时长，可以查慢sql
# - What to Log -
log_duration = on # 导致每一个完成的语句的持续时间被记录
log_lock_waits = on # 等锁超时记录日志，超时时间参数 deadlock_timeout
log_statement = 'mod' # mod记录所有ddl语句，外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM
log_replication_commands = off # 不记录流复制命令
log_timezone = 'Asia/Shanghai' # 时区，查看操作系统时区 timedatectl | grep "Time zone"重启数据库
pg_ctl restart -mf更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
sql直接读取日志
安装 file_fdw 插件
create extension file_fdw;创建外部表
drop foreign table if exists pg_log_mon;
create foreign table pg_log_mon(
log_time timestamp
,user_name text
,database_name text
,process_id integer
,connection_from text
,session_id text
,session_line_num bigint
,command_tag text
,session_start_time timestamp
,virtual_transaction_id text
,transaction_id bigint
,error_severity text
,sql_state_code text
,message text
,detail text
,hint text
,internal_query text
,internal_query_pos integer
,context text
,query text
,query_pos integer
,location text
,application_name text
) server pg_file_server options(
filename '/data/pgdata/pg_log/postgresql.Mon.csv'
,format 'csv'
,header 'false'
,delimiter ','
,quote '"'
,escape '"'
);
comment on foreign table pg_log_mon is '每周一当天审计日志';
comment on column pg_log_mon.log_time is '日志时间';
comment on column pg_log_mon.user_name is '用户名';
comment on column pg_log_mon.database_name is '数据库名';
comment on column pg_log_mon.process_id is '进程id';
comment on column pg_log_mon.connection_from is '客户端ip:端口';
comment on column pg_log_mon.session_id is '会话id';
comment on column pg_log_mon.session_line_num is '当前会话的第几次查询';
comment on column pg_log_mon.command_tag is '命令类型';
comment on column pg_log_mon.session_start_time is '会话开始时间';
comment on column pg_log_mon.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_mon.transaction_id is '事务id';
comment on column pg_log_mon.error_severity is '错误级别';
comment on column pg_log_mon.sql_state_code is 'sql状态代码';
comment on column pg_log_mon.message is '信息';
comment on column pg_log_mon.detail is '详细信息';
comment on column pg_log_mon.hint is '提示信息';
comment on column pg_log_mon.query is '查询的sql';
comment on column pg_log_mon.application_name is '应用名（客户端名）';
drop foreign table if exists pg_log_tue;
create foreign table pg_log_tue(
log_time timestamp
,user_name text
,database_name text
,process_id integer
,connection_from text
,session_id text
,session_line_num bigint
,command_tag text
,session_start_time timestamp
,virtual_transaction_id text
,transaction_id bigint
,error_severity text
,sql_state_code text
,message text
,detail text
,hint text
,internal_query text
,internal_query_pos integer
,context text
,query text
,query_pos integer
,location text
,application_name text
) server pg_file_server options(
filename '/data/pgdata/pg_log/postgresql.Tue.csv'
,format 'csv'
,header 'false'
,delimiter ','
,quote '"'
,escape '"'
);
comment on foreign table pg_log_tue is '每周二当天审计日志';
comment on column pg_log_tue.log_time is '日志时间';
comment on column pg_log_tue.user_name is '用户名';
comment on column pg_log_tue.database_name is '数据库名';
comment on column pg_log_tue.process_id is '进程id';
comment on column pg_log_tue.connection_from is '客户端ip:端口';
comment on column pg_log_tue.session_id is '会话id';
comment on column pg_log_tue.session_line_num is '当前会话的第几次查询';
comment on column pg_log_tue.command_tag is '命令类型';
comment on column pg_log_tue.session_start_time is '会话开始时间';
comment on column pg_log_tue.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_tue.transaction_id is '事务id';
comment on column pg_log_tue.error_severity is '错误级别';
comment on column pg_log_tue.sql_state_code is 'sql状态代码';
comment on column pg_log_tue.message is '信息';
comment on column pg_log_tue.detail is '详细信息';
comment on column pg_log_tue.hint is '提示信息';
comment on column pg_log_tue.query is '查询的sql';
comment on column pg_log_tue.application_name is '应用名（客户端名）';
drop foreign table if exists pg_log_wed;
create foreign table pg_log_wed(
log_time timestamp
,user_name text
,database_name text
,process_id integer
,connection_from text
,session_id text
,session_line_num bigint
,command_tag text
,session_start_time timestamp
,virtual_transaction_id text
,transaction_id bigint
,error_severity text
,sql_state_code text
,message text
,detail text
,hint text
,internal_query text
,internal_query_pos integer
,context text
,query text
,query_pos integer
,location text
,application_name text
) server pg_file_server options(
filename '/data/pgdata/pg_log/postgresql.Wed.csv'
,format 'csv'
,header 'false'
,delimiter ','
,quote '"'
,escape '"'
);
comment on foreign table pg_log_wed is '每周三当天审计日志';
comment on column pg_log_wed.log_time is '日志时间';
comment on column pg_log_wed.user_name is '用户名';
comment on column pg_log_wed.database_name is '数据库名';
comment on column pg_log_wed.process_id is '进程id';
comment on column pg_log_wed.connection_from is '客户端ip:端口';
comment on column pg_log_wed.session_id is '会话id';
comment on column pg_log_wed.session_line_num is '当前会话的第几次查询';
comment on column pg_log_wed.command_tag is '命令类型';
comment on column pg_log_wed.session_start_time is '会话开始时间';
comment on column pg_log_wed.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_wed.transaction_id is '事务id';
comment on column pg_log_wed.error_severity is '错误级别';
comment on column pg_log_wed.sql_state_code is 'sql状态代码';
comment on column pg_log_wed.message is '信息';
comment on column pg_log_wed.detail is '详细信息';
comment on column pg_log_wed.hint is '提示信息';
comment on column pg_log_wed.query is '查询的sql';
comment on column pg_log_wed.application_name is '应用名（客户端名）';
drop foreign table if exists pg_log_thu;
create foreign table pg_log_thu(
log_time timestamp
,user_name text
,database_name text
,process_id integer
,connection_from text
,session_id text
,session_line_num bigint
,command_tag text
,session_start_time timestamp
,virtual_transaction_id text
,transaction_id bigint
,error_severity text
,sql_state_code text
,message text
,detail text
,hint text
,internal_query text
,internal_query_pos integer
,context text
,query text
,query_pos integer
,location text
,application_name text
) server pg_file_server options(
filename '/data/pgdata/pg_log/postgresql.Thu.csv'
,format 'csv'
,header 'false'
,delimiter ','
,quote '"'
,escape '"'
);
comment on foreign table pg_log_thu is '每周四当天审计日志';
comment on column pg_log_thu.log_time is '日志时间';
comment on column pg_log_thu.user_name is '用户名';
comment on column pg_log_thu.database_name is '数据库名';
comment on column pg_log_thu.process_id is '进程id';
comment on column pg_log_thu.connection_from is '客户端ip:端口';
comment on column pg_log_thu.session_id is '会话id';
comment on column pg_log_thu.session_line_num is '当前会话的第几次查询';
comment on column pg_log_thu.command_tag is '命令类型';
comment on column pg_log_thu.session_start_time is '会话开始时间';
comment on column pg_log_thu.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_thu.transaction_id is '事务id';
comment on column pg_log_thu.error_severity is '错误级别';
comment on column pg_log_thu.sql_state_code is 'sql状态代码';
comment on column pg_log_thu.message is '信息';
comment on column pg_log_thu.detail is '详细信息';
comment on column pg_log_thu.hint is '提示信息';
comment on column pg_log_thu.query is '查询的sql';
comment on column pg_log_thu.application_name is '应用名（客户端名）';
drop foreign table if exists pg_log_fri;
create foreign table pg_log_fri(
log_time timestamp
,user_name text
,database_name text
,process_id integer
,connection_from text
,session_id text
,session_line_num bigint
,command_tag text
,session_start_time timestamp
,virtual_transaction_id text
,transaction_id bigint
,error_severity text
,sql_state_code text
,message text
,detail text
,hint text
,internal_query text
,internal_query_pos integer
,context text
,query text
,query_pos integer
,location text
,application_name text
) server pg_file_server options(
filename '/data/pgdata/pg_log/postgresql.Fri.csv'
,format 'csv'
,header 'false'
,delimiter ','
,quote '"'
,escape '"'
);
comment on foreign table pg_log_fri is '每周五当天审计日志';
comment on column pg_log_fri.log_time is '日志时间';
comment on column pg_log_fri.user_name is '用户名';
comment on column pg_log_fri.database_name is '数据库名';
comment on column pg_log_fri.process_id is '进程id';
comment on column pg_log_fri.connection_from is '客户端ip:端口';
comment on column pg_log_fri.session_id is '会话id';
comment on column pg_log_fri.session_line_num is '当前会话的第几次查询';
comment on column pg_log_fri.command_tag is '命令类型';
comment on column pg_log_fri.session_start_time is '会话开始时间';
comment on column pg_log_fri.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_fri.transaction_id is '事务id';
comment on column pg_log_fri.error_severity is '错误级别';
comment on column pg_log_fri.sql_state_code is 'sql状态代码';
comment on column pg_log_fri.message is '信息';
comment on column pg_log_fri.detail is '详细信息';
comment on column pg_log_fri.hint is '提示信息';
comment on column pg_log_fri.query is '查询的sql';
comment on column pg_log_fri.application_name is '应用名（客户端名）';
drop foreign table if exists pg_log_sat;
create foreign table pg_log_sat(
log_time timestamp
,user_name text
,database_name text
,process_id integer
,connection_from text
,session_id text
,session_line_num bigint
,command_tag text
,session_start_time timestamp
,virtual_transaction_id text
,transaction_id bigint
,error_severity text
,sql_state_code text
,message text
,detail text
,hint text
,internal_query text
,internal_query_pos integer
,context text
,query text
,query_pos integer
,location text
,application_name text
) server pg_file_server options(
filename '/data/pgdata/pg_log/postgresql.Sat.csv'
,format 'csv'
,header 'false'
,delimiter ','
,quote '"'
,escape '"'
);
comment on foreign table pg_log_sat is '每周六当天审计日志';
comment on column pg_log_sat.log_time is '日志时间';
comment on column pg_log_sat.user_name is '用户名';
comment on column pg_log_sat.database_name is '数据库名';
comment on column pg_log_sat.process_id is '进程id';
comment on column pg_log_sat.connection_from is '客户端ip:端口';
comment on column pg_log_sat.session_id is '会话id';
comment on column pg_log_sat.session_line_num is '当前会话的第几次查询';
comment on column pg_log_sat.command_tag is '命令类型';
comment on column pg_log_sat.session_start_time is '会话开始时间';
comment on column pg_log_sat.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_sat.transaction_id is '事务id';
comment on column pg_log_sat.error_severity is '错误级别';
comment on column pg_log_sat.sql_state_code is 'sql状态代码';
comment on column pg_log_sat.message is '信息';
comment on column pg_log_sat.detail is '详细信息';
comment on column pg_log_sat.hint is '提示信息';
comment on column pg_log_sat.query is '查询的sql';
comment on column pg_log_sat.application_name is '应用名（客户端名）';
drop foreign table if exists pg_log_sun;
create foreign table pg_log_sun(
log_time timestamp
,user_name text
,database_name text
,process_id integer
,connection_from text
,session_id text
,session_line_num bigint
,command_tag text
,session_start_time timestamp
,virtual_transaction_id text
,transaction_id bigint
,error_severity text
,sql_state_code text
,message text
,detail text
,hint text
,internal_query text
,internal_query_pos integer
,context text
,query text
,query_pos integer
,location text
,application_name text
) server pg_file_server options(
filename '/data/pgdata/pg_log/postgresql.Sun.csv'
,format 'csv'
,header 'false'
,delimiter ','
,quote '"'
,escape '"'
);
comment on foreign table pg_log_sun is '每周日当天审计日志';
comment on column pg_log_sun.log_time is '日志时间';
comment on column pg_log_sun.user_name is '用户名';
comment on column pg_log_sun.database_name is '数据库名';
comment on column pg_log_sun.process_id is '进程id';
comment on column pg_log_sun.connection_from is '客户端ip:端口';
comment on column pg_log_sun.session_id is '会话id';
comment on column pg_log_sun.session_line_num is '当前会话的第几次查询';
comment on column pg_log_sun.command_tag is '命令类型';
comment on column pg_log_sun.session_start_time is '会话开始时间';
comment on column pg_log_sun.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_sun.transaction_id is '事务id';
comment on column pg_log_sun.error_severity is '错误级别';
comment on column pg_log_sun.sql_state_code is 'sql状态代码';
comment on column pg_log_sun.message is '信息';
comment on column pg_log_sun.detail is '详细信息';
comment on column pg_log_sun.hint is '提示信息';
comment on column pg_log_sun.query is '查询的sql';
comment on column pg_log_sun.application_name is '应用名（客户端名）';
create or replace view pg_log as
select * from pg_log_mon
union all
select * from pg_log_tue
union all
select * from pg_log_wed
union all
select * from pg_log_thu
union all
select * from pg_log_fri
union all
select * from pg_log_sat
union all
select * from pg_log_sun;更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
备份策略
每天00:30备份前一天的审计日志
shell脚本实现备份
#! /bin/bash
#备份日期默认昨天，也是备份日期上限
BAKUP_DATE_UPPER=`date '+%Y%m%d' -d '-1 day'`
BAKUP_DATE_FLOOR=`date '+%Y%m%d' -d '-6 day'`
BAKUP_DATE=${BAKUP_DATE_UPPER}
showuseage() {
echo "程序功能:在数据库中备份昨天的pg审计日志
Useage: [backup_pglog -h 20220830]
-h [:可选，指定备份日期，可选范围${BAKUP_DATE_FLOOR}-${BAKUP_DATE_UPPER}，其他默认昨天]"
}
# /home/postgres/backup_pglog.sh
# 每天备份昨天的数据
while getopts h: OPTS; do
case "$OPTS" in
h)
if [ $OPTARG -ge ${BAKUP_DATE_FLOOR} -a $OPTARG -le ${BAKUP_DATE_UPPER} ]; then
BAKUP_DATE=$OPTARG
fi
;;
:)
echo "$0 必须为 -$OPTARG 添加一个参数!"
exit 1
;;
?)
showuseage
exit 1
;;
esac
done
BAKUP_SQL="
create table if not exists pg_log_:bak_log_span as
select
:today::varchar(8) as bak_date
,*
from pg_log
where 1 = 2
;
delete from pg_log_:bak_log_span where to_char(log_time,'yyyymmdd')::numeric = ${BAKUP_DATE}
;
insert into pg_log_:bak_log_span
select :today::varchar as bak_date ,*
from pg_log
where to_char(log_time,'yyyymmdd')::numeric = ${BAKUP_DATE}
;
"
echo `date '+%Y-%m-%d %H:%m:%S'`"|INFO|备份数据日期：${BAKUP_DATE}"
echo `date '+%Y-%m-%d %H:%m:%S'`"|INFO|审计日志备份开始"
BAKUP_SQL_EXEC_DATE=`date '+%Y%m%d%H%m%S'`
psql <<EOF > /tmp/bakup_sql_exec_${BAKUP_SQL_EXEC_DATE}.log 2>&1
\i ~/var/${BAKUP_DATE}.sql
${BAKUP_SQL}
\q
EOF
EXECLOG=`cat /tmp/bakup_sql_exec_${BAKUP_SQL_EXEC_DATE}.log`
rm -f /tmp/bakup_sql_exec_${BAKUP_SQL_EXEC_DATE}.log
echo `date '+%Y-%m-%d %H:%m:%S'`"|INFO|审计日志备份结束"
# 错误
ERRORNUM=`echo "${EXECLOG}" | grep -i 'ERROR' | wc -l`
if [ ${ERRORNUM} -eq 0 ]
then
# 发短信通知备份成功
echo `date '+%Y-%m-%d %H:%m:%S'`"|INFO|审计日志备份成功"
exit 0
else
# 发短信通知备份失败
echo "请及时重新执行备份脚本进行备份" | mail -s "审计日志备份失败" 1445471277@qq.com
echo `date '+%Y-%m-%d %H:%m:%S'`"|ERROR|审计日志备份失败"
exit 1
fichmod +x /home/postgres/backup_pglog.sh
crontab -e
30 0 * * * . /home/postgres/backup_pglog.sh >> /home/postgres/backup_pglog.log 2>&1格式符说明
%a #星期的英文单词的缩写：如星期一， 则返回 Mon
%A #星期的英文单词的全拼：如星期一，返回 Monday
%b #月份的英文单词的缩写：如一月， 则返回 Jan
%B #月份的引文单词的缩写：如一月， 则返回 January
%c #返回datetime的字符串表示，如03/08/15 23:01:26
%d #返回的是当前时间是当前月的第几天
%f #微秒的表示： 范围: [0,999999]
%H #以24小时制表示当前小时
%I #以12小时制表示当前小时
%j #返回 当天是当年的第几天 范围[001,366]
%m #返回月份 范围[0,12]
%M #返回分钟数 范围 [0,59]
%P #返回是上午还是下午–AM or PM
%S #返回秒数 范围 [0,61]。。。手册说明的
%U #返回当周是当年的第几周 以周日为第一天
%W #返回当周是当年的第几周 以周一为第一天
%w #当天在当周的天数，范围为[0, 6]，6表示星期天
%x #日期的字符串表示 ：03/08/15
%X #时间的字符串表示 ：23:22:08
%y #两个数字表示的年份 15
%Y #四个数字表示的年份 2015
%z #与utc时间的间隔 （如果是本地时间，返回空字符串）
%Z #时区名称（如果是本地时间，返回空字符串）消息严重级别
更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
Postgresql 数据库巡检
主机信息
CPU
mpstat | sed -n '3,$p' | awk -F' ' '{print $13}'
echo 'CPU CORE' && cat /proc/cpuinfo|grep processor|wc -l正常：空闲cpu大于20%；
异常处理：排查问题，杀掉cpu高进程，top 按c；
检查内存
free -m正常：空闲内存大于30%；
异常处理：排查问题，杀掉内存高进程，top 按c；
检查磁盘空间
df -lh正常：磁盘空间已用空间小于70%；
异常处理：增加硬盘或者删除无用的数据；
检查IO
iostat -x | sed -n '6,$p' | awk -F' ' '{print $1,$13,$14}'正常：磁盘空间已用空间小于70%；
异常处理：增加硬盘或者删除无用的数据；
检查端口
netstat -tanp | grep 'LISTEN' | grep '5432'正常：tcp4和tcp6正常监听；
异常处理：排查数据库是否正常启动，排查数据库配置文件的端口参数是否为5432；
检查postgres进程
ps -ef | grep "checkpointer|background writer|walwriter|autovacuum launcher|archiver|stats collector|logical replication launcher|logger" | grep -v grep正常：进程都在；
异常处理：重启数据库；
数据库
检查安装信息
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,to_char(pg_postmaster_start_time(),'yyyy-mm-dd hh24:mi:ss') "pg_start_time(启动时间)"
,now()-pg_postmaster_start_time() "pg_running_time(运行时长)"
--,inet_server_addr() "server_ip(服务器ip)"
--,inet_server_port() "server_port(服务器端口)"
--,inet_client_addr() "client_ip(客户端ip)"
--,inet_client_port() "client_port(客户端端口)"
,version() "server_version(数据库版本)"
,(case when pg_is_in_recovery()='f' then 'primary' else 'standby' end ) as "primary_or_standby(主或备)"
;正常：数据库正常使用；
异常处理：重装数据库；
检查postgresql.conf文件
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,sourceline "sourceline(行号)"
,name "para(参数名)"
,setting "value(参数值)"
from pg_file_settings
order by "sourceline(行号)";正常：各项参数设置适合；
异常处理：编辑postgresql.conf文件，修改参数后重启数据库；
vi $PGDATA/postgresql.conf
pg_ctl restart -mf检查pg_hba.conf文件
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,line_number "line_number(行号)"
,type "type(连接类型)"
,database "database(数据库名)"
,user_name "user_name(用户名)"
,address "address(ip地址)"
,netmask "netmask(子网掩码)"
,auth_method "auth_method(认证方式)"
from pg_hba_file_rules
order by "line_number(行号)";正常：非套接字连接都需要md5认证；
异常处理：编辑pg_hba.conf文件，修改参数后重新加载数据库；
vi $PGDATA/postgresql.conf
pg_ctl reload检查数据库重要配置
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,name
,setting
from
pg_settings a
where a.name in (
'data_directory',
'port',
'client_encoding',
'config_file',
'hba_file',
'ident_file',
'archive_mode',
'logging_collector',
'log_directory',
'log_filename',
'log_truncate_on_rotation',
'log_statement',
'log_min_duration_statement',
'max_connections',
'listen_addresses'
)
order by name;正常：各项配置都适合；
异常处理：修改不合适的配置；
检查主从WAl状态
主
select
-- pid "pid(进程id)"
--,usename "username(用户名)"
--,application_name "application_name(应用名)"
--,client_addr "client_addr(IP)"
--,backend_start "backend_start(备份开始时间)"
state "state(WAL发送状态编码)"
-- ,case
-- when state = 'startup' then '正在启动'
-- when state = 'catchup' then '追赶主库'
-- when state = 'streaming' then '流传送'
-- when state = 'backup' then '发送备份'
-- when state = 'stopping' then '发送停止'
-- end "statename(WAL状态)"
,sync_state "sync_state(同步状态编码)"
-- ,case
-- when sync_state = 'async' then '异步'
-- when sync_state = 'potential' then '后备失效变同步'
-- when sync_state = 'sync' then '同步'
-- when sync_state = 'quorum' then '候选'
-- end "sync_statename(同步状态名称)"
--,round(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) /(1024 * 1024),2) as "slave_latency_mb(同步延迟_MB)"
from pg_stat_replication;从
select
-- pid "pid(进程id)"
status "status(WAl接收状态)"
,'async' "sync_state(同步状态编码)"
--,last_msg_send_time "last_msg_send_time(接收到最后的消息发送时间)"
--,last_msg_receipt_time "last_msg_receipt_time(接收到最后的消息接收时间)"
--,sender_host "sender_host(主库IP)"
from pg_stat_wal_receiver
;更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
检查表空间
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,spcname AS "Name(名称)"
,pg_catalog.pg_get_userbyid(spcowner) AS "Owner(拥有者)"
--,pg_catalog.pg_tablespace_location(oid) AS "Location(数据文件目录)"
--,pg_catalog.array_to_string(spcacl, E'\n') AS "Access privileges(访问权限)"
--,spcoptions AS "Options(参数)"
,pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "Size(表空间大小)"
--,pg_catalog.shobj_description(oid, 'pg_tablespace') AS "Description(备注)"
from pg_catalog.pg_tablespace
order by 1;检查连接数
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,max_conn "max_conn(最大连接数)"
,now_conn "now_conn(当前连接数)"
,max_conn - now_conn "remain_conn(剩余连接数)"
from (
select
setting::int8 as max_conn
,(select count(*) from pg_stat_activity ) as now_conn
from pg_settings
where name = 'max_connections'
) a
;正常：连接数不超过总连接数的90%；
异常处理：超级用户（postgres）杀连接；
--杀掉所有空闲连接
select pg_terminate_backend(pid) from pg_stat_activity WHERE state = 'idle';检查锁表
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,relname "relname(表名)"
,b.nspname "shemaname(模式名)"
,c.rolname "user(用户名)"
,d.locktype "locktype(被锁对象类型)"
,d.mode "mode(锁类型)"
,d.pid "pid(进程id)"
,e.query "query(锁表sql)"
,current_timestamp-state_change "lock_duration(锁表时长)"
from pg_class a
inner join pg_namespace b
on (a.relnamespace = b.oid)
inner join pg_roles c
on (a.relowner = c.oid)
inner join pg_locks d
on (a.oid = d.relation)
left join pg_stat_activity e
on (d.pid = e.pid)
where d.mode = 'AccessExclusiveLock'
order by "lock_duration(锁表时长)" desc;正常：无锁表；
异常处理：取消该进程或杀掉该会话；
--取消该进程
select pg_cancel_backend(pid);
--杀掉该会话
select pg_terminate_backend(pid);检查空闲连接top5
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,a.datname "datname(数据库名)"
,a.pid "pid(进程id)"
,b.rolname "username(用户名)"
--,a.application_name "app_name(应用名称)"
,a.client_addr "client_ip(客户端ip)"
--,a.query_start "query_start(当前查询开始时间)"
,to_char(a.state_change,'yyyy-mm-dd hh24:mi:ss') "state_change(状态变化时间)"
--,a.state "state(状态)"
--,a.query "sql(执行的sql)"
--,a.backend_type "backend_type(后端类型)"
from pg_stat_activity a
inner join pg_roles b
on (a.usesysid = b.oid)
where a.state = 'idle'
and state_change < current_timestamp - interval '30 min'
order by current_timestamp-state_change desc
limit 5
;正常：超半小时空闲的连接；
异常处理：杀连接；
select pg_terminate_backend(pid);检查长事务top5
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,a.datname "datname(数据库名)"
,a.pid "pid(进程id)"
,b.rolname "username(用户名)"
--,a.application_name "app_name(应用名称)"
,a.client_addr "client_ip(客户端ip)"
--,a.xact_start "xact_start(当前事务开始时间)"
--,a.query_start "query_start(当前查询开始时间)"
,to_char(a.state_change,'yyyy-mm-dd hh24:mi:ss') "state_change(状态变化时间)"
--,a.state "state(状态)"
--,a.query "sql(执行的sql)"
--,a.backend_type "backend_type(后端类型)"
from pg_stat_activity a
inner join pg_roles b
on (a.usesysid = b.oid)
where a.state in ('idle in transaction','idle in transaction (aborted)')
and state_change < current_timestamp - interval '30 min'
order by current_timestamp-state_change desc
limit 5;正常：不存在长事务；
异常处理：杀会话；
select pg_terminate_backend(pid);检查慢SQLtop5
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,a.datname "datname(数据库名)"
,a.pid "pid(进程id)"
,b.rolname "username(用户名)"
--,a.application_name "app_name(应用名称)"
,a.client_addr "client_ip(客户端ip)"
--,a.query_start "query_start(当前查询开始时间)"
,to_char(a.state_change,'yyyy-mm-dd hh24:mi:ss') "state_change(状态变化时间)"
--,a.wait_event_type "wait_event_type(等待类型)"
--,a.wait_event "wait_event(等待事件)"
--,a.state "state(状态)"
--,a.query "sql(执行的sql)"
--,a.backend_type "backend_type(后端类型)"
from pg_stat_activity a
left join pg_roles b
on (a.usesysid = b.oid)
where a.state = 'active'
and state_change < current_timestamp - interval '1 hour'
and a.datname is not null
order by current_timestamp-state_change desc
limit 5;正常：不存在慢sql；
异常处理：分析原因，有针对性地杀连接；
select pg_terminate_backend(pid);检查对象数
--这里需要循环查每个库所有数据然后合并
select to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,current_database()
,sum(obj_num) "obj_num(对象数)"
from (
select count(1) obj_num from pg_class
union all
select count(1) from pg_proc
) a
;正常：总对象数不超过5万；
异常处理：删除无用的对象；
检查表膨胀top5
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,current_database() current_database
,relname as "table_name(表名)"
,schemaname as "schema_name(模式名)"
,pg_size_pretty(pg_relation_size('"'||schemaname|| '"."'||relname||'"')) as "table_size(表大小)"
,n_dead_tup as "n_dead_tup(无效记录数)"
,n_live_tup as "n_live_tup(有效记录数)"
,to_char(round(n_dead_tup*1.0/(n_live_tup+n_dead_tup)*100,2),'fm990.00') as "dead_rate(无效记录比例%)"
from
pg_stat_all_tables
where n_live_tup+n_dead_tup <> 0
;正常：不存在表膨胀，因为有自动清理垃圾进程；
异常处理：对膨胀表做vacuum analyze操作；
检查索引膨胀
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间",
current_database() AS db, schemaname, tablename,bs, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::bigint || $$ bytes$$ END AS wastedisize,
CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
END AS totalwastedbytes
FROM (
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(bs,0) AS bs,
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM
pg_class cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
LEFT JOIN
(
SELECT
ma,bs,foo.nspname,foo.relname,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname, hdr, ma, bs,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,
(
SELECT
(SELECT current_setting($$block_size$$)::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
WHERE att.attnum > 0 AND tbl.relkind=$$r$$
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
;索引膨胀，依赖于统计信息，统计信息未更新，索引膨胀信息不准确。一般每年统一做一次重建索引即可。
异常处理：重建索引；
reindex index 索引名;更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
参考文章：https://blog.csdn.net/qq_33445829/article/
details/126578647 https://blog.csdn.net/qq_33445829
/article/details/126638945
读者专属技术群
构建高质量的技术交流社群，欢迎从事后端开发、运维技术进群（备注岗位，已在技术交流群的请勿重复添加）。主要以技术交流、内推、行业探讨为主，请文明发言。广告人士勿入，切勿轻信私聊，防止被骗。
扫码加我好友，拉你进群
推荐阅读 点击标题可跳转
PS：因为公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。点“在看”支持我们吧!
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
