Greenplum 操作锦囊

说明

本文描述问题及解决方法基于 腾讯云数据仓库 TCHouse-P（ Tencent Cloud House-P，TCHouse-P ）。

背景

在大数据领域，Greenplum 是一个广泛使用的开源数据仓库工具，特别受到数据分析师、数据科学家和企业的青睐，因为它能够提供高效、高性能的数据分析功能。然而，掌握 Greenplum 的操作技巧并非易事，为了让用户更快速地掌握这些技巧，本文将为您提供 Greenplum 操作的锦囊妙计。希望通过本文，您能够快速熟悉 Greenplum，提升工作效率和数据分析能力。

查询类

1. 查看参数修改在什么样的情况下生效

代码语言：javascript复制

select name, context from pg_settings;

2. 查看GP session情况

代码语言：javascript复制

select * from pg_stat_activity where  current_query != '<IDLE>';
select * from pg_stat_activity where  current_query != '<IDLE>' and now() - query_start > '1 hour';(运行是否超过1小时)

3. 查看base目录下数字与实体的联系

base下路径：base/dboid/relfilenode，其中dbid可以通过下面语句查看是哪一个数据库

代码语言：javascript复制

select oid, datname from pg_database where oid = xxxx(dboid)
select oid, datname from pg_database where datname = '{dbname}'

其中relfilenode可以通过下面语句查看是哪一张表(这个需要切到对应数据库下面运行)

代码语言：javascript复制

select a.relname, b.nspname from pg_class a join pg_namespace b on a.relnamespace = b.oid where a.relfilenode = xxxx(relfilenode);
select a.relfilenode, a.relname, b.nspname from pg_class a join pg_namespace b on a.relnamespace = b.oid where a.relname = '{tablename}'

4. 查看当前数据库实例下所有的表

代码语言：javascript复制

SELECT tablename FROM pg_tables  

5. 查看当前数据库实例下所有的schema

代码语言：javascript复制

select * from information_schema.schemata;

6. schema相关

查看当前的schema

代码语言：javascript复制

SHOW search_path

更改当前schema

代码语言：javascript复制

SET search_path TO ***

7. 查看数据分布情况

是否有数据倾斜，还可以带条件查看

代码语言：javascript复制

select gp_segment_id,count(1) from {tablename} group by 1; 
SELECT gp_segment_id, count(*) FROM table_name WHERE column='value' GROUP BY gp_segment_id;

8. 查看gp server的版本

代码语言：javascript复制

show gp_server_version; 
select version();

9. 查看所有role和user

代码语言：javascript复制

SELECT rolname from pg_roles ;
SELECT usename from pg_user;

10. 查看集群分布

代码语言：javascript复制

SELECT * FROM gp_segment_configuration;

其中content相同的是一对，比如主备，segment从0-N-1，N就是segment的数量

role代表角色，P是主，M是备

preferred_role是之前的角色， 如果和role不一致，代表有主备切换

status代表状态，d是down了，s是正常

11. 查看所有数据库相关大小

查看所有数据库大小列表

代码语言：javascript复制

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

查看某指定数据库的大小

代码语言：javascript复制

select pg_size_pretty(pg_database_size('{DATBASE_NAME}'));

查看schema大小

代码语言：javascript复制

select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='{SCHEMANAME}' group by 1;

查看表的大小

普通表：

代码语言：javascript复制

select pg_size_pretty(pg_relation_size('relation_name')); -- pg_relation_size返回byte大小，pg_size_pretty转化为可读的单位
select pg_size_pretty(pg_total_relation_size('relation_name'));  -- 查看表的总大小，包括索引这些等

分区表：

代码语言：javascript复制

select schemaname,tablename,round(sum(pg_total_relation_size(schemaname||'.'||partitiontablename))/1024/1024) "MB" from pg_partitions where tablename='catalog_returns' group by 1,2;

查看各个segment所在磁盘剩余大小(in kilobytes)

代码语言：javascript复制

SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;

按大小对表排序

代码语言：javascript复制

