greenplum集群常用运维排障命令汇总
1. 管理命令
1-1. 查看greenplum集群状态-gpstate
1-2. greenplum配置参数管理-gpconfig
1-3. 启动greenplum集群-gpstart
1-4. 停止nplum集群-gpstop
1-5. greenplum集群数据恢复修复-gprecoverseg
1-6. greenplum激活备库流程
gpactivatestandby -d path: 指定备库路径,使用数据目录绝对路径，默认：$ MASTER_DATA_DIRECTORY
gpactivatestandby -f: 强制激活备份主机。
gpactivatestandby -v | 显示此版本信息
1-7. 初始化备Master(standby)
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列信息
SELECT
*
FROM
information_schema.COLUMNS
WHERE
table_schema = 'public'
AND TABLE_NAME = 'here_your_table_name';
-- 仅查看单表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节点状态
select * from gp_segment_configuration;
-- 查看各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每个数据库占用空间大小
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
-- 查看库下面的所有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下所有表和索引 占用的磁盘空间
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的信息
SELECT * from gp_toolkit.gp_pgdatabase_invalid;
-- 正在活动的进程
select * from pg_stat_activity where state ='active';
-- 空闲的进程
select * from pg_stat_activity where state ='idle';
-- 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;