PG_DBA_运维手册

jim 发布于 2026-02-08 收录于技術--PostgreSQL

PG_DBA_运维手册

查看database的owner

select datname, usename as owner from pg_database left join pg_user on usesysid = datdba;

查看可见SCHEMA

select * from information_schema.schemata;

查看表膨胀（对所有表进行膨胀率排序），取前10个

SELECT
    schemaname||'.'||relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
FROM
    pg_stat_all_tables
WHERE
    n_dead_tup >= 1000
and		schemaname in ('base','workspace','permission','process','form','pipeline')    
ORDER BY dead_tup_ratio DESC
LIMIT 10;

表膨胀处理

VACUUM (VERBOSE, ANALYZE)  表明，不锁表，不影响业务使用

VACUUM ( FULL,VERBOSE ) 表名称 ，注意会锁表，连查询都不可以

设置序列catche 1 (无缓存,每个进程取值就连续了)

select   'alter sequence '|| sequence_name|| ' cache 1 ;'
 from information_schema.sequences where sequence_schema != 'public'

查看性能sql

查看正在执行的sql,长事务（top sql）

select datname,
       usename,
       client_addr,
       application_name,
       state,
       backend_start,
       xact_start,
       xact_stay,
       query_start,
       query_stay,
	   now() - query_start AS Duration, 
       replace(query, chr(10), ' ') as query
from
  (select pgsa.datname as datname,
          pgsa.usename as usename,
          pgsa.client_addr client_addr,
          pgsa.application_name as application_name,
          pgsa.state as state,
          pgsa.backend_start as backend_start,
          pgsa.xact_start as xact_start,
          extract(epoch
                  from (now() - pgsa.xact_start)) as xact_stay,
          pgsa.query_start as query_start,
          extract(epoch
                  from (now() - pgsa.query_start)) as query_stay,
          pgsa.query as query
   from pg_stat_activity as pgsa
   where pgsa.state != 'idle'
     and pgsa.state != 'idle in transaction'
     and pgsa.state != 'idle in transaction (aborted)'
	 and usename='datalink'
	--and (now() - query_start) > interval '10 seconds'
	 ) idleconnections
order by query_stay desc
limit 5;

查看之前执行的sql

SELECT 
  procpid, 
  start, 
  now() - start AS lap, 
  current_query 
FROM 
  (SELECT 
    backendid, 
    pg_stat_get_backend_pid(S.backendid) AS procpid, 
    pg_stat_get_backend_activity_start(S.backendid) AS start, 
    pg_stat_get_backend_activity(S.backendid) AS current_query 
  FROM 
    (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
  ) AS S 
WHERE 
  current_query <> '<IDLE>' 
ORDER BY 
  lap DESC;

procpid：进程id , 强制结束 SELECT pg_cancel_backend(进程id) –取消后台操作; SELECT pg_terminate_backend(PID) –中断session; start：进程开始时间 lap：经过时间 current_query：执行中的sql