SELECT 
table_schema || '.' || table_name 
AS table_full_name, pg_size_pretty(pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size
FROM 
information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC limit 20

12. 不登录数据库，运行sql

指定sql文件运行

代码语言：javascript复制

psql -p 20001 -d etl3d -f /home/gpadmincloud/install/TPC-DS/05_sql/179.tpcds.79.sql

直接在命令行中指定sql

代码语言：javascript复制

psql -c "sql语句"　-h 主机 -p 端口 -U 用户名  -d 数据库名

13. 查询表的创建时间

代码语言：javascript复制

select statime,stausename from pg_stat_last_operation where classid = 'pg_class'::regclass and objid = 'schemaname.tablename'::regclass and staactionname = 'CREATE';

14. 查看当前系统有那些extenstion可以loading

代码语言：javascript复制

select * from  pg_available_extensions;
select * from  pg_available_extension_versions;

15. 查看当前登录的用户

代码语言：javascript复制

select user

16. 显示哪些没有统计信息且可能需要ANALYZE的表

代码语言：javascript复制

SELECT * from gp_toolkit.gp_stats_missing;

注：空表会一直在里面

17. 查询某个用户对某个表有什么权限

代码语言：javascript复制

select * from INFORMATION_SCHEMA.role_table_grants where grantee='user_name' and table_name='table';

18. 登录QE_Primary

代码语言：javascript复制

env PGOPTIONS="-c gp_session_role=utility" psql -p 40000 -d postgres

19. 查看每个segment部署的路径

代码语言：javascript复制

select * from pg_filespace_entry

20. 在命令行中指定密码登录数据库

代码语言：javascript复制

PGPASSWORD=lambpwd11 psql -h10.0.6.43 -p5436 -Ulambuser -d testdb

21. 查看db中创建的extension

代码语言：javascript复制

select * from pg_extension

22. 查询某条数据在哪个segment

这里面的gp_segment_id对应的是gp_segment_configuration的content值

代码语言：javascript复制

select gp_segment_id,* from alldata.t_userinfo where uid = 462324573;

23. 查询对象操作

数据库对象（表，索引，视图等）以及全局对象（role）等的操作（CREATE，ANALYZE等）

代码语言：javascript复制

select objname,actionname,statime from pg_stat_operations  where objname like 'sales';

24. 命令 d 解释

代码语言：javascript复制

dS : 系统表也会展示出来
dT name : 查看自定义数据类型,比如rolname和datname的类型是name
df {func_name} : 查看函数
dn : 查看schame
di : 查看index 

25. 查看触发器

代码语言：javascript复制

SELECT * FROM pg_trigger

26. 查看对象是否被锁住

最常见是表

代码语言：javascript复制

select * from pg_locks where relation = '39964457'; 

其中relation是下面的oid

代码语言：javascript复制

select oid,* from pg_class where relname = '{table_name}'

27. 查看sql卡住的task

特别是alter table相关的sql，先在master查看是否有锁

代码语言：javascript复制

select * from pg_stat_activity;

然后看是否有相互等待的session

代码语言：javascript复制

SELECT * FROM pg_catalog.gp_dist_wait_status();

然后在每个segment上看是否有锁

-- 这里有可能出现segment上有没有kill干净的sql在继续跑，导致在segment上语句加上锁了

代码语言：javascript复制

select * from pg_stat_activity
select * from pg_locks where pid = {xxx}（这里的pid就是pg_stat_activity的procpid） 
select * from pg_class where oid = {xxx} (这里的oid就是pg_locks的relation字段，如果locktype是relation)

28. 查看表的存储格式

代码语言：javascript复制

select relstorage from pg_class where relname = '{table_name}';  
 a  -- 行存储AO表  
 h  -- heap堆表、索引  
 x  -- 外部表(external table)  
 v  -- 视图  
 c  -- 列存储AO表  

29. 处理数据膨胀

代码语言：javascript复制

select prosrc from pg_proc where proname = '{function_name}'

30. 查看函数内容

代码语言：javascript复制

select prosrc from pg_proc where proname = '{function_name}'

31. 查看分区表相关内容

代码语言：javascript复制

select * from pg_partitions where partitiontablename = 'test_range_partition_1_prt_p1';

32. 设置各种级别的参数

tablespace与filespace

查看集群tablespace与filespace的对应关系，以及路径

代码语言：javascript复制

SELECT
	spcname as tblspc
	,fsname as filespc
	,fsedbid as seg_dbid
	,fselocation as datadir
FROM pg_tablespace pgts
	,pg_filespace pgfs
	,pg_filespace_entry pgfse
WHERE pgts.spcfsoid=pgfse.fsefsoid
AND pgfse.fsefsoid=pgfs.oid
ORDER BY tblspc, seg_dbid

查看某张表使用的tablespace

代码语言：javascript复制

select * from pg_tables where tablename='xxxx';

集群监控类

1. 查看workfile使用情况

代码语言：javascript复制

select * from gp_toolkit.gp_workfile_entries ：contains one row for each operator using disk space for workfiles on a segment at the current time.
select * from gp_toolkit.gp_workfile_usage_per_query  : contains one row for each query using disk space for workfiles on a segment at the current time.
select * from gp_toolkit.gp_workfile_usage_per_segment  : contains one row for each segment.
Each row displays the total amount of disk space used for workfiles on the segment at the current time.

2. 查看session消耗的虚拟内存

需要先创建一个view

代码语言：javascript复制

psql -d postgres -f /home/gpadmincloud/install/share/postgresql/contrib/gp_session_state.sql

然后

代码语言：javascript复制

select * from session_state.session_level_memory_consumption

3. 查看数据膨胀情况

对于heap表，可以通过以下方式查看是否有膨胀

代码语言：javascript复制

select * from gp_toolkit.gp_bloat_diag where bdirelname = '{table_name}';
select * from gp_toolkit.gp_bloat_diag limit 10;
-- 注意这个表也是离线更新的，可能不是很及时，analyze会有助于加快速度

对于AO表

代码语言：javascript复制

select * from gp_toolkit.__gp_aovisimap_compaction_info('{table_name}'::regclass);

资源队列类

1. 查看资源队列的配置

代码语言：javascript复制

select * from pg_resqueue_attributes;

2. 查看资源队列的使用情况

代码语言：javascript复制

SELECT * FROM gp_toolkit.gp_resqueue_status;

3. 查看资源队列中的等待查询

代码语言：javascript复制

SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

4. 修改资源队列的配置，只能superuser

代码语言：javascript复制

alter resource queue pg_default with(ACTIVE_STATEMENTS=30)

操作类

1. 启停

在master节点上的install目录

代码语言：javascript复制

export MASTER_DATA_DIRECTORY=/data/greenplum/master/gpseg-1

停止

代码语言：javascript复制

gpstop
gpstop -M fast（所有连接直接kill掉）

启动

代码语言：javascript复制

gpstart

2. 修改配置

在master节点上的install目录

代码语言：javascript复制

gpconfig -c log_min_duration_statement -v 3600
gpconfig -c log_statement -v ddl（注意字符串的话需要双引号加单引号）
-- 注意如果配置的参数有错误，可能会导致之后的参数都不生效，比如gpconfig -c log_min_duration_statement -v 3600000
-- 修改后更加参数不同，生效的方式也不一样，有些需要重启gp，有些只需要重新加载配置等

3. 安全模式

有时候集群无法启动，比如修改了配置，但是配置超过了限制，如下错误

代码语言：javascript复制

2018-07-10 09:18:16.187637 GMT,,,p18969,th1177327744,,,,0,,,seg-1,,,,,"FATAL","22023","2500 is outside the valid range for parameter ""max_prepared_transactions"" (1 .. 1000)",,,,,,,,"set_config_option","guc.c",4892

这个时候需要重新修改配置，就以安全模式只启动master(gpstart -m)，然后调用gpconfig修改参数

如果要连接master，需要使用：

代码语言：javascript复制

PGOPTIONS='-c gp_session_role=utility' psql 

4. 取消查询或者关闭session

代码语言：javascript复制

select pg_cancel_backend(procpid) 活跃查询
select  pg_terminate_backend(procpid) idle

5. 将standby master升主

在standy master上，export以下2个环境变量

代码语言：javascript复制

export MASTER_DATA_DIRECTORY=/data/greenplum/master/gpseg-1
export PGPORT=5432
./gpactivatestandby -d /data/greenplum/master/gpseg-1

6. 修改pg_hba.conf

配置客户端认证，只需要修改master上的pg_hba.conf，注意其中的地址使用的是CIDR格式，也就是如果指定某一个ip

需要写成.../32,然后gpstop -u

注意：gpstop命令非常耗内存（与segment个数有关），这里修改master节点hba文件，理论上只需要加载mater即可，也可以使用

代码语言：javascript复制

pg_ctl reload -D /data/greenplum/master/gpseg-1 

7. 恢复集群

代码语言：javascript复制

gprecoverseg -Fo ./recv 
gprecoverseg -Fi ./recv 

8. 将集群role与preferred_role切回相等状态

To rebalance the cluster and bring all the segments into their preferred role

代码语言：javascript复制

gprecoverseg -r

9. 恢复standby master

代码语言：javascript复制

gpinitstandby -n

数据导出导入

1. gp_dump

这个是分布式导出，会在每个segment下固定位置导出对应文件

代码语言：javascript复制

env PGDATABASE="postgres" gp_dump -s -n public -p 20001 -U postgres 

2. clone一个数据库

包括数据，注意old_dbname需要没有连接

代码语言：javascript复制

CREATE DATABASE new_dbname TEMPLATE old_dbname;

3. 导出部分数据

需要在master上运行

代码语言：javascript复制

copy (select * from {table_name} limit 100) to '{local_path}' csv
copy {table_name} from '{local_path}' csv;  

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！

腾讯云数据仓库TCHouse-P 2023腾讯·技术创作特训营第三期 大数据解决方案 greenplum olap

0 人点赞