PostgreSQL 日常巡检&问题排查文档

yum install -y sysstat
# Ubuntu
apt install -y sysstat

# 空闲CPU占比
mpstat | sed -n '3,$p' | awk -F' ' '{print $13}'
# 用户CPU占用
mpstat | sed -n '3,$p' | awk -F' ' '{print $12}'
# CPU核心总数
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(运行时长)"
    ,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;

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;

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
    state "state(WAL发送状态编码)"
    ,sync_state "sync_state(同步状态编码)"
    ,round(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) /(1024 * 1024),2) as "slave_latency_mb(同步延迟_MB)"
from pg_stat_replication;

select
     status "status(WAl接收状态)"
    ,'async' "sync_state(同步状态编码)"
    ,sender_host "sender_host(主库IP)"
from pg_stat_wal_receiver;

SELECT

    to_char(now(),'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",

    spcname AS "Name(表空间名称)",

    pg_catalog.pg_get_userbyid(spcowner) AS "Owner(拥有者)",

    pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "Size(表空间大小)"

FROM pg_catalog.pg_tablespace

ORDER BY pg_catalog.pg_tablespace_size(oid) DESC;

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
    datname AS "数据库名",
    COUNT(*) AS "连接数"
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY datname
ORDER BY COUNT(*) DESC;

SELECT usename, COUNT(*) AS connection_count  
FROM pg_stat_activity  
GROUP BY usename;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'test_db'
  AND state = 'idle'
  AND pid <> pg_backend_pid();

DO
$$
DECLARE  
    r RECORD;  
BEGIN  
    FOR r IN (  
        SELECT pid  
        FROM pg_stat_activity  
        WHERE state = 'idle'  
          AND now() - state_change > INTERVAL '30 minutes'  
          AND pid <> pg_backend_pid()  
    ) LOOP  
        PERFORM pg_terminate_backend(r.pid);  
    END LOOP;  
END
$$;

SELECT
    to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
    a.relname AS "表名",
    b.nspname AS "模式名",
    c.rolname AS "用户名",
    d.locktype AS "锁对象类型",
    d.mode AS "锁模式",
    d.pid AS "进程PID",
    e.query AS "阻塞SQL",
    age(now(), e.query_start) AS "锁持有时长"
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 IN ('AccessExclusiveLock', 'RowExclusiveLock')
    AND e.pid <> pg_backend_pid()
ORDER BY "锁持有时长" DESC;

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN (
        SELECT d.pid AS pid
        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 IN ('AccessExclusiveLock', 'RowExclusiveLock')
    ) LOOP
        PERFORM pg_terminate_backend(rec.pid);
    END LOOP;
END $$;

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN (
        SELECT d.pid AS pid, a.relname, b.nspname, c.rolname, e.query, age(now(), e.query_start) 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 IN ('AccessExclusiveLock', 'RowExclusiveLock')
        AND e.datname = 'test_db'
    ) LOOP
        RAISE NOTICE '终止PID:% 表%.% 锁时长:%', rec.pid, rec.nspname, rec.relname, rec.lock_duration;
        PERFORM pg_terminate_backend(rec.pid);
    END LOOP;
END $$;

SELECT
    TO_CHAR(NOW(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",
    a.datname AS "数据库名",
    a.pid AS "进程PID",
    b.rolname AS "用户名",
    a.client_addr AS "客户端IP",
    TO_CHAR(a.state_change, 'yyyy-mm-dd hh24:mi:ss') AS "空闲起始时间",
    a.query AS "最后执行SQL"
FROM pg_stat_activity a
INNER JOIN pg_roles b ON a.usesysid = b.oid
WHERE a.state = 'idle'
AND a.state_change < CURRENT_TIMESTAMP - INTERVAL '5 min'
ORDER BY current_timestamp-state_change DESC
LIMIT 5;

SELECT   
    to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",  
    a.datname AS "数据库名",  
    a.pid AS "进程PID",  
    b.rolname AS "用户名",  
    a.client_addr AS "客户端IP",  
    to_char(a.state_change, 'yyyy-mm-dd hh24:mi:ss') AS "事务空闲起始时间",  
    a.query AS "未提交事务SQL"
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 a.state_change < current_timestamp - INTERVAL '5 min'  
ORDER BY   
    current_timestamp - a.state_change DESC  
LIMIT 5;

SELECT   
    to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",  
    a.datname AS "数据库名",  
    a.pid AS "进程PID",  
    b.rolname AS "用户名",  
    a.client_addr AS "客户端IP",  
    a.wait_event_type AS "等待类型",  
    a.wait_event AS "等待事件",  
    SUBSTRING(a.query,1,200) AS "执行SQL"
FROM   
    pg_stat_activity a  
LEFT JOIN   
    pg_roles b ON (a.usesysid = b.oid)  
WHERE   
    a.state = 'active'  
AND   
    a.state_change < current_timestamp - INTERVAL '5 min'  
AND   
    a.datname IS NOT NULL  
ORDER BY   
    a.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
;

SELECT

    to_char(now(), 'yyyy-mm-dd hh24:mi:ss') AS "巡检时间",

    current_database() AS current_database,

    relname AS "table_name(表名)",

    schemaname AS "schema_name(模式名)",

    pg_size_pretty(pg_relation_size((schemaname || '.' || relname)::regclass)) 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 / NULLIF(n_live_tup + n_dead_tup, 0)) * 100,

            2

        ),

        'fm990.00'

    ) AS "dead_rate(无效记录比例%)"

