Postgres 数据库日常维护SQL

1、查数据库的活动连接
select datname,procpid,query_start,current_query,waiting,client_addr from pg_stat_activity order by datname;

--查看所有系统视图
select relkind,relname from pg_class where relnamespace = (select oid from pg_namespace where nspname='pg_catalog') and relkind='v' order by 1,2

2、查看数据库正在执行的超过10秒的语句
select distinct 'SELECT pg_cancel_backend(' || pid || ');',query,waiting,ageee
from (select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
pg_locks.granted as "g",pg_stat_activity.query, pg_stat_activity.waiting,pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "ageee",
pg_stat_activity.pid from pg_stat_activity,pg_locks
left outer join pg_class on
(pg_locks.relation = pg_class.oid)  
where pg_locks.pid=pg_stat_activity.pid and query not like 'auto%' ) a
where a.ageee>'00:00:10' order by query;

--查询正在执行的sql
SELECT
    procpid,
    'SELECT pg_cancel_backend(' || 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;

3、查看锁定对象和语句
select pg_class.relname, pg_locks.transactionid, pg_locks.mode, pg_locks.granted as "g",
pg_stat_activity.query, pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.pid, 'SELECT pg_cancel_backend('||pg_stat_activity.pid||');'
from pg_stat_activity,pg_locks
left outer join pg_class on
(pg_locks.relation = pg_class.oid)  
where pg_locks.pid=pg_stat_activity.pid and age(now(),pg_stat_activity.query_start)>'1 minute'::interval

4、查看数据库表结构
select
(select relname||'--'||(select description from pg_description where objoid=oid and objsubid=0) as comment
from pg_class where oid=a.attrelid) as table_name,
a.attname as column_name,
format_type(a.atttypid,a.atttypmod) as data_type,
(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,
(case when a.attnotnull=true then 'Y' else 'N' end) as nullable,
col_description(a.attrelid,a.attnum) as comment
from pg_attribute a
where attstattarget=-1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind ='r' --and relname like 'exg_%'
))
order by table_name,a.attnum;

========数据库日常维护=========
1、日常清理（VACUUM;）因为有大量的更新（update）"删除(delete)操作，会有大量的空间需要释放
   每日执行一次VACUUM，每月访问量低的时候执行VACUUM FULL

2、重建索引，重建之后需要分析数据表
REINDEX INDEX testtable_idx;
ANALYZE testtable;
查询用户链接限制

SELECT rolconnlimit FROM pg_roles WHERE rolname = 'postgres';
查看用户当前连接数

SELECT count(*) FROM pg_stat_activity WHERE usename = 'postgres';

select * from pg_stat_activity where DATNAME = 'template1';

--查询当前执行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 like '%t_crm_market_activity%'      
   and current_query <> '<IDLE>'
 ORDER BY lap DESC;

----------------------------------------------------------查看shared_buffers情况统计-----------------------------------------------------
------创建-------------------
 create extension pg_buffercache ;
 
show shared_buffers
select name,unit as 单元块大小,setting as 总数,current_setting(name) from pg_settings where name = 'shared_buffers' ;
----------- 备注：主要字段 relfilenode,isdirty,usagecount,
-----------relfilenode:是指表的文件id,与 pg_class.relfilenode 关联;
-----------isdirty:标记 shared_buffers 里的块是否为脏，如果被修改的块还未被刷新到硬盘里，则标记为 t, 否则，标记为 f;
-----------usagecount:指 shared_buffers 里的块被使用的次数。
select pg_size_pretty(count(*) * 8192) as buffered from pg_buffercache limit 11 ;     
------------统计信息--说明：usagecount都是4或者5,那么这表明你的shared buffer不够多，usagecount是0或者1,可以尝试减少你的shared buffer--------
select usagecount,count(*),isdirty
    from pg_buffercache
   group by isdirty, usagecount
   order by isdirty, usagecount ;

