分享！Greenplum常用命令整理
gpstate -b =》 显示简要状态
gpstate -c =》 显示主镜像映射
gpstart -d =》 指定数据目录（默认值：$MASTER_DATA_DIRECTORY）
gpstate -e =》 显示具有镜像状态问题的片段
gpstate -f =》 显示备用主机详细信息
gpstate -i =》 显示GRIPLUM数据库版本
gpstate -m =》 显示镜像实例同步状态
gpstate -p =》 显示使用端口
gpstate -Q =》 快速检查主机状态
gpstate -s =》 显示集群详细信息
gpstate -v =》 显示详细信息
作用
gpconfig -c =》 --change param_name 通过在postgresql.conf 文件的底部添加新的设置来改变配置参数的设置。
gpconfig -v =》 --value value 用于由-c选项指定的配置参数的值。默认情况下，此值将应用于所有Segment及其镜像、Master和后备Master。
gpconfig -m =》 --mastervalue master_value 用于由-c 选项指定的配置参数的Master值。如果指定，则该值仅适用于Master和后备Master。该选项只能与-v一起使用。
gpconfig -masteronly =》当被指定时，gpconfig 将仅编辑Master的postgresql.conf文件。
gpconfig -r =》 --remove param_name 通过注释掉postgresql.conf文件中的项删除配置参数。
gpconfig -l =》 --list 列出所有被gpconfig工具支持的配置参数。
gpconfig -s =》 --show param_name 显示在Greenplum数据库系统中所有实例（Master和Segment）上使用的配置参数的值。如果实例中参数值存在差异，则工具将显示错误消息。使用-s=》选项运行gpconfig将直接从数据库中读取参数值，而不是从postgresql.conf文件中读取。如果用户使用gpconfig 在所有Segment中设置配置参数，然后运行gpconfig -s来验证更改，用户仍可能会看到以前的（旧）值。用户必须重新加载配置文件（gpstop -u）或重新启动系统（gpstop -r）以使更改生效。
gpconfig --file =》 对于配置参数，显示在Greenplum数据库系统中的所有Segment（Master和Segment）上的postgresql.conf文件中的值。如果实例中的参数值存在差异，则工具会显示一个消息。必须与-s选项一起指定。
gpconfig --file-compare 对于配置参数，将当前Greenplum数据库值与主机（Master和Segment）上postgresql.conf文件中的值进行比较。
gpconfig --skipvalidation 覆盖gpconfig的系统验证检查，并允许用户对任何服务器配置参数进行操作，包括隐藏参数和gpconfig无法更改的受限参数。当与-l选项（列表）一起使用时，它显示受限参数的列表。 警告： 使用此选项设置配置参数时要格外小心。
gpconfig --verbose 在gpconfig命令执行期间显示额外的日志信息。
gpconfig --debug 设置日志输出级别为调试级别。
gpconfig -? | -h | --help 显示在线帮助。
gpstart -a => 快速启动|
gpstart -d => 指定数据目录（默认值：$MASTER_DATA_DIRECTORY）
gpstart -q => 在安静模式下运行。命令输出不显示在屏幕，但仍然写入日志文件。
gpstart -m => 以维护模式连接到Master进行目录维护。例如：$ PGOPTIONS='-c gp_session_role=utility' psql postgres
gpstart -R => 管理员连接
gpstart -v => 显示详细启动信息
gpstop -a => 快速停止
gpstop -d => 指定数据目录（默认值：$MASTER_DATA_DIRECTORY）
gpstop -m => 维护模式
gpstop -q => 在安静模式下运行。命令输出不显示在屏幕，但仍然写入日志文件。
gpstop -r => 停止所有实例，然后重启系统
gpstop -u => 重新加载配置文件 postgresql.conf 和 pg_hba.conf
gpstop -v => 显示详细启动信息
gpstop -M fast => 快速关闭。正在进行的任何事务都被中断。然后滚回去。
gpstop -M immediate => 立即关闭。正在进行的任何事务都被中止。不推荐这种关闭模式，并且在某些情况下可能导致数据库损坏需要手动恢复。
gpstop -M smart => 智能关闭。如果存在活动连接，则此命令在警告时失败。这是默认的关机模式。
gpstop --host hostname => 停用segments数据节点，不能与-m、-r、-u、-y同时使用
gprecoverseg -a => 快速恢复
gprecoverseg -i => 指定恢复文件
gprecoverseg -d => 指定数据目录
gprecoverseg -l => 指定日志文件
gprecoverseg -r => 平衡数据
gprecoverseg -s => 指定配置空间文件
gprecoverseg -o => 指定恢复配置文件
gprecoverseg -p => 指定额外的备用机
gprecoverseg -S => 指定输出配置空间文件
gpactivatestandby -d 路径 | 使用数据目录路径，默认：$MASTER_DATA_DIRECTORY
gpactivatestandby -f | 强制激活备份主机
gpactivatestandby -v | 显示此版本信息
gpinitstandby -s 备库名称 => 指定新备库
gpinitstandby -D => debug 模式
gpinitstandby -r => 移除备用机
##################################################
#创建用户
create role dbdream password 'dbdream' createdb login;
#查看用户
select * from pg_user;
#查看权限
select * from information_schema.table_privileges;
#修改文件允许远程登陆
pg_hba.conf
host all dbdream 10.9.15.20/32 md5
gpstop –u
#通过pg_roles字典开查看数据库的用户信息
select rolname,oid from pg_roles;
#查看表空间
SELECT oid,* from pg_tablespace;
#查看表空间及对应文件目录
WITH spc AS (SELECT * FROM gp_tablespace_location(24589))
SELECT seg.role, spc.gp_segment_id as seg_id, seg.hostname, seg.datadir, tblspc_loc
FROM spc, gp_segment_configuration AS seg
WHERE spc.gp_segment_id = seg.content ORDER BY seg_id;
#查看表、索引、视图对应的物理文件
select oid,relfilenode,relname from pg_class where relname = 't2';
#查看表oid
SELECT a.oid,
a.relname AS name,
b.description AS comment
FROM pg_class a
LEFT OUTER JOIN pg_description b ON b.objsubid= AND a.oid = b.objoid
WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public') --用户表一般存储在public模式下
AND a.relkind='r'
ORDER BY a.relname
#查看当前postgresql有几个数据库
SELECT datname FROM pg_database
#查看数据库大小
select pg_size_pretty(pg_database_size('gpdw'));
#查看表空间大小
select pg_size_pretty(pg_tablespace_size('pg_default'));
#查看索引大小
select pg_size_pretty(pg_indexes_size('t1'));
#查看表大小
select pg_size_pretty(pg_table_size('t2'));
#查看表索引
select
relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from
pg_stat_user_indexes
order by
idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
#查看指定schema 里所有的表大小，按从大到小的顺序排列
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
#查看表定义
SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = 't2'
and a.attnum >
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;
1、表膨胀相关查询
-- 该视图显示了那些膨胀的（在磁盘上实际的页数超过了根据表统计信息得到预期的页数）正规的堆存储的表。
select * from gp_toolkit.gp_bloat_diag;
-- 所有对象的膨胀明细
select * from gp_toolkit.gp_bloat_expected_pages;
2、表倾斜的相关信息
-- 该视图通过计算存储在每个Segment上的数据的变异系数（CV）来显示数据分布倾斜。
select * from gp_toolkit.gp_skew_coefficients;
-- 该视图通过计算在表扫描过程中系统空闲的百分比来显示数据分布倾斜，这是一种数据处理倾斜的指示器。
select * from gp_toolkit.gp_skew_idle_fractions;
3、锁查询相关的信息
-- 该视图显示了当前所有表上持有锁，以及查询关联的锁的相关联的会话信息。
select * from gp_toolkit.gp_locks_on_relation;
-- 该视图显示当前被一个资源队列持有的所有的锁，以及查询关联的锁的相关联的会话信息。
select * from gp_toolkit.gp_locks_on_resqueue;
4、日志查询相关的信息
-- 该视图使用一个外部表来读取来自整个Greenplum（Master、Segment、镜像）的服务器日志文件并且列出所有的日志项。
select * from gp_toolkit.gp_log_system;
-- 该视图用一个外部表来读取在主机上的日志文件同时报告在数据库会话中SQL命令的执行时间
select * from gp_toolkit.gp_log_command_timings;
-- 该视图使用一个外部表来读取整个Greenplum系统（主机，段，镜像）的服务器日志文件和列出与当前数据库关联的日志的入口。
select * from gp_toolkit.gp_log_database;
-- 该视图使用一个外部表读取来自Master日志文件中日志域的一个子集。
select * from gp_toolkit.gp_log_master_concise;
5、资源队列相关查询信息
-- gp_toolkit.gp_resgroup_config视图允许管理员查看资源组的当前CPU、内存和并发限制
select * from gp_toolkit.gp_resgroup_config;
-- gp_toolkit.gp_resgroup_status视图允许管理员查看资源组的状态和活动
select * from gp_toolkit.gp_resgroup_status;
-- 该视图允许管理员查看到一个负载管理资源队列的状态和活动。
select * from gp_toolkit.gp_resqueue_status;
-- 对于那些有活动负载的资源队列，该视图为每一个通过资源队列提交的活动语句显示一行。
select * from gp_toolkit.gp_resq_activity;
-- 对于有活动负载的资源队列，该视图显示了队列活动的总览。
select * from gp_toolkit.gp_resq_activity_by_queue;
-- 资源队列的执行优先级
select * from gp_toolkit.gp_resq_priority_backend;
-- 该视图为当前运行在Greenplum数据库系统上的所有语句显示资源队列优先级、会话ID以及其他信息
select * from gp_toolkit.gp_resq_priority_statement;
-- 该视图显示与角色相关的资源队列。
select * from gp_toolkit.gp_resq_role;
6、查看磁盘上(database,schema,table,indexs,view)等的占用大小的相关信息
-- 外部表在活动Segment主机上运行df（磁盘空闲）并且报告返回的结果
select * from gp_toolkit.gp_disk_free;
-- 该视图显示数据库的总大小。
select * from gp_toolkit.gp_size_of_database;
-- 该视图显示当前数据库中schema在数据中的大小
select * from gp_toolkit.gp_size_of_schema_disk;
-- 该视图显示一个表在磁盘上的大小。
select * from gp_toolkit.gp_size_of_table_disk;
-- 该视图查看表的索引
select * from gp_toolkit.gp_table_indexes;
-- 该视图显示了一个表上所有索引的总大小。
select * from gp_toolkit.gp_size_of_all_table_indexes;
-- 该视图显示分区子表及其索引在磁盘上的大小。
select * from gp_toolkit.gp_size_of_partition_and_indexes_disk;
-- 该视图显示表及其索引在磁盘上的大小。
select * from gp_toolkit.gp_size_of_table_and_indexes_disk;
-- 该视图显示表及其索引的总大小
select * from gp_toolkit.gp_size_of_table_and_indexes_licensing;
-- 该视图显示追加优化（AO）表没有压缩时的大小。
select * from gp_toolkit.gp_size_of_table_uncompressed;
7、用户使用的工作空间大小信息
-- 该视图为当前在Segment上使用磁盘空间作为工作文件的操作符包含一行。
select * from gp_toolkit.gp_workfile_entries;
-- GP工作文件管理器使用的磁盘空间
select * from gp_toolkit.gp_workfile_mgr_used_diskspace;
-- 每个查询的GP工作文件使用情况
select * from gp_toolkit.gp_workfile_usage_per_query;
-- 每个segment在GP工作文件中的使用量
select * from gp_toolkit.gp_workfile_usage_per_segment;
8、查看用户创建的信息(数据库,schema,表,索引,函数,视图)等信息
-- gp 中所有的名字(索引、表、视图、函数)等的名字
select * from gp_toolkit."__gp_fullname";
-- gp 中AO表的名字
select * from gp_toolkit."__gp_is_append_only";
-- gp 中segment的个数
select * from gp_toolkit."__gp_number_of_segments";
-- gp 中用户表的个数
select * from gp_toolkit."__gp_user_data_tables";
-- GP用户数据表可读
select * from gp_toolkit."__gp_user_data_tables_readable";
-- 用户自己创建的schema信息
select * from gp_toolkit."__gp_user_namespaces";
-- 用户自己创建的表信息
select * from gp_toolkit."__gp_user_tables";
9、系统中维护的ID信息
-- gp 本地维护的ID
select * from gp_toolkit."__gp_localid";
-- gp master外部的log信息
select * from gp_toolkit."__gp_log_master_ext";
-- gp segment外部的log信息
select * from gp_toolkit."__gp_log_segment_ext";
-- gp master 的id信息
select * from gp_toolkit."__gp_masterid";
10、系统查用的查询信息
-- 该视图显示那些没有统计信息的表，因此可能需要在表上执行ANALYZE命令。
select * from gp_toolkit.gp_stats_missing;
-- 该视图显示系统目录中被标记为down的Segment的信息。
select * from gp_toolkit.gp_pgdatabase_invalid;
-- 那些被分类为本地（local）（表示每个Segment从其自己的postgresql.conf文件中获取参数值）的服务器配置参数，应该在所有Segment上做相同的设置。
select * from gp_toolkit.gp_param_settings_seg_value_diffs;
-- 该视图显示系统中所有的角色以及指派给它们的成员（如果该角色同时也是一个组角色）。
select * from gp_toolkit.gp_roles_assigned;
11、系统中常用查询的函数
select * from gp_toolkit.gp_param_settings();
select * from gp_toolkit.gp_skew_details(oid);
select * from gp_toolkit."__gp_aocsseg"(IN oid);
select * from gp_toolkit."__gp_aovisimap"(IN oid);
select * from gp_toolkit.gp_param_setting(varchar);
select * from gp_toolkit."__gp_skew_coefficients"();
select * from gp_toolkit."__gp_workfile_entries_f"();
select * from gp_toolkit."__gp_skew_idle_fractions"();
select * from gp_toolkit."__gp_aocsseg_name"(IN text);
select * from gp_toolkit."__gp_aovisimap_name"(IN text);
select * from gp_toolkit."__gp_aocsseg_history"(IN oid);
select * from gp_toolkit."__gp_aovisimap_entry"(IN oid);
select * from gp_toolkit."__gp_aovisimap_hidden_typed"(oid);
select * from gp_toolkit."__gp_param_local_setting"(varchar);
select * from gp_toolkit."__gp_aovisimap_entry_name"(IN text);
select * from gp_toolkit."__gp_aovisimap_hidden_info"(IN oid);
select * from gp_toolkit."__gp_workfile_mgr_used_diskspace_f"();
select * from gp_toolkit."__gp_aovisimap_hidden_info_name"(IN text);
select * from gp_toolkit.gp_skew_coefficient(IN targetoid oid, OUT skcoid oid, OUT skccoeff numeric);
select * from gp_toolkit.gp_skew_idle_fraction(IN targetoid oid, OUT sifoid oid, OUT siffraction numeric);
select * from gp_toolkit.gp_bloat_diag(IN btdrelpages int4, IN btdexppages numeric, IN aotable bool, OUT bltidx int4, OUT bltdiag text);
select * from gp_toolkit."__gp_aovisimap_compaction_info"(IN ao_oid oid, OUT content int4, OUT datafile int4, OUT compaction_possible bool, OUT hidden_tupcount int8, OUT total_tupcount int8, OUT percent_hidden numeric);
--查询某schema下所有函数的属主
SELECT n.nspname AS "Schema",p.proname AS "Name",pg_catalog.pg_get_userbyid(p.proowner) AS "Owner"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname='dwbi02' ORDER BY 2;
--查询某schema下所有函数的详细信息
SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(p.proowner) AS "Owner",
CASE WHEN proallarg* IS NOT NULL THEN
pg_catalog.array_to_string(ARRAY(
SELECT
CASE
WHEN p.proargmodes[s.i] = 'i' THEN ''
WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '
END ||
CASE
WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
ELSE p.proargnames[s.i] || ' '
END ||
pg_catalog.format_type(p.proallarg*[s.i], NULL)
FROM
pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallarg*, 1)) AS s(i)
), ', ')
ELSE
pg_catalog.array_to_string(ARRAY(
SELECT
CASE
WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''
ELSE p.proargnames[s.i+1] || ' '
END ||
pg_catalog.format_type(p.proarg*[s.i], NULL)
FROM
pg_catalog.generate_series(, pg_catalog.array_upper(p.proarg*, 1)) AS s(i)
), ', ')
END AS "data_*"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE 1=1
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname = 'dmc' order by 2,3;
--某schema下，表数量统计（正则过滤分区表子分区）
SELECT count(*) FROM pg_tables WHERE tablename !~ '.*_1+_prt_.*$'AND schemaname = 'ctl';
--查询一个库下各schema占用的空间
SELECT pg_size_pretty(cast( sum(pg_total_relation_size( schemaname || '.' || tablename)) AS bigint)), schemaname
FROM pg_tables t INNER JOIN pg_namespace d ON t.schemaname=d.nspname GROUP BY schemaname;
SELECT pg_size_pretty(cast( sum(pg_total_relation_size( schemaname || '.' || tablename)) AS bigint)), schemaname
FROM pg_tables t INNER JOIN pg_namespace d ON t.schemaname=d.nspname WHERE schemaname in ('mad','mad_ods','dwbi_ods','coupon_ods') GROUP BY schemaname;
SELECT pg_size_pretty(cast( sum(pg_total_relation_size( schemaname || '.' || tablename)) AS bigint)), schemaname
FROM pg_tables t INNER JOIN pg_namespace d ON t.schemaname=d.nspname WHERE schemaname = 'mad' GROUP BY schemaname;
--统计某张表的分区数量
SELECT p.schemaname, p.tablename, count(*) prt_count
FROM pg_partitions p
WHERE p.tablename='表名（不含schema名称）'
GROUP BY 1,2
ORDER BY 2;
--统计AO分区表数量（不含子表）
SELECT count(*) FROM pg_class c
LEFT JOIN pg_namespace n
ON c.relnamespace = n.oid
WHERE relstorage IN ('c','a')
AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule);
--统计AO表分区数量
SELECT count(*) FROM pg_class c
LEFT JOIN pg_namespace n
ON c.relnamespace = n.oid
WHERE relstorage IN ('c','a')
AND c.oid IN(SELECT parchildrelid FROM pg_partition_rule);
--查询分区数量大于100的AO表
SELECT * FROM
(
SELECT n.nspname, c.relname, count(*) part_count FROM pg_class c
LEFT JOIN pg_namespace n
ON c.relnamespace = n.oid
INNER JOIN pg_partitions p
ON p.tablename=c.relname
AND p.schemaname=n.nspname
WHERE c.relstorage IN ('c','a')
AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule)
GROUP BY n.nspname, c.relname
) a
WHERE part_count >= 100
ORDER BY 3 DESC;
--统计分区表大小（不含索引）
SELECT p.schemaname,p.tablename,sum(sotdsize)
FROM pg_partitions p LEFT JOIN (SELECT autnspname, autrelname, sotdsize FROM gp_toolkit.__gp_user_tables a, gp_toolkit.gp_size_of_table_disk b
WHERE a.autoid = b.sotdoid) b on p.partitionschemaname = b.autnspname AND p.partitiontablename = b.autrelname
GROUP BY 1,2
ORDER BY 1,2;
--统计某分区表大小（含索引）
SELECT p.schemaname, p.tablename, round(sum(pg_total_relation_size(p.schemaname || '.' || p.partitiontablename))/1024/1024) "MB"
FROM pg_partitions p
WHERE p.tablename='employee_daily'
GROUP BY 1,2
ORDER BY 2;
--统计某schema下各分区表大小（含索引）
SELECT p.schemaname, p.tablename, round(sum(pg_total_relation_size(p.schemaname || '.' || p.partitiontablename))/1024/1024) "MB"
FROM pg_partitions p
WHERE p.schemaname ='mad'
GROUP BY 1,2
ORDER BY 2;
--查询所有非分区表名
SELECT t.schemaname, t.tablename
FROM pg_tables t, pg_partitions p
WHERE t.tablename <> p.tablename AND t.tablename <> p.partitiontablename AND t.schemaname='mad'
GROUP BY 1,2
ORDER BY 2;
--查询所有表名（不含分区表子表）
SELECT n.nspname,c.relname
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid AND n.nspname NOT LIKE 'pg_%' AND n.nspname NOT LIKE 'gp_%'
AND n.nspname <> 'information_schema' AND relkind IN('r') AND relstorage <> 'x'
AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule);
--Greenplum查询争用（表锁）
SELECT l.locktype, l.database, c.relname, l.relation, l.transactionid, 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;
--用函数杀SQL
-------------------------------------
--一般查询SQL
SELECT pg_cancel_backend(procpid);
--其他SQL
SELECT pg_terminate_backend(procpid);
-------------------------------------
--查询某张表的数据分布均衡性
SELECT gp_segment_id,count(*) FROM schemaname.tablename GROUP BY gp_segment_id ORDER BY count(*) DESC;
--查询数据分布均匀性
SELECT * FROM gp_toolkit.gp_skew_idle_fractions; --siffraction字段值大于0.1的需要重新选择分布键
--保证全库统计信息是新的情况下，查询表膨胀情况
SELECT * FROM gp_toolkit.gp_bloat_diag; --bdidiag字段内容显示膨胀程度
--查询用户情况
SELECT rolname,rolsuper,rolinherit,rolcreaterole,rolcanlogin,rolconnlimit,rolresqueue,oid FROM pg_roles ORDER BY 8;
--查询所有外部表
SELECT n.nspname,c.relname
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid AND n.nspname NOT LIKE 'pg_%' AND n.nspname NOT LIKE 'gp_%'
AND n.nspname <> 'information_schema' AND relkind IN('r') AND relstorage = 'x'
AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule);
--重组表 消除膨胀
\d+ dwbi01_ods.boh__bohrscsales
ALTER TABLE dwbi01_ods.boh__bohrscsales SET WITH (REORGANIZE=TRUE) DISTRIBUTED RANDOMLY;
ALTER TABLE dwbi01_ods.boh__bohrscsales SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (transaction_code);
ANALYZE dwbi01_ods.boh__bohrscsales;
--检测字段是否适合用作分布键
select COUNT(bizdate), gp_segment_id from dwbi01_ods.boh__bohrscsales group by 2;
--查询角色配置情况（使用资源组时）
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcanlogin, rsgname FROM pg_roles, pg_resgroup WHERE pg_roles.rolresgroup=pg_resgroup.oid;
--查询膨胀率大于20的AO表
SELECT nspname, relname, AVG(percent_hidden)
FROM
(
SELECT n.nspname, c.relname, (gp_toolkit.__gp_aovisimap_compaction_info(c.oid)).percent_hidden percent_hidden
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid
AND relstorage IN ('c','a')
AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule)
) t
WHERE t.percent_hidden > 20
GROUP BY nspname, relname
ORDER BY 3 DESC;
--查询溢出文件大小（详细）
select * from gp_toolkit.gp_workfile_entries;
--查询溢出文件大小
select * from gp_toolkit.gp_workfile_usage_per_query;
--查询每个Segment上的溢出文件大小
select * from gp_toolkit.gp_workfile_usage_per_segment;
--查询表的分布键
SELECT att.nspname,att.relname,string_agg (a.attname, ',') attby
FROM
(
SELECT c.oid,n.nspname,c.relname,regexp_split_to_table (array_to_string (d.attrnums, ','),',')::int as attnu
FROM gp_distribution_policy d
LEFT JOIN pg_class c ON c.oid = d.localoid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = 'ods.oracletest'::regclass
) att
LEFT JOIN pg_attribute a ON a.attrelid = att.oid
WHERE att.attnu = a.attnum
GROUP BY 1,2;
--查询前20张大表
SELECT tabs.nspname AS schema_name,
COALESCE(parts.tablename, tabs.relname) AS table_name,
ROUND(SUM(sotaidtablesize) / 1024 / 1024, 3) AS table_MB,
ROUND(SUM(sotaidtablesize) / 1024 / 1024 / 1024, 3) AS table_GB,
ROUND(SUM(sotaididxsize) / 1024 / 1024 / 1024, 3) AS index_GB,
ROUND(SUM(sotaididxsize) / 1024 / 1024, 3) AS index_MB
FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotd,
(
SELECT c.oid, c.relname, n.nspname
FROM pg_class c, pg_namespace n
WHERE n.oid = c.relnamespace
AND c.relname NOT LIKE '%_err'
) tabs
LEFT JOIN pg_partitions parts
ON tabs.nspname = parts.schemaname
AND tabs.relname = parts.partitiontablename
WHERE sotd .sotaidoid = tabs.oid
GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)
ORDER BY 4 DESC
LIMIT 20;
--资源队列属性视图
SELECT * FROM pg_catalog.pg_resqueue_attributes;
--查询Filespace和Locating
SELECT a.dbid,a.content,a.role,a.port,a.hostname,b.fsname,c.fselocation FROM gp_segment_configuration a,pg_filespace b,pg_filespace_entry c WHERE a.dbid=c.fsedbid AND b.oid=c.fsefsoid ORDER BY 2,6;
--日期计算：
riqi::date + interval '1 month' --"riqi"增加1月
riqi::date - interval '1 day' --"riqi"减去1天
1.后分析或真空或创建表或等...
Select * from pg_stat_operations where schemaname='SCHEMA NAME '
and actionname in ('ANALYZE','VACUUM') order by statime;
2.长时间查询空闲：
Select * from pg_stat_activity order by query_start,backend_start;
gpdb=# Select * from pg_stat_activity order by query_start,backend_start;
datid | datname | procpid | sess_id | usesysid | usename |
current_query | waiting | query_start
| backend_start | client_addr | client_port | application_name
| xact_start | waiting_reason | rsgid | rsgname | rsgqueueduratio
n
-------+-----------+---------+---------+----------+---------+-----------------------
---------------------------------------------+---------+----------------------------
---+-------------------------------+---------------+-------------+------------------
+-------------------------------+----------------+-------+---------+----------------
--
| gpperfmon | 31604 | 2822 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:19:40.079557-
| 2019-03-19 05:45:25.082823-04 | 192.168.0.221 | 62596 | gpcc
| | | | unknown |
| gpperfmon | 7652 | 207 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:19:45.883945-
| 2019-03-19 01:52:55.080215-04 | 192.168.0.221 | 45824 | gpcc
| | | | unknown |
| gpdb | 6961 | 27890 | 10 | gpadmin | Select * from pg_stat_
activity order by query_start,backend_start; | f | 2019-03-20 21:19:47.667488-
| 2019-03-20 21:19:20.171974-04 | | -1 | psql
| 2019-03-20 21:19:47.667488-04 | | | unknown |
(3 rows)
3.如何在数据库中找到大的表？
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
gpdb=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
----------------------------------------------------------------+----------
test_1 | 1672
test_index_1 | 1672
test99 | 1000
gp_disk_free | 1000
__gp_log_segment_ext | 1000
t1 | 1000
__gp_localid | 1000
__gp_masterid | 1000
__gp_log_master_ext | 1000
test_index_1_idx | 56
pg_proc | 20
pg_rewrite | 19
pg_attribute | 14
pg_depend | 14
pg_depend_reference_index | 13
pg_depend_depender_index | 13
pg_proc_proname_args_nsp_index | 9
test66 | 8
pg_statistic | 6
gp_persistent_relation_node | 6
pg_description | 6
pg_attribute_relid_attnam_index | 6
pg_attribute_relid_attnum_index | 5
pg_description_o_c_o_index | 5
test1 | 5
pg_proc_oid_index | 5
test2 | 4
test110 | 4
pg_operator | 4
gpcrondump_history | 3
pg_type | 3
4.数据库中的前5个大表
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5;
gpdb=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5;
relname | relpages
---------------------+----------
test_index_1 | 1672
test_1 | 1672
__gp_localid | 1000
__gp_masterid | 1000
__gp_log_master_ext | 1000
(5 rows)
relname– 关系/表的名称.
relpages - 关系页面（页数，默认情况下页面为8kb）
pg_class– 系统表，维护关系的细节
limit 5 – 限制输出只显示5行。
5.如何计算磁盘中的数据库大小？
SELECT pg_database_size('Database Name' );
SELECT pg_size_pretty(pg_database_size( 'Database Name' ));
gpdb=# SELECT pg_database_size('gpdb' );
pg_database_size
------------------
(1 row)
gpdb=# SELECT pg_database_size('postgres' );
pg_database_size
------------------
(1 row)
gpdb=# SELECT pg_size_pretty(pg_database_size( 'gpdb' ));
pg_size_pretty
----------------
MB
(1 row)
gpdb=# SELECT pg_size_pretty(pg_database_size( 'postgres' ));
pg_size_pretty
----------------
MB
(1 row)
6.如何计算磁盘中的表大小？
SELECT pg_size_pretty(pg_total_relation_size('public.test1'));
gpdb=# SELECT pg_size_pretty(pg_total_relation_size('public.test1'));
pg_size_pretty
----------------
kB
(1 row)
7.如何查找表的大小（不包括索引）？
SELECT pg_size_pretty(pg_relation_size('public.test1'));
gpdb=# SELECT pg_size_pretty(pg_relation_size('public.test1'));
pg_size_pretty
----------------
kB
(1 row)
8.如何生成一系列数字并将其插入表格中？
INSERT INTO test2 (id) VALUES ( generate_series(1,1000));
gpdb=# INSERT INTO test3 (id) VALUES ( generate_series(1,1000));
INSERT 1000
gpdb=# SELECT * from test3 limit 3;
id | name
----+------
|
|
|
(3 rows)
9.如何计算表中的总行数？
select count(*) from test1;
gpdb=# select count(*) from test2;
count
-------
(1 row)
10.具有特定列值的行总数不为空。
select count(id) from public.test1;
gpdb=# select count(id) from public.test1;
count
-------
(1 row)
11.如何获取表中列的大值？ - 秒
select max( col_name) from table ;
SELECT MIN(col name) from table_namewhere num > (select MIN(col
name) from table_name);
12.如何获得表中列的第二个小值？ - 个和第二个
select min(col_name) from table;
SELECT MIN(col name) from table_namewhere num > (select MIN(col
name) from table_name);
13.如何查看GP中的基本可用数据类型。
SELECT typname,typlen from pg_type where typtype='b';
typname – 数据类型的名称
typlen – 数据类型的长度
gpdb=# SELECT typname,typlen from pg_type where typtype='b';
typname | typlen
---------------------+--------
bool | 1
bytea | -1
char | 1
name | 64
int8 | 8
int2 | 2
int2vector | -1
int4 | 4
regproc | 4
text | -1
oid | 4
tid | 6
xid | 4
cid | 4
oidvector | -1
xml | -1
_xml | -1
json | -1
_json | -1
complex | 16
_complex | -1
smgr | 2
point | 16
lseg | 32
path | -1
box | 32
polygon | -1
line | 32
_line | -1
float4 | 4
float8 | 8
abstime | 4
reltime | 4
14.显示已关闭的segments。
Select * from gp_segment_configuration where status='d';
15.查找当前用户：
SELECT SESSION_USER, CURRENT_USER;
gpdb=# SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
--------------+--------------
gpadmin | gpadmin
(1 row)
16.检查活动会话（工作负载）：
SELECT * FROM pg_stat_activity;
gpdb=# SELECT * FROM pg_stat_activity;
datid | datname | procpid | sess_id | usesysid | usename | current_query
| waiting | query_start | backend_start
| client_addr | client_port | application_name | xact_start | w
aiting_reason | rsgid | rsgname | rsgqueueduration
-------+-----------+---------+---------+----------+---------+-----------------------
----------+---------+-------------------------------+-------------------------------
+---------------+-------------+------------------+-------------------------------+--
--------------+-------+---------+------------------
| gpperfmon | 7652 | 207 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:28:10.079528-04 | 2019-03-19 01:52:55.080215-04
| 192.168.0.221 | 45824 | gpcc | |
| | unknown |
| gpperfmon | 31604 | 2822 | 16558 | gpmon | <IDLE>
| f | 2019-03-20 21:28:25.079502-04 | 2019-03-19 05:45:25.082823-04
| 192.168.0.221 | 62596 | gpcc | |
| | unknown |
| gpdb | 6961 | 27890 | 10 | gpadmin | SELECT * FROM pg_stat_
activity; | f | 2019-03-20 21:28:29.916239-04 | 2019-03-20 21:19:20.171974-04
| | -1 | psql | 2019-03-20 21:28:29.916239-04 |
| | unknown |
(3 rows)
17.正在队列中等待的查询
SELECT * FROM gp_tookit.gp_resqueue_status;
18.查看数据库列表
SELECT datname from pg_database;
gpdb=# SELECT datname from pg_database;
datname
-----------
template1
template0
postgres
gpdb
gpperfmon
test2
(6 rows)
1.如何获取查询运行时和已用时间。
例子:
Select tstart, tfinish, (tfinish-tstart) as total_time, trim(query_text)
from queries_history
Where tstart >= '2011-07-07 11:00:00' and tstart < '2011-07-07 13:00:00'
and db ='yourdatabasename'
Order by tstart;
2。
*查看上次执行的操作
gpdb=# SELECT schemaname as schema, objname as table, usename as role, actionname as action,
gpdb-# subtype as type, statime as time
gpdb-# FROM pg_stat_operations
gpdb-# WHERE objname='cust';
schema | table | role | action | type | time
--------+-------+------+--------+------+------
( rows)
*后分析或创建表或ETC...
Select * from pg_stat_operations
where schemaname='SCHEMA NAME '
and actionname in ('ANALYZE','VACUUM')
order by statime;
gpdb=# Select * from pg_stat_operations
gpdb-# where schemaname='SCHEMA NAME '
gpdb-# and actionname in ('ANALYZE','VACUUM')
gpdb-# order by statime;
classname | objname | objid | schemaname | usestatus | usename | actionname | subty
pe | statime
-----------+---------+-------+------------+-----------+---------+------------+------
---+---------
( rows)
3.显示已关闭的细分。
select * from gp_segment_configuration where status='d';
4.如何计算磁盘中的数据库大小？
select pg_size_pretty(pg_database_size('test1'));
5.如何计算磁盘中的表大小？
select pg_size_pretty(pg_relation_size('gpdb' ));
6.对于分区表，您需要使用下面的sql来查找累积表大小
select sum(pg_total_relation_size(tablename)) from pg_tables where tablename like 'table_name%' ;
7。
*授予/撤消模式中对象的权限的功能
*撤销模式对象（表，视图，序列）权限的功能
8. GreenPlum数据库GUI工具
9.您可能喜欢的数据偏斜查询
gpdb=# select gp_segment_id, count(*) , abs(count(*) - avg(count(*)) over(order by gp_segment_id rowsbetween unbounded preceding and unbounded following))/count(*) skewfrom test1group by gp_segment_idorder by skew desclimit 10;
gp_segment_id | count | skew
---------------+-------+------------------------
| 2 | 0.40000000000000000000
| 1 | 0.20000000000000000000
| 1 | 0.20000000000000000000
| 1 | 0.20000000000000000000
| 1 | 0.20000000000000000000
(5 rows)
10。
*segments
gpdb=# SELECT count(*)::smallint AS numsegments FROM gp_segment_configurationWHERE gp_segment_configuration.preferred_role = 'p'::"char" ANDgp_segment_configuration.content >= ;
numsegments
-------------
(1 row)
*segments的大小
gpdb=# SELECT pg_size_pretty(dfspace)FROM gp_toolkit.gp_disk_freeORDER BY dfsegment;
pg_size_pretty
----------------
MB
MB
MB
MB
MB
MB
MB
MB
(8 rows)
11.确定当前的主段配置
gpdb=# SELECT dbid, content, address as host_address, port, replication_port, fselocation as datadirFROM gp_segment_configuration, pg_filespace_entryWHERE dbid=fsedbidORDER BY dbid;
dbid | content | host_address | port | replication_port | datadir
------+---------+--------------+-------+------------------+-------------------------
--------
| -1 | greenplum01 | 5432 | | /greenplum/data/master/g
pseg-1
| | greenplum02 | 6000 | 34000 | /greenplum/data/primary/
gpseg0
| 1 | greenplum02 | 6001 | 34001 | /greenplum/data/primary/
gpseg1
| 2 | greenplum02 | 6002 | 34002 | /greenplum/data2/primary
/gpseg2
| 3 | greenplum02 | 6003 | 34003 | /greenplum/data2/primary
/gpseg3
| 4 | greenplum03 | 6000 | 34000 | /greenplum/data/primary/
gpseg4
| 5 | greenplum03 | 6001 | 34001 | /greenplum/data/primary/
gpseg5
| 6 | greenplum03 | 6002 | 34002 | /greenplum/data2/primary
/gpseg6
| 7 | greenplum03 | 6003 | 34003 | /greenplum/data2/primary
/gpseg7
| | greenplum03 | 43000 | 44000 | /greenplum/data/mirror/g
pseg0
| 1 | greenplum03 | 43001 | 44001 | /greenplum/data/mirror/g
pseg1
| 2 | greenplum03 | 43002 | 44002 | /greenplum/data2/mirror/
gpseg2
| 3 | greenplum03 | 43003 | 44003 | /greenplum/data2/mirror/
gpseg3
| 4 | greenplum02 | 43000 | 44000 | /greenplum/data/mirror/g
pseg4
| 5 | greenplum02 | 43001 | 44001 | /greenplum/data/mirror/g
pseg5
| 6 | greenplum02 | 43002 | 44002 | /greenplum/data2/mirror/
12.时区设置：
gpdb=# BEGIN;
gpdb=# SELECT NOW();
gpdb=# SET timezone TO '-8';
gpdb=# SELECT NOW();
13.Re-create GP Toolkit objects:本文来源：https://blog.csdn.net/qq_42979842/article/details/108553571
相关文章