Greenplum 操作锦囊

select name, context from pg_settings;

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小时)

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

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}'

SELECT tablename FROM pg_tables  

select * from information_schema.schemata;

SHOW search_path

SET search_path TO ***

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;

show gp_server_version; 
select version();

SELECT rolname from pg_roles ;
SELECT usename from pg_user;

SELECT * FROM gp_segment_configuration;

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

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

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

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'));  -- 查看表的总大小，包括索引这些等

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;

SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;

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

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

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

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

select * from  pg_available_extensions;
select * from  pg_available_extension_versions;

select user

SELECT * from gp_toolkit.gp_stats_missing;

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

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

select * from pg_filespace_entry

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

select * from pg_extension

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

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

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

SELECT * FROM pg_trigger

select * from pg_locks where relation = '39964457'; 

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

select * from pg_stat_activity;

SELECT * FROM pg_catalog.gp_dist_wait_status();

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)

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

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

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

select * from pg_partitions where partitiontablename = 'test_range_partition_1_prt_p1';

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

select * from pg_tables where tablename='xxxx';

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.

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

select * from session_state.session_level_memory_consumption

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

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

select * from pg_resqueue_attributes;

SELECT * FROM gp_toolkit.gp_resqueue_status;

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

alter resource queue pg_default with(ACTIVE_STATEMENTS=30)

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

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

gpstart

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

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

PGOPTIONS='-c gp_session_role=utility' psql 

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

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

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

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

gprecoverseg -r

gpinitstandby -n

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

CREATE DATABASE new_dbname TEMPLATE old_dbname;

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