FROM

    pg_stat_all_tables

WHERE

    (n_live_tup + n_dead_tup) <> 0

    AND schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')

ORDER BY

    (n_dead_tup * 1.0 / NULLIF(n_live_tup + n_dead_tup, 0)) DESC;

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
  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
order by wastedbytes desc limit 20;

REINDEX INDEX CONCURRENTLY 索引名;
REINDEX TABLE CONCURRENTLY 表名;

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database;

select pg_size_pretty(pg_total_relation_size('public.表名'));

SELECT

    format('%I.%I', table_schema, table_name) AS table_name,

    pg_size_pretty(pg_table_size((format('%I.%I', table_schema, table_name))::regclass)) AS table_size,

    pg_size_pretty(pg_indexes_size((format('%I.%I', table_schema, table_name))::regclass)) AS indexes_size,

    pg_size_pretty(pg_total_relation_size((format('%I.%I', table_schema, table_name))::regclass)) AS total_size

FROM information_schema.tables

WHERE table_schema NOT IN ('pg_catalog', 'information_schema')

  AND table_type = 'BASE TABLE'  -- 仅普通表

ORDER BY pg_total_relation_size((format('%I.%I', table_schema, table_name))::regclass) DESC;

SELECT p.pid,
p.datname,
p.query,
p.backend_type,
a.phase,
a.heap_blks_scanned / a.heap_blks_total::float * 100 AS "% scanned",
a.heap_blks_vacuumed / a.heap_blks_total::float * 100 AS "% vacuumed",
pg_size_pretty(pg_table_size(a.relid)) AS "table size",
pg_get_userbyid(c.relowner) AS owner
FROM pg_stat_activity p
JOIN pg_stat_progress_vacuum a ON a.pid = p.pid
JOIN pg_class c ON c.oid = a.relid
WHERE p.query LIKE 'autovacuum%';

SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN ('r','t')
        AND N.nspname NOT IN ('pg_catalog','information_schema')
        AND N.nspname !~ '^pg_toast'
      ) AS av
ORDER BY av_needed DESC ,n_dead_tup DESC;

select w1.pid as 等待进程,
w1.mode as 等待锁模式,
w2.usename as 等待用户,
w2.query as 等待会话SQL,
b1.pid as 持有锁进程,
b1.mode 持有锁模式,
b2.usename as 持有锁用户,
b2.query as 持有锁SQL,
b2.application_name 应用名称,
b2.client_addr 客户端IP
from pg_locks w1
join pg_stat_activity w2 on w1.pid=w2.pid
join pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pid
join pg_stat_activity b2 on b1.pid=b2.pid
where not w1.granted;

SELECT
    r.rolname AS username,
    COALESCE(d.description, '无备注') AS comment,
    r.rolvaliduntil AS password_expire_time,
    r.rolsuper AS is_superuser,
    r.rolcanlogin AS can_login,
    r.rolreplication AS can_replicate,
    r.rolconnlimit AS max_conn_limit
FROM pg_roles r
LEFT JOIN pg_shdescription d ON d.objoid = r.oid
WHERE r.rolcanlogin = true
ORDER BY r.rolname;

SELECT * FROM pg_ls_tmpdir();

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_xmin,
    age(backend_xmin) AS xmin_age,
    now() - xact_start AS xact_duration,
    state,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY age(backend_xmin) DESC;

ALTER DATABASE test_db 
SET idle_in_transaction_session_timeout = '3min';
ALTER DATABASE test_db 
SET statement_timeout = '10min';
SELECT pg_reload_conf();