知新_RL

这个屌丝很懒，什么也没留下！

热门标签

进阶数据库系列（二十四）：PostgreSQL 数据库日志与日常巡检

作者：知新_RL | 2024-08-03 09:08:28

踩

postgres日志

点击下方名片，设为星标！

回复“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
fi


chmod +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

读者专属技术群

构建高质量的技术交流社群，欢迎从事后端开发、运维技术进群（备注岗位，已在技术交流群的请勿重复添加）。主要以技术交流、内推、行业探讨为主，请文明发言。广告人士勿入，切勿轻信私聊，防止被骗。

扫码加我好友，拉你进群

推荐阅读点击标题可跳转

卸载 Postman！事实证明，它更牛逼。。。

果真有这样的公司，够横！直接干上了热搜

C 盘真的一下子就不红了！微软官方工具就能解决

面试官：如何修改正运行的Docker容器端口映射？

突然，很多人被裁！知名公司遭曝光。。。

不雅信息发到工作群！回应：病毒入侵，已报警！

阿里再次大改革，江湖再无 P8 了。。。

PS：因为公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。点“在看”支持我们吧!

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/922340