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

gprecoverseg 优先级顺序 1. gprecoverseg -a --> 2. gprecoverseg --> 3. gprecoverseg -r

只有1和2实在恢复不了，数据无法修复恢复时才会使用[ gprecoverseg -F ]

1-6. greenplum激活备库流程

• gpactivatestandby

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

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

• gpinitstandby

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

2. SQL命令

2-1. 表相关

2-1-1. 查看表信息

• 所有表信息，不包含分区表的子表

xxxxxxxxxx
-- 所有表信息，不包含分区表的子表
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;

• 所有表信息，包含分区表的子表

xxxxxxxxxx
-- 所有表信息，包含分区表的子表
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. 查看表分区信息

xxxxxxxxxx
-- 查看某个分区表的信息   
SELECT
    tablename,
    partitiontablename,
    partitiontype,
    partitionboundary 
FROM
    pg_partitions 
WHERE
    tablename = 'here_your_table_name' 
ORDER BY
    partitionboundary DESC; 

2-1-3. 查看表的存储空间占用

• 查看指定表的文件占用大小

​x
-- 字节显示 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'));

• 查看全部表的文件占用大小

xxxxxxxxxx
-- 查看全部表的大小
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. 查看表的数据量

xxxxxxxxxx
-- 评估表中的数据量
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. 表数据量重新分布均衡

xxxxxxxxxx
-- 不会改变分布键，会清理过期空间
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. 查看索引信息

• 查看所有表的索引信息

xxxxxxxxxx
-- 查看所有表的索引信息
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';

• 查看单表的索引信息

xxxxxxxxxx
-- 查看单表的索引信息
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. 查看索引空间占用

xxxxxxxxxx
-- 检查索引占用
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列信息

xxxxxxxxxx
-- 查看单表的column列信息
SELECT
    * 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public' 
    AND TABLE_NAME = 'here_your_table_name';

• 仅查看单表column列名称(逗号分隔)

xxxxxxxxxx
-- 仅查看单表column列名称(逗号分隔)
SELECT
    string_agg ( COLUMN_NAME, ',' ) 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public' 
    AND TABLE_NAME = 'here_your_table_name';

• 查看所有表的列信息

xxxxxxxxxx
-- 查看所有表的列信息
SELECT
    * 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public';    

• 查看所有表的列名称，逗号分隔

xxxxxxxxxx
-- 查看所有表的列名称，逗号分隔
SELECT
    string_agg ( COLUMN_NAME, ',' ),
TABLE_NAME 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public' 
GROUP BY
    2;

2-4. 环境相关

• 查看segment节点状态

xxxxxxxxxx
-- 查看segment节点状态
select * from gp_segment_configuration; 

• 查看各segment剩余可用空间

xxxxxxxxxx
-- 查看各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每个数据库占用空间大小

xxxxxxxxxx
-- greemplum每个数据库占用空间大小
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

• 查看库下面的所有schema占用的磁盘空间

xxxxxxxxxx
-- 查看库下面的所有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下所有表和索引 占用的磁盘空间

xxxxxxxxxx
-- 查看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的信息

xxxxxxxxxx
-- 查看在系统表中被标记为掉线的Segment的信息
SELECT * from gp_toolkit.gp_pgdatabase_invalid; 

• 正在活动的进程

xxxxxxxxxx
-- 正在活动的进程
select * from pg_stat_activity where state ='active';

• 空闲的进程

xxxxxxxxxx
-- 空闲的进程
select * from pg_stat_activity where state ='idle';

• 关闭正在执行的SQL进程

xxxxxxxxxx
-- Kill正在执行的SQL进程

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

-- 可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作
select  pg_terminate_backend(线程id);

• 查询对应用户的连接

xxxxxxxxxx
-- 查询对应用户的连接
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;

• 查看超级管理用户最大连接数

xxxxxxxxxx
-- 查看超级管理用户最大连接数
show superuser_reserved_connections;

• 查看膨胀表

xxxxxxxxxx
-- 查看膨胀表
select * from gp_toolkit.gp_bloat_diag order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname;

表膨胀通常指的是表的物理磁盘空间使用超过了实际需要的大小，导致性能下降和资源浪费，表膨胀问题需要处理，因为它会导致磁盘空间的浪费，减慢查询性能

1. 表重新组织（REORG）：这将重新组织表以减少碎片并释放未使用的磁盘空间。REORG是一个轻量级的操作，适用于小型的膨胀表。

   xxxxxxxxxx
   REORGANIZE here_your_table_name;
   

2. 表重建（REBUILD）：这将创建一个新的表，将数据从原始表复制到新表，并替换原始表。这是处理大型膨胀表的更强大选项。

   xxxxxxxxxx
   REBUILD here_your_table_name;
   

3. VACUUM操作：运行VACUUM操作可以帮助清理并释放未使用的磁盘空间。在运行REORG或REBUILD之前，通常需要运行VACUUM来减少膨胀表的碎片。

   VACUUM here_your_table_name;