--查看表占用空间大小
SELECT
table_schema || '.' || table_name
AS table_full_name, pg_size_pretty(pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size
FROM
information_schema.tables
where table_schema = 'public'
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC limit 20
   
-----查看BUFFERED大小
 SELECT c.relname,pg_size_pretty(count(*) * 8192) as buffered,
round(100.0 * count(*) /
(SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent,
round(100.0 * count(*) * 8192 /pg_relation_size(c.oid),1) AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 30;
-------------------统计buffer命中率----------------------
select blks_read,blks_hit,
       blks_hit::numeric/(blks_read + blks_hit ) as ratio
 from  pg_stat_database
 where datname = 'STBSP' ;

 
-----------------分别统计table，inx等各个部分的命中率。---------------------------
  SELECT (sum(heap_blks_hit) + sum(heap_blks_read)) AS TABLE_CNT,
             round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 ,3)as TABLE_RATIO,
             (sum(idx_blks_hit) + sum(idx_blks_read)) AS INDEX_CNT,
             round(sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read)) * 100,3) as INDEX_RATIO,
             (sum(toast_blks_hit) + sum(toast_blks_read)) AS TOAST_CNT,
             round(sum(toast_blks_hit) / (sum(toast_blks_hit) + sum(toast_blks_read)) * 100,3) as TOAST_RATIO,
             (sum(tidx_blks_hit) + sum(tidx_blks_read)) AS TOASIDX_CNT ,
           round(sum(tidx_blks_hit) / (sum(tidx_blks_hit) + sum(tidx_blks_read))*100,3) as TOASTIDX_RATIO
 FROM pg_statio_user_tables ;

----------各个relation各自的命中率情况--------------
 SELECT relname, heap_blks_read, heap_blks_hit,
         round(heap_blks_hit::numeric/(heap_blks_hit + heap_blks_read), 3)
  FROM   pg_statio_user_tables
  WHERE  heap_blks_read > 0
  ORDER  BY 4 DESC ;

 ----------各个idx各自的命中率情况--------------
  SELECT relname, idx_blks_read, idx_blks_hit,
         round(idx_blks_hit::numeric/(idx_blks_hit + idx_blks_read), 3)
  FROM   pg_statio_user_tables
  WHERE  idx_blks_read > 0
  ORDER  BY 4 DESC ;

----------------------------------------------------------查看数据库进程情况统计-----------------------------------------------------
-------------------PG10.4---------------------------------------
select distinct 'SELECT pg_cancel_backend(' || pid || ');',query,wait_event,state,ageee
from (select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
pg_locks.granted as "g",pg_stat_activity.query, pg_stat_activity.wait_event,pg_stat_activity.query_start,
pg_stat_activity.state,
age(now(),pg_stat_activity.query_start) as "ageee",
pg_stat_activity.pid from pg_stat_activity,pg_locks
left outer join pg_class on
(pg_locks.relation = pg_class.oid)  
where pg_locks.pid=pg_stat_activity.pid ) a
order by query;

-------------------PG9.1---------------------------------------
select distinct 'SELECT pg_cancel_backend(' || pid || ');',query,waiting,ageee
from (select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
pg_locks.granted as "g",pg_stat_activity.query, pg_stat_activity.waiting,pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "ageee",
pg_stat_activity.pid from pg_stat_activity,pg_locks
left outer join pg_class on
(pg_locks.relation = pg_class.oid)  
where pg_locks.pid=pg_stat_activity.pid and query not like 'auto%' ) a
where a.ageee>'00:00:10' order by query;

-----------------------PG9.1以下版本-----------------
select distinct 'SELECT pg_cancel_backend(' || procpid || ');',current_query,waiting,ageee
from (select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
pg_locks.granted as "g",pg_stat_activity.current_query, pg_stat_activity.waiting,pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "ageee",
pg_stat_activity.procpid from pg_stat_activity,pg_locks
left outer join pg_class on
(pg_locks.relation = pg_class.oid)  
where pg_locks.pid=pg_stat_activity.procpid and current_query not like 'auto%' ) a
where a.ageee>'00:00:10' order by current_query;

 

------------查看锁-------------------------------
select locktype,virtualtransaction,transactionid,nspname,relname,mode,granted,
cast(date_trunc('second',query_start) as timestamp) as query_start,
substr(query,1,25) as query
from pg_locks left outer join pg_class on (pg_locks.relation=pg_class.oid)
left outer join pg_namespace on (pg_namespace.oid=pg_class.relnamespace),
pg_stat_activity
where not pg_locks.pid=pg_backend_pid()
and pg_locks.pid=pg_stat_activity.pid;

