greenplum集群常用运维排障命令汇总

1. 管理命令

1-1. 查看greenplum集群状态-gpstate

  • gpstate

1-2. greenplum配置参数管理-gpconfig

  • gpconfig

1-3. 启动greenplum集群-gpstart

  • gpstart

1-4. 停止nplum集群-gpstop

  • gpstop

1-5. greenplum集群数据恢复修复-gprecoverseg

  • gprecoverseg

1-6. greenplum激活备库流程

  • gpactivatestandby
gpactivatestandby -d path: 指定备库路径,使用数据目录绝对路径,默认:$ MASTER_DATA_DIRECTORY
gpactivatestandby -f: 强制激活备份主机。
gpactivatestandby -v | 显示此版本信息

1-7. 初始化备Master(standby)

  • gpinitstandby
gpinitstandby -s standby_name: 指定新备库名称。
gpinitstandby -D: debug调试模式。
gpinitstandby -r: 移除备用机。

2. SQL命令

2-1. 表相关

2-1-1. 查看表信息
  • 所有表信息,不包含分区表的子表
-- 所有表信息,不包含分区表的子表
SELECT
	psut.relid,
	psut.relname,
	psut.schemaname 
FROM
	pg_statio_user_tables psut
	LEFT JOIN pg_inherits pi ON psut.relid = pi.inhrelid 
WHERE
	schemaname = 'public' 
	AND pi.inhparent IS NULL 
ORDER BY
	2;
  • 所有表信息,包含分区表的子表
-- 所有表信息,包含分区表的子表
SELECT
	psut.relid,
	psut.relname,
	psut.schemaname 
FROM
	pg_statio_user_tables psut
	LEFT JOIN pg_inherits pi ON psut.relid = pi.inhrelid 
WHERE
	schemaname = 'public' 
ORDER BY
	2;
2-1-2. 查看表分区信息
-- 查看某个分区表的信息	
SELECT
	tablename,
	partitiontablename,
	partitiontype,
	partitionboundary 
FROM
	pg_partitions 
WHERE
	tablename = 'here_your_table_name' 
ORDER BY
	partitionboundary DESC;	
2-1-3. 查看表的存储空间占用
  • 查看指定表的文件占用大小
-- 字节显示 SELECT pg_relation_size('table_name');
SELECT pg_relation_size('here_your_table_name');

-- 显示格式(合理的文件大小单位) SELECT pg_size_pretty(pg_relation_size('table_name'));
SELECT pg_size_pretty(pg_relation_size('here_your_table_name'));
  • 查看全部表的文件占用大小
-- 查看全部表的大小
SELECT relname AS relation,
       pg_size_pretty (
         pg_total_relation_size (C .oid)
       ) AS total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE nspname NOT IN (
        'pg_catalog',
        'information_schema'
      )
  AND C .relkind <> 'i'
  AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size (C .oid) DESC
LIMIT 20;
2-1-4. 查看表的数据量
-- 评估表中的数据量
SELECT reltuples::numeric as count
FROM pg_class
WHERE relname='here_your_table_name';


-- 查看表数据分布情况(一张表各segment存储情况)
select gp_segment_id,count(*) from here_your_table_name group by gp_segment_id order by 1;
2-1-5. 表数据量重新分布均衡
-- 不会改变分布键,会清理过期空间
ALTER TABLE table_name SET WITH (REORGANIZE=true);

-- 指定新的分布键,会清理过期空间
ALTER TABLE table_name SET WITH (REORGANIZE=true) DISTRIBUTED BY (column_name,column_name);

-- 修改表分布策略为随机分布,但是不移动数据
ALTER TABLE table_name SET WITH (REORGANIZE=false) DISTRIBUTED randomly;

2-2. 索引

2-2-1. 查看索引信息
  • 查看所有表的索引信息
-- 查看所有表的索引信息
SELECT A
	.SCHEMANAME,
	A.TABLENAME,
	A.INDEXNAME,
	A.TABLESPACE,
	A.INDEXDEF,
	B.AMNAME,
	C.INDEXRELID,
	C.INDNATTS,
	C.INDISUNIQUE,
	C.INDISPRIMARY,
	C.INDISCLUSTERED,
	D.DESCRIPTION 
FROM
	PG_AM B
	LEFT JOIN PG_CLASS F ON B.OID = F.RELAM
	LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID
	LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID
	LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID,
	PG_INDEXES A 
WHERE
	A.SCHEMANAME = E.SCHEMANAME 
	AND A.TABLENAME = E.RELNAME 
	AND A.INDEXNAME = E.INDEXRELNAME 
	AND E.SCHEMANAME = 'public';
  • 查看单表的索引信息
