- 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;
- --查看PostgreSQL正在执行的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;
- procpid:进程id
- start:进程开始时间
- lap:经过时间
- current_query:执行中的sql
- 怎样停止正在执行的sql
- SELECT pg_cancel_backend(进程id);
- 或者用系统函数
- kill -9 进程id;
- --查找是否有waiting
- ps -ef|grep postgres | grep wait
- --查看当前库表和索引的的大小并排序显示前20条
- SELECT
- nspname,
- relname,
- relkind as "type",
- pg_size_pretty(pg_table_size(C.oid)) AS size,
- pg_size_pretty(pg_indexes_size(C.oid)) AS idxsize,
- pg_size_pretty(pg_total_relation_size(C.oid)) as "total"
- FROM pg_class C
- LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND
- nspname !~ '^pg_toast' AND
- relkind IN ('r','i')
- ORDER BY pg_total_relation_size(C.oid) DESC
- LIMIT 20;