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日由蛋蛋发布,转载请注明出处
热门推荐
相关文章
该篇文章的评论功能已被站长关闭