--
select pg_class.relname, pg_locks.transactionid, pg_locks.mode, pg_locks.granted as "g",
pg_stat_activity.query, pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.pid, 'SELECT pg_cancel_backend('||pg_stat_activity.pid||');'
from pg_stat_activity,pg_locks
left outer join pg_class on
(pg_locks.relation = pg_class.oid)  
where pg_locks.pid=pg_stat_activity.pid and age(now(),pg_stat_activity.query_start)>'1 minute'::interval
------------查看死锁-------------------------------
select locktype,virtualtransaction,transactionid,nspname,relname,mode,granted,
cast(date_trunc('second',query_start) as timestamp) as query_start,
substr(current_query,1,25) as query
from pg_locks left outer join pg_class on (pg_locks.relation=pg_class.oid)
left outer join pg_namespace on (pg_namespace.oid=pg_class.relnamespace),
pg_stat_activity
where not pg_locks.pid=pg_backend_pid()
and pg_locks.pid=pg_stat_activity.procpid;

-----查询表锁-------
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a
join pg_class b on a.relation = b.oid
where b.relname = 'table';

--------------------------查看数据库索引膨胀情况---------------------------------------
-----查看数据库索引信息----------------------
----通常index_ratio应该小于100%
----如果大于2，建议重建索引
SELECT
    nspname,relname,
    round(100 * pg_relation_size(indexrelid) / pg_relation_size(indrelid)) / 100
      AS index_ratio,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(indrelid)) AS table_size
FROM pg_index I
LEFT JOIN pg_class C ON (C.oid = I.indexrelid)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
 nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND
  C.relkind='i' AND
  pg_relation_size(indrelid) > 0 order by index_ratio desc;
 
 
--查看表索引信息
select * from pg_stat_user_indexes ui where ui.relname in ('t_affair_remind_receive');
 
--查看表空间大小及分析时间
SELECT a.schemaname,a.table_name,a.row_num
,substring(a.table_spaces FROM 1 FOR POSITION(' ' IN a.table_spaces)-1)  table_spaces
,a.last_analyze,a.last_vacuum
FROM
(
SELECT sut.schemaname, sut.relname as table_name,c.reltuples as row_num
, pg_size_pretty(pg_total_relation_size(sut.relid)) as table_spaces
,COALESCE(sut.last_analyze,sut.last_autoanalyze) AS last_analyze
,COALESCE(sut.last_vacuum,sut.last_autovacuum) AS last_vacuum
FROM pg_stat_user_tables sut,pg_class c
WHERE sut.relname = c.relname
ORDER BY 3 DESC
) a
WHERE a.table_spaces LIKE '%MB'
AND SUBSTRING(a.table_spaces FROM 1 FOR POSITION(' ' IN a.table_spaces)-1)::INT > 500;

--检查表与索引在同一个表空间的情况
SELECT ta.relname,ta.rownum,ia.indexrelname,ta.spcname
FROM
(
SELECT sut.schemaname,sut.relname,pt.spcname,tc.reltuples as rownum
FROM pg_stat_user_tables sut,pg_class tc,(SELECT * FROM pg_tablespace WHERE spcname NOT IN ('pg_global') ) pt
WHERE sut.relname = tc.relname
AND tc.relowner = pt.spcowner
) ta,
(
SELECT sui.schemaname,sui.relname,sui.indexrelname,pt.spcname
FROM pg_stat_user_indexes sui,pg_class tc,(SELECT * FROM pg_tablespace WHERE spcname NOT IN ('pg_global') ) pt
WHERE sui.indexrelname = tc.relname
AND tc.relowner = pt.spcowner
) ia
WHERE ta.schemaname = ia.schemaname
AND ta.relname = ia.relname
AND ta.spcname = ia.spcname
ORDER BY 1,2;

