GBase 8a 迁移GreenPlum常用sql(2)

greenplum 进程ID 查看

SELECT procpid,START,now() - START AS lap,current_query,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;

kill 进程ID

SELECT PG_TERMINATE_BACKEND(18554); --取消后台操作，回滚未提交事物，kill当前查询的session

关闭more显示模式

\pset pager off

gp查询数据库中当前正在执行的sql

SELECT datname,current_query from pg_stat_activity;

gp查询函数创建语句

select prosrc from pg_proc where proname ='f_ret';

pg_dump参数格式，文件夹需提前创建

        pg_dump -E UTF8 -s -v dw -n t1  > /home/gpadmin/20220222/t1.ddl
        -v --verbose 详细模式 
        -s 只转储模式，不转储数据
        -d 指定数据库
        -n 只转储指定的schema
        pg_dump -E UTF8 -s -v dw -n IN >/var/gbase_data/in.ddl