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')orderby 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
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
orderby1;
检查连接数
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
,(selectcount(*)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
innerjoin pg_namespace b
on(a.relnamespace = b.oid)innerjoin pg_roles c
on(a.relowner = c.oid)innerjoin pg_locks d
on(a.oid = d.relation)leftjoin pg_stat_activity e
on(d.pid = e.pid)where d.mode='AccessExclusiveLock'orderby"lock_duration(锁表时长)"desc;