--查询重复的索引
SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
       (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
       (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
    SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                         COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
    FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

--索引空间大小统计
SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

--索引概要
SELECT
    pg_class.relname,
    pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    COUNT(indexname) AS number_of_indexes,
    CASE WHEN x.is_unique = 1 THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    SUM(CASE WHEN number_of_columns = 1 THEN 1
              ELSE 0
            END) AS single_column,
    SUM(CASE WHEN number_of_columns IS NULL THEN 0
             WHEN number_of_columns = 1 THEN 0
             ELSE 1
           END) AS multi_column
FROM pg_namespace
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
       (SELECT indrelid,
           MAX(CAST(indisunique AS INTEGER)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
    AS foo
    ON pg_class.relname = foo.ctablename
WHERE
     pg_namespace.nspname='public'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;

--检查表空间使用情况
SELECT tsa.tablespace_name,tsa.tablespace_spaces/1024/1024  tablespace_spaces
FROM
(
SELECT ts.spcname tablespace_name
,pg_tablespace_size(ts.spcname::CHARACTER VARYING)::BIGINT tablespace_spaces
FROM pg_tablespace ts
) tsa;

--检查表扫描的命中率
SELECT
  relname,
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
  n_live_tup rows_in_table
FROM
  pg_stat_user_tables
WHERE
    seq_scan + idx_scan > 0
ORDER BY
  n_live_tup DESC

--缓冲区的命中率

SELECT
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
  pg_statio_user_indexes;
 
select name,substring(setting,1,10),unit from pg_settings order by category, name;

select attrelid ::regclass, attname, atttypid ::regtype, attnotnull, attnum
from pg_attribute
where attrelid = 'tbl_role' ::regclass

--生成主键自增
select pg_class.relname,pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename
,'ALTER TABLE '|| pg_class.relname ||'   ALTER COLUMN '|| pg_attribute.attname ||' SET DEFAULT nextval('''|| pg_class.relname ||'_seq''::regclass);'
from pg_constraint  
inner join pg_class on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid
and  pg_attribute.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attribute.atttypid
where pg_class.relname like 't_cc%'
and pg_constraint.contype='p'

--创建序列
select ut.relname::character varying ,'CREATE SEQUENCE '|| ut.relname::character varying ||'_seq   INCREMENT 1   START 1   MINVALUE 1   MAXVALUE 999999999999999999   CACHE 10;'
from pg_stat_user_tables ut;

--这是监控索引未使用的，你评估下是否可以删除
select *
      from (
           SELECT t.relname,
                  t.indexrelname ,
                  max(idx_scan)-min(idx_scan) AS diff_idx_scan,
                  max(idx_tup_read)-min(idx_tup_read) AS diff_idx_tup_read
           FROM db_stat.snapshot_pg_stat_all_indexes t
           WHERE indexrelname not like 'pk_%' and relname not like 'pg_%'
           GROUP BY t.relname, t.indexrelname) t1
           where diff_idx_scan = 0
order by diff_idx_scan,relname,indexrelname ;

==查看表的大小和表中索引个数
SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

---------------查看索引趋势语句----------------------
--------------
--查看使用比较少的索引
select *
      from (
           SELECT t.relname,
                  t.indexrelname ,
                  max(idx_scan)-min(idx_scan) AS diff_idx_scan,
                  max(idx_tup_read)-min(idx_tup_read) AS diff_idx_tup_read
           FROM db_stat.snapshot_pg_stat_all_indexes t
           --WHERE snap_create_time BETWEEN '2015-12-11' AND '2016-03-11'
           GROUP BY t.relname, t.indexrelname) t1
order by diff_idx_scan,relname,indexrelname ;

--查看索引使用率趋势图
select relname,
       indexrelname,
       snap_day,
       diff_idx_scan,
       case when sum(diff_idx_scan) over w1 >0 then  diff_idx_scan*100/sum(diff_idx_scan) over w1 else 0 end as  diff_idx_scan_percent,
       diff_idx_tup_read,
       case when sum(diff_idx_tup_read) over w1 >0 then  diff_idx_tup_read*100/sum(diff_idx_tup_read) over w1  else 0 end as diff_idx_tup_read_percent
from (
      SELECT t.relname,
             t.indexrelname,
             date_trunc('hour', snap_create_time) snap_day,
             t.idx_scan-lag(t.idx_scan,1) over w AS diff_idx_scan,
             t.idx_tup_read - lag(t.idx_tup_read,1) over w AS diff_idx_tup_read
      from db_stat.snapshot_pg_stat_all_indexes t
      --where indexrelname in ('','')
      WINDOW w AS (PARTITION BY t.relname,t.indexrelname ORDER BY date_trunc('hour', t.snap_create_time))
) t1
where diff_idx_scan is not null
WINDOW w1 as (PARTITION BY t1.relname,t1.indexrelname)
order by relname,indexrelname,snap_day;