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
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;
备份策略
每天00:30备份前一天的审计日志
#! /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 数据库巡检
mpstat | sed -n '3,$p' | awk -F' ' '{print $13}'
echo 'CPU CORE' && cat /proc/cpuinfo|grep processor|wc -l
free -m
df -lh
iostat -x | sed -n '6,$p' | awk -F' ' '{print $1,$13,$14}'
netstat -tanp | grep 'LISTEN' | grep '5432'
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(主或备)"
;
select
to_char(now(),'yyyy-mm-dd hh24:mi:ss') "巡检时间"
,sourceline "sourceline(行号)"
,name "para(参数名)"
,setting "value(参数值)"
from pg_file_settings
order by "sourceline(行号)";
vi $PGDATA/postgresql.conf
pg_ctl restart -mf
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(行号)";
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;
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
;
检查表空间
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
;
--杀掉所有空闲连接
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);
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);
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);
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;
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
;
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
;
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 索引名;
参考文章:https://blog.csdn.net/qq_33445829/article/ details/126578647 https://blog.csdn.net/qq_33445829 /article/details/126638945