原创

postgres 维护操作集合

  1. 查看正在执行的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 ,pg_stat_activity pa  
    WHERE   
    current_query <> '<IDLE>' and  procpid<> pg_backend_pid() and pa.pid=s.procpid and pa.state<>'idle'  
    ORDER BY   
    lap DESC;
    
  2. 查看锁住的sql语句

    SELECT locker.pid,  
         pc.relname,  
         locker.mode,  
         locker_act.application_name,  
         least(query_start,xact_start) start_time,  
         locker_act.state,  
         CASE  
     WHEN granted='f' THEN  
     'wait_lock'  
     WHEN granted='t' THEN  
     'get_lock'  
     END lock_satus,current_timestamp - least(query_start,xact_start) AS runtime,  
     locker_act.query  
    FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc  
    WHERE locker.pid=locker_act.pid  
         AND NOT locker.pid=pg_backend_pid()  
         AND application_name<>'pg_statsinfod'  
         AND locker.relation = pc.oid  
         AND pc.reltype<>0 --and pc.relname='t' 
    ORDER BY  runtime desc;
    
  3. 字段说明:

字段 说明
procpid 进程id
start 进程开始时间
lap 经过时间
current_query 执行中的sql
  1. 怎样停止正在执行的sql
    SELECT pg_cancel_backend(进程id);
    SELECT pg_terminate_backend(PID);
    或者用系统函数
    kill -9 进程id;

5.修改表分区键

alter table public.test_table set distributed by(name);

更多关于分区的详细教程:
http://www.postgres.cn/docs/9.4/ddl-partitioning.html

https://blog.51cto.com/13126942/2053712

https://cloud.tencent.com/developer/article/1374067

https://blog.csdn.net/lsr40/article/details/90642873

分区表:https://zhuanlan.zhihu.com/p/334958464

分布键:https://blog.csdn.net/double_happiness/article/details/83273054

正文到此结束
该篇文章的评论功能已被站长关闭
本文目录