原创

postgres 维护操作集合

查看正在执行的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;

查看锁住的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;

字段说明:

字段	说明
procpid	进程id
start	进程开始时间
lap	经过时间
current_query	执行中的sql

怎样停止正在执行的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

正文到此结束

所属分类：数据库

本文标签： postgreSql
版权声明： 本站原创文章，于2020年12月28日由蛋蛋发布，转载请注明出处

postgres 维护操作集合

热门推荐

相关文章

说给你听

免注册登录

本文目录

标签云

近期评论

网站信息