-- 查看单表的索引信息
SELECT A
	.SCHEMANAME,
	A.TABLENAME,
	A.INDEXNAME,
	A.TABLESPACE,
	A.INDEXDEF,
	B.AMNAME,
	C.INDEXRELID,
	C.INDNATTS,
	C.INDISUNIQUE,
	C.INDISPRIMARY,
	C.INDISCLUSTERED,
	D.DESCRIPTION 
FROM
	PG_AM B
	LEFT JOIN PG_CLASS F ON B.OID = F.RELAM
	LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID
	LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID
	LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID,
	PG_INDEXES A 
WHERE
	A.SCHEMANAME = E.SCHEMANAME 
	AND A.TABLENAME = E.RELNAME 
	AND A.INDEXNAME = E.INDEXRELNAME 
	AND E.SCHEMANAME = 'public' 
	AND E.RELNAME = 'here_your_table_name';
2-2-2. 查看索引空间占用
-- 检查索引占用
SELECT schemaname || '.' || relname AS table, indexrelname AS index
	, pg_size_pretty(pg_relation_size(i.indexrelid)) AS "index size", idx_scan AS "index scans"
FROM pg_stat_user_indexes ui
	JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
	AND idx_scan < 50
	AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC
LIMIT 10;

2-3. 列信息

  • 查看单表的column列信息
-- 查看单表的column列信息
SELECT
	* 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = 'public' 
	AND TABLE_NAME = 'here_your_table_name';
  • 仅查看单表column列名称(逗号分隔)
-- 仅查看单表column列名称(逗号分隔)
SELECT
	string_agg ( COLUMN_NAME, ',' ) 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = 'public' 
	AND TABLE_NAME = 'here_your_table_name';
  • 查看所有表的列信息
-- 查看所有表的列信息
SELECT
	* 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = 'public';	
  • 查看所有表的列名称,逗号分隔
-- 查看所有表的列名称,逗号分隔
SELECT
	string_agg ( COLUMN_NAME, ',' ),
TABLE_NAME 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = 'public' 
GROUP BY
	2;

2-4. 环境相关

  • 查看segment节点状态
-- 查看segment节点状态
select * from gp_segment_configuration;	
  • 查看各segment剩余可用空间
-- 查看各segment剩余可用空间 dfspace / 1024 / 1024 表示GB
SELECT dfsegment, dfhostname, dfdevice
	, round(dfspace / 1024 / 1024, 2) AS free_disk_GB
FROM gp_toolkit.gp_disk_free
ORDER BY free_disk_MB DESC;
  • greemplum每个数据库占用空间大小
-- greemplum每个数据库占用空间大小
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
  • 查看库下面的所有schema占用的磁盘空间
-- 查看库下面的所有schema占用的磁盘空间
select sosdnsp,round(sosdschematablesize/1024/1024/1024,2) schema_tb_size_GB,round(sosdschemaidxsize /1024/1024/1024,2) as schema_idx_size_GB from gp_toolkit.gp_size_of_schema_disk;
  • 查看schema下所有表和索引 占用的磁盘空间
-- 查看schema下所有表和索引 占用的磁盘空间
SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024/1024,2) as INDEX_SIZE_GB FROM gp_toolkit.gp_size_of_all_table_indexes 
order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ;
  • 查看在系统表中被标记为掉线的Segment的信息
-- 查看在系统表中被标记为掉线的Segment的信息
SELECT * from gp_toolkit.gp_pgdatabase_invalid;	
  • 正在活动的进程
-- 正在活动的进程
select * from pg_stat_activity where state ='active';
  • 空闲的进程
-- 空闲的进程
select * from pg_stat_activity where state ='idle';
  • 关闭正在执行的SQL进程
-- Kill正在执行的SQL进程

-- 这个函数只能 kill、Select查询,而updae、delete DML不生效
select pg_cancel_backend(进程pid);

-- 可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作
select  pg_terminate_backend(线程id);
  • 查询对应用户的连接
-- 查询对应用户的连接
select client_addr,count(*) as a from pg_stat_activity  where usename = 'here_your_user_name' group by client_addr order by a desc ;

SELECT count(*), state FROM pg_stat_activity GROUP BY state;
  • 查看超级管理用户最大连接数
-- 查看超级管理用户最大连接数
show superuser_reserved_connections;
  • 查看膨胀表
-- 查看膨胀表
select * from gp_toolkit.gp_bloat_diag order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname;
10-31 13:48