greenplum-常用管理语句-转载

1.greenplum查询正在运行的sql，session

– 方法1：

SELECT
tt.procpid, – pid
usename user_name, – 执行的用户
backend_start, – 会话开始时间
query_start, – 查询开始时间
waiting, – 是否等待执行
now() - query_start AS current_query_time, – 累计执行时间
now() - backend_start AS current_session_time,*/
current_query,
client_addr , datname
FROM
pg_stat_activity tt
WHERE current_query != ‘’
ORDER BY current_query_time DESC;

– 方法2（通过视图查）

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!= ‘’
ORDER BY lap DESC;

– 方法3（限定了角色和资源队列，查当前账号正在查询的语句）

SELECT
rolname,
rsqname,
pid,
GRANTED,
current_query,
datname
FROM
pg_roles t1,
gp_toolkit.gp_resqueue_status t2 ,
pg_locks t3 ,
pg_stat_activity t4
WHERE
t1.rolresqueue = t3.objid
AND t3.objid=t2.queueid
and t4.procpid=t3.pid

2.终止执行的sql

select pg_terminate_backend(48988); --pid

3.查看greemplum资源队列状态

SELECT * FROM gp_toolkit.gp_resqueue_status;

4.查看greemplum资源队列锁

SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting=‘true’;

5.查看greemplum资源队列优先级

select * from gp_toolkit.gp_resq_priority_statement;

6.查看greemplum所有连接 类似mysql SHOW PROCESSLIST

select * from pg_stat_activity; – 所有状态的连接

7.greemplum磁盘使用，通过SQL查看Greenplum中用了多少空间

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

8.查看greemplum节点状态

select * from gp_segment_configuration tt
select * from gp_segment_configuration tt where tt.status=‘d’; – 状态为down

9.节点故障等历史信息

select * from gp_configuration_history tt order by 1 desc ;

10.数据倾斜

SELECT
t1.gp_segment_id,
t1.count_tatol,
round(t1.count_tatol-(AVG(t1.count_tatol) over()) ,0)
FROM
(
SELECT
gp_segment_id,
COUNT (*) count_tatol
FROM
– 要查的表
GROUP BY
gp_segment_id
) t1
order by 3

11.greemplum表或索引大小 （占用空间）

select pg_size_pretty(pg_relation_size(‘gp_test’));

12.greemplum表和索引大小（占用空间）

select pg_size_pretty(pg_total_relation_size(‘gp_test’));

13.greemplum查看指定数据库大小（占用空间）

select pg_size_pretty(pg_database_size(‘postgres’));

14.greemplum所有数据库大小（占用空间）

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

15.查看greemplum数据分布情况

select gp_segment_id,count(*) from gp_test group by gp_segment_id order by 1;

二 查源数据

16.查看greemplum数据表更新时间

SELECT
*
FROM
pg_stat_last_operation,
pg_class
WHERE
objid = oid
AND relname = ‘base_common’; – 表名

17.通过sql 获取greemplum表的预估数据量

select
relname,
reltuples::int as total
from
pg_class
where
relname = ‘base_common’
and relnamespace = (select oid from pg_namespace where nspname = ‘positions’);

18.通过sql 获取greemplum获取分布键

SELECT string_agg(att.attname,‘,’ order by attrnums) as distribution
FROM gp_distribution_policy a,pg_attribute att
WHERE a.localoid =‘bi_data.schoolmate_relations’::regclass
and a.localoid = att.attrelid
and att.attnum = any(a.attrnums);

19.通过sql获取 greemplum指定表结构

SELECT
attname, typname
FROM
pg_attribute
INNER JOIN pg_class ON pg_attribute.attrelid = pg_class.oid
INNER JOIN pg_type ON pg_attribute.atttypid = pg_type.oid
INNER JOIN pg_namespace on pg_class.relnamespace=pg_namespace.oid –
WHERE
pg_attribute.attnum > 0
AND attisdropped <> ‘t’
AND pg_namespace.nspname=‘resumes’
AND pg_class.relname= ‘base_common’
– and pg_class.relname ~~* any(array[‘%some%’, ‘%someelse’]));
order by pg_attribute.attnum

同时可以参考：

【greenplum】greenplum 数据字典实践–通过sql脚本查询表结构，拼装建表语句

【greenplum】 获取表结构，实现类似mysql show create table 功能

20.显示哪些没有统计信息且可能需要ANALYZE的表

SELECT * from gp_toolkit.gp_stats_missing ;

21.显示在系统表中被标记为掉线的Segment的信息

SELECT * from gp_toolkit.gp_pgdatabase_invalid;

22.显示库中表的大小（单位G）

SELECT sotdoid,sotdsize/1024/1024/1024 as sotdsize,sotdtoastsize,sotdadditionalsize,sotdschemaname,sotdtablename from gp_toolkit.gp_size_of_table_disk order by sotdsize desc;

23.查询一个库中有多少表（如果有分区表不列出子分区）

SELECT relname from pg_class a,pg_namespace b where relname not like ‘%prt%’ and relkind =‘r’ and a.relnamespace=b.oid and nspname not in (‘pg_catalog’,‘information_schema’,‘gp_toolkit’) and nspname not like ‘%pg_temp%’;

24.查询某个用户对某个表有什么权限

select * from INFORMATION_SCHEMA.role_table_grants where grantee=‘user_name’ and table_name=‘table’;

25.查看分区表的信息

SELECT tablename,partitiontablename,partitiontype,partitionboundary from pg_partitions where tablename=‘table_name’ order by partitionboundary desc;

26.导入数据

copy t1 from ‘/home/gpadmin/t1.txt’ with delimiter ‘|’ LOG ERRORS INTO INSERT_ERRS SEGMENT REJECT LIMIT 100;

27.远程导入数据

psql -h 1.1.1.1 -U user_name -d db_name -W -c “copy tb1 from stdin with delimiter ‘|’” < /home/gpadmin/tb1.txt

28.导数据指定分隔符（和mysql的select into outfile很像）

psql -d db_name -c “select * from tb1” -o tb1.txt -t -A -F $‘\t’

29.生成授权语句

SELECT ‘grant select on ‘||relname||’ to user_name;’ from pg_class a,pg_namespace b where relname not like ‘%prt%’ and relkind =‘r’ and has_table_privilege(‘user_name’,a.oid,‘select’)=‘f’ and a.relnamespace=b.oid and nspname not in (‘pg_catalog’,‘information_schema’,‘gp_toolkit’) and nspname not like ‘%pg_temp%’;

30 .给用户授权

select ‘grant all on SCHEMA ’ || tt.autnspname || ’ to tuser;’ as grant_script from gp_toolkit.__gp_user_tables tt – group by tt.autnspname
union – all
select ‘grant all on table ’ || tt.autnspname || ‘.’ ||tt.autrelname || ’ to tuser;’ grant_script from gp_toolkit.__gp_user_tables tt;

31 .查看数据库被锁表

select locktype, --##锁的类型

​ database,

​ c.relname, --##表名

​ l.relation,l.transactionid,l.transaction,

​ l.pid,l.mode,l.granted,a.current_query

from pg_locks l,

​ pg_class c,

​ pg_stat_activity a

where l.relation=c.oid

and l.pid=a.procpid

order by c.relname;