如何查看Greenplum或者postgreSQL的后台查询进程

如何查看Greenplum或者postgreSQL的后台查询进程

# 查看进程id，耗费时间，开始时间，当前查询语句
SELECT
    procpid,
    START,
    now() - START AS lap,
    current_query,
 	 -- count() over() count_num,
  	t2.rolname,t3.rsqname,
  	ip
FROM
    (
        SELECT
            backendid,
            pg_stat_get_backend_userid(S.backendid) as uid,
            pg_stat_get_backend_client_addr(S.backendid) as ip,
            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 t1 left join pg_authid  t2 on t1.uid=t2.oid
    left join pg_resqueue t3 on t2.rolresqueue=t3.oid
WHERE
    current_query!= '<IDLE>' and rolname = 'gpadmin'
ORDER BY lap DESC;

--杀死查询进程
select pg_terminate_backend(32004);

# 取消该后台查询进程
--假如PG_TERMINATE_BACKEND(进程ID)不能杀死session的情况下，我们可以使用kill -9 pid强制杀死session
SELECT PG_CANCEL_BACKEND(进程ID); --取消后台操作，回滚未提交事物don't kill当前session
SELECT PG_TERMINATE_BACKEND(进程ID); --取消后台操作，回滚未提交事物，kill当前查询的session