100 条命令系列文章专栏
Greenplum 是基于 PostgreSQL 构建的 MPP 分布式分析型数据库，由 Coordinator、Standby Coordinator、Primary Segment 和 Mirror Segment 等组件组成。DBA 日常工作不仅包括数据库对象和 SQL，还需要关注 Segment 状态、数据分布、资源组、膨胀、统计信息、备份恢复及故障
Greenplum 是基于 PostgreSQL 构建的 MPP 分布式分析型数据库，由 Coordinator、Standby Coordinator、Primary Segment 和 Mirror Segment 等组件组成。DBA 日常工作不仅包括数据库对象和 SQL，还需要关注 Segment 状态、数据分布、资源组、膨胀、统计信息、备份恢复及故障恢复。
下面整理 Greenplum DBA 常用的 100 条命令，主要面向 Greenplum 6/7 系列。不同发行版在术语上可能使用 Master/Coordinator，部分系统视图、资源管理方式和工具参数也可能不同，执行前应确认版本。开源 Greenplum 原项目仓库目前已归档，商业发行版应以对应 Broadcom Tanzu Greenplum 文档为最终依据。
文中的主机、端口、目录、数据库和表名均为示例。停止集群、恢复 Segment、切换 Standby、调整配置、重分布和恢复备份等操作，应先确认影响范围并准备回退方案。
1psql -h 192.168.1.10 -p 5432 -U gpadmin -d postgres客户端应连接 Coordinator，不要直接连接 Segment 处理业务数据。
1SELECT version();1SELECT current_database();1SELECT current_user, session_user;1SELECT now(), current_timestamp;1SELECT inet_server_addr(), inet_server_port(),2 inet_client_addr(), inet_client_port();1SHOW search_path;1\l+1\dn+1\dx1gpstate1gpstate -s1gpstate -e1gpstate -m1gpstate -f1gpstate -p1SELECT dbid, content, role, preferred_role, mode, status,2 hostname, address, port, datadir3FROM gp_segment_configuration4ORDER BY content, role;1SELECT dbid, content, role, preferred_role, mode, status,2 hostname, port, datadir3FROM gp_segment_configuration4WHERE status <> 'u'5 OR role <> preferred_role6 OR mode <> 's'7ORDER BY content, role;1SELECT hostname, role, count(*) AS segment_count2FROM gp_segment_configuration3WHERE content >= 04GROUP BY hostname, role5ORDER BY hostname, role;1SELECT *2FROM gp_configuration_history3ORDER BY time DESC4LIMIT 50;1CREATE DATABASE appdb;1SELECT datname,2 pg_size_pretty(pg_database_size(datname)) AS size3FROM pg_database4ORDER BY pg_database_size(datname) DESC;1SELECT datname, count(*) AS connections2FROM pg_stat_activity3GROUP BY datname4ORDER BY connections DESC;1\dt+ app.*1pg_dump -s -t app.orders appdb1CREATE TABLE app.orders (2 order_id BIGINT,3 customer_id BIGINT,4 order_time TIMESTAMP,5 amount NUMERIC(18,2)6)7DISTRIBUTED BY (order_id);1CREATE TABLE app.event_stage (2 event_id BIGINT,3 payload TEXT4)5DISTRIBUTED RANDOMLY;1CREATE TABLE app.dim_status (2 status_code VARCHAR(20),3 status_name VARCHAR(100)4)5DISTRIBUTED REPLICATED;复制表适合体量较小、更新不频繁的维表。
1CREATE TABLE app.order_history (2 order_id BIGINT,3 order_time TIMESTAMP,4 amount NUMERIC(18,2)5)6WITH (appendoptimized=true, orientation=row)7DISTRIBUTED BY (order_id);1CREATE TABLE app.fact_sales (2 sale_id BIGINT,3 sale_date DATE,4 amount NUMERIC(18,2)5)6WITH (7 appendoptimized=true,8 orientation=column,9 compresstype=zlib,10 compresslevel=511)12DISTRIBUTED BY (sale_id);1CREATE TABLE app.fact_log (2 log_id BIGINT,3 log_date DATE,4 message TEXT5)6DISTRIBUTED BY (log_id)7PARTITION BY RANGE (log_date)8(9 START (DATE '2026-01-01')10 INCLUSIVE END (DATE '2027-01-01')11 EXCLUSIVE EVERY (INTERVAL '1 month')12);分区语法在不同大版本存在差异，执行前应在测试环境验证。
1SELECT n.nspname AS schema_name,2 c.relname AS table_name,3 pg_get_table_distributedby(c.oid) AS distributed_by4FROM pg_class c5JOIN pg_namespace n ON n.oid = c.relnamespace6WHERE n.nspname = 'app'7 AND c.relname = 'orders';1SELECT *2FROM pg_partitions3WHERE schemaname = 'app'4 AND tablename = 'fact_log'5ORDER BY partitionrank;1SELECT n.nspname, c.relname, c.relstorage2FROM pg_class c3JOIN pg_namespace n ON n.oid = c.relnamespace4WHERE n.nspname = 'app'5ORDER BY c.relname;relstorage 的取值含义与版本有关，可结合 pg_appendonly 判断 AO 表属性。
1SELECT n.nspname, c.relname, a.*2FROM pg_appendonly a3JOIN pg_class c ON c.oid = a.relid4JOIN pg_namespace n ON n.oid = c.relnamespace5WHERE n.nspname = 'app';1SELECT pg_size_pretty(2 pg_total_relation_size('app.orders')3 ) AS total_size;1SELECT sotdschemaname AS schemaname,2 pg_size_pretty(3 SUM(sotdsize + sotdtoastsize + sotdadditionalsize)::BIGINT4 ) AS total_size5FROM gp_toolkit.gp_size_of_table_and_indexes_disk6GROUP BY sotdschemaname7ORDER BY SUM(sotdsize + sotdtoastsize + sotdadditionalsize) DESC;1SELECT sotdschemaname AS schemaname,2 sotdtablename AS tablename,3 pg_size_pretty(4 sotdsize + sotdtoastsize + sotdadditionalsize5 ) AS size6FROM gp_toolkit.gp_size_of_table_and_indexes_disk7ORDER BY sotdsize + sotdtoastsize + sotdadditionalsize DESC8LIMIT 20;1SELECT gp_segment_id, count(*) AS rows2FROM app.orders3GROUP BY gp_segment_id4ORDER BY gp_segment_id;1SELECT gp_segment_id, count(*) AS rows2FROM app.orders3GROUP BY gp_segment_id4ORDER BY rows DESC;最大值与平均值差距过大时，应重新评估分布键。
1SELECT *2FROM gp_toolkit.gp_skew_coefficients3WHERE skcnamespace = 'app'4ORDER BY skccoeff DESC;1SELECT *2FROM gp_toolkit.gp_skew_idle_fractions3WHERE sifnamespace = 'app'4ORDER BY siffraction DESC;1SELECT *2FROM gp_toolkit.gp_bloat_diag3WHERE bdirelname = 'orders';1SELECT *2FROM gp_toolkit.__gp_aovisimap_compaction_info(3 'app.orders'::regclass4);该内部工具函数的可用性和返回列与版本有关。
1gpssh -f hostfile -e 'df -h'1SELECT pid, usename, datname, client_addr,2 state, query_start, wait_event_type, wait_event, query3FROM pg_stat_activity4WHERE state <> 'idle'5ORDER BY query_start;Greenplum 6 的部分视图仍可能使用 procpid 等旧字段。
1SELECT pid, usename, datname,2 now() - query_start AS elapsed,3 state, query4FROM pg_stat_activity5WHERE state <> 'idle'6 AND query_start < now() - interval '5 minutes'7ORDER BY query_start;1SELECT pg_cancel_backend(12345);1SELECT pg_terminate_backend(12345);1SELECT locktype, database, relation::regclass,2 mode, granted, pid3FROM pg_locks4ORDER BY granted, pid;1SELECT blocked.pid AS blocked_pid,2 blocker.pid AS blocker_pid,3 blocked.query AS blocked_sql,4 blocker.query AS blocker_sql5FROM pg_stat_activity blocked6JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted7JOIN pg_locks kl8 ON kl.locktype = bl.locktype9 AND kl.database IS NOT DISTINCT FROM bl.database10 AND kl.relation IS NOT DISTINCT FROM bl.relation11 AND kl.granted12JOIN pg_stat_activity blocker ON blocker.pid = kl.pid13WHERE blocked.pid <> blocker.pid;1SELECT *2FROM gp_toolkit.gp_locks_on_relation3ORDER BY lorrelname;1EXPLAIN2SELECT *3FROM app.orders4WHERE order_id = 10001;1EXPLAIN (ANALYZE, VERBOSE, BUFFERS)2SELECT customer_id, sum(amount)3FROM app.orders4GROUP BY customer_id;ANALYZE 会真正执行 SQL，修改类语句必须放在可回滚事务中测试。
1SELECT *2FROM gp_toolkit.gp_resqueue_status;1SELECT *2FROM gp_toolkit.gp_resgroup_status;资源队列与资源组的使用取决于版本和集群配置。
1SELECT *2FROM gp_toolkit.gp_resgroup_status_per_host;1SELECT logtime, logseverity, loguser, logdatabase,2 logmessage3FROM gp_toolkit.gp_log_database4WHERE logtime > now() - interval '1 hour'5ORDER BY logtime DESC;1SELECT n.nspname, c.relname AS index_name,2 i.indisvalid, i.indisready3FROM pg_index i4JOIN pg_class c ON c.oid = i.indexrelid5JOIN pg_namespace n ON n.oid = c.relnamespace6WHERE NOT i.indisvalid OR NOT i.indisready;1SELECT schemaname, relname2FROM pg_stat_all_tables3WHERE schemaname NOT IN ('pg_catalog', 'information_schema')4 AND last_analyze IS NULL5 AND last_autoanalyze IS NULL6ORDER BY schemaname, relname;1ANALYZE app.orders;1ANALYZE app.orders (customer_id, order_time);1ANALYZE;1SELECT schemaname, relname,2 last_analyze, last_autoanalyze3FROM pg_stat_all_tables4WHERE schemaname = 'app'5ORDER BY relname;1VACUUM app.orders;1VACUUM ANALYZE app.orders;1VACUUM FULL app.orders;该命令会持有强锁并重写表，应在维护窗口执行。
1VACUUM app.order_history;AO 表的 VACUUM 会根据隐藏行比例执行压缩，实际行为取决于版本和阈值。
1REINDEX TABLE app.orders;1ALTER TABLE app.orders2SET DISTRIBUTED BY (customer_id);该操作通常需要重分布全表数据，应评估空间、锁和执行时间。
1SHOW ALL;1gpconfig -s max_connections1gpconfig -c log_min_duration_statement -v 3000是否动态生效取决于参数上下文。
1gpconfig -c max_connections -m 200 -v 10001gpstop -u1gpstart -a1gpstop -a1gpstop -a -M fastFast 模式会回滚活动事务，执行前应通知业务。
1gpstop -a -r1gpssh -f hostfile -e 'ps -ef | grep "[p]ostgres"'1gpbackup --dbname appdb1gpbackup --dbname appdb --include-schema app1gpbackup --dbname appdb --include-table app.orders1gpbackup --dbname appdb --backup-dir /backup/greenplum1gprestore --timestamp 202607271030001gprestore --timestamp 20260727103000 --create-db1gprestore --timestamp 20260727103000 \2 --include-table app.orders1gpfdist -d /data/load -p 8081 -l /tmp/gpfdist.log1CREATE EXTERNAL TABLE app.ext_orders (2 order_id BIGINT,3 customer_id BIGINT,4 amount NUMERIC(18,2)5)6LOCATION ('gpfdist://etl01:8081/orders.csv')7FORMAT 'CSV' (HEADER);1gpload -f load_orders.yml -l load_orders.log1gprecoverseg -a先通过 gpstate -e 确认故障范围和主机状态。
1gprecoverseg -a -F全量恢复开销较大，仅在增量恢复不可用或数据目录重建时使用。
1gprecoverseg -a -r1gpaddmirrors -i mirror_config应先用工具生成并审核配置文件，再正式执行。
1gpinitstandby -s gpstandby1gpactivatestandby -a仅在确认原 Coordinator 不再提供服务且 Standby 同步正常后执行。
1gpexpand -f new_hosts1gpexpand -i gpexpand_inputfile1gpexpand -d 01:00:00参数和工作流在不同版本存在差异，应以当前发行版扩容手册为准。
1gpstate -s2gpstate -e3gpstate -f4gpconfig -s max_connections5gpssh -f hostfile -e 'df -h'6psql -d postgres -c \7"SELECT content, role, preferred_role, mode, status, hostname8 FROM gp_segment_configuration ORDER BY content, role;"巡检还应检查数据库容量、数据倾斜、长 SQL、锁、统计信息、膨胀、备份状态和主机资源。
Greenplum 运维的核心是把 PostgreSQL 单库视角扩展到整个 MPP 集群。遇到问题时，应依次检查 Coordinator、Segment、Mirror、数据分布、资源管理和 SQL 执行计划，避免只在 Coordinator 上观察局部现象。
对于 Segment 恢复、Standby 激活、扩容和全表重分布等高风险操作，应使用与当前发行版匹配的官方手册，并在执行前完成备份、空间评估和回退设计。