postgresql数据库一些检查命令

mahan

检查安装信息
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(行号)";

检查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(行号)";

检查数据库重要配置
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
     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
     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;

检查空闲连接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
;

检查长事务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;

检查慢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;

检查对象数
--这里需要循环查每个库所有数据然后合并
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
;

检查表膨胀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
;