GP数据库系统管理语句

gpconfig -s max_connections;--查看最大连接数
psql -h 10.255.235.96 -p 5432 -d ac -U gpconnect
select  count(*) from pg_stat_activity;--查看连接数据库的活跃会话数


查询GP数据库中表的数据量，去除系统表、外部表
--select schemaname,tablename from pg_tables where schemaname not in ('pg_catalog','information_schema','gp_toolkit')
and tablename not like '%_1_prt_%';


--select * from pg_namespace;


--select aa.nspname,bb.relname from pg_namespace aa,pg_class bb 
where aa.oid=bb.relnamespace and aa.nspname not in ('pg_catalog','information_schema','gp_toolkit','pg_toast','pg_bitmapindex','pg_aoseg')
and bb.relkind='r' and bb.relstorage!='x' and bb.relname not like '%_1_prt_%';


----查询分区表
select * from table_name partition(par_name);
----------------生成添加分区--日----------------------
select 'alter table '||p.schemaname||'.'||p.tablename||' add partition '||p.partitionname||' VALUES('||p.ary[1]||') WITH (appendonly=true, compresslevel=5, compresstype=zlib)  TABLESPACE dataspace;'
from 
(select *,string_to_array(partitionlistvalues,'::') ary from pg_partitions where schemaname='dw') p
where p.ary[1] like '201504%'
 -- and p.ary[1] not like '20150331%'
  and p.tablename <> 'tdw_12580_busslist_h_bk' 
group by p.schemaname,p.tablename,p.partitionname,p.ary[1]
order by p.ary[1],p.schemaname,p.tablename;
----------------生成添加分区--小时------------------------
select 'alter table '||p.schemaname||'.'||p.tablename||' add partition '||p.partitionname||' START ('||p.rangestart[1]||') END ('||p.rangeend[1]||') INCLUSIVE WITH (appendonly=true, compresslevel=5, compresstype=zlib)  TABLESPACE dataspace;'
from 
(select *,string_to_array(partitionrangestart,'::') rangestart,string_to_array(partitionrangeend,'::') rangeend from pg_partitions where schemaname='dw') p
where p.rangestart[1] like '201504%'
 -- and p.ary[1] not like '20150331%'
  and p.tablename <> 'tdw_12580_busslist_h_bk' 
group by p.schemaname,p.tablename,p.partitionname,p.rangestart[1],p.rangeend[1]
order by p.rangestart[1],p.schemaname,p.tablename;
-----生成删除分区的语句
select 'alter table '||p.schemaname||'.'||p.tablename||' drop partition for('||p.ary[1]||');'
from 
(select *,string_to_array(partitionlistvalues,'::') ary from pg_partitions where schemaname='sc') p
where p.ary[1] like '201411%'
group by p.schemaname,p.tablename,p.ary[1]
order by p.schemaname,p.tablename,p.ary[1];
----查看分区是否全
select p.schemaname,p.tablename,count(distinct p.ary[1])
from 
(select *,string_to_array(partitionlistvalues,'::') ary from pg_partitions where schemaname='sc') p
where p.ary[1] like '201502%'
group by p.schemaname,p.tablename
order by 3;
-------查询表的尺寸--------
select pg_relation_size('dw.tdw_read_read_d_1_prt_partition_date_20141102')


----备份------
 ----备份表结构-s,mk下的所有表
 gp_dump --gp-d=/gpdata -s --schema=mk -U gpadmin ac
 ----备份mk下的表,--gp-c用gzip压缩导出
 gp_dump --gp-c --gp-d=/gpdata --schema=mk -U gpadmin ac
 gp_dump --gp-d=/gpdata -s -t dw.tb_det_read_cont -U gpadmin ac
 ---导具体的表-----
 pg_dump -f ./dw.tb_det_dm_fac_h.sql -s -t dw.tb_det_dm_fac_h -U gpadmin ac
 gpcrondump -x ac -a -g -G
 -----全量备份
 gpcrondump -x ac -a -u /gpdata/backup -g -G -h -B 34


查看各个segment情况：
select * from gp_segment_configuration;
--查询版本
select version();
----查询锁1
select * from gp_toolkit.gp_locks_on_relation where lorrelname like '%tdc_label_relation_linkmen_tmp%';


----查询正在运行的语句
select * from pg_stat_activity where current_query like '%tdc_label_relation_linkmen_tmp%';


-------查看队列的情况
SELECT * FROM gp_toolkit.gp_resqueue_status;
----取消语句
select pg_cancel_backend(pid);
----取消idle语句(相当于kill)
pg_terminate_backend(pid)


-------查看等待队列的情况 
SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';


----查看当前处于活动状态或者等待状态的语句：
SELECT rolname, rsqname, pid, granted, current_query, datname
FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks, pg_stat_activity
WHERE pg_roles.rolresqueue=pg_locks.objid
AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid
AND pg_stat_activity.procpid=pg_locks.pid;


-------查看dbid,hostname,port,dir
SELECT dbid, content, hostname, address, port,
replication_port, fselocation as datadir
FROM gp_segment_configuration, pg_filespace_entry
WHERE dbid=fsedbid
ORDER BY content,dbid;


--------查询一段时间内内存，CPU平均使用率和峰值--------------------
select t.hostname,avg(mem_actual_used*1.0000/mem_total),max(mem_actual_used*1.0000/mem_total),avg(100-cpu_idle)*1.00,max(100-cpu_idle)*1.00 
from system_history t where ctime >='2015-05-27 03:00:00' and ctime <='2015-05-27 12:00:00'  group by t.hostname order by t.hostname


--------查询一段时间内主机内存，CPU使用率--------------------
select t.hostname,ctime,mem_actual_used*1.0000/mem_total,(100-cpu_idle)*1.00 
from system_history t where ctime >='2015-05-27 03:00:00' and ctime <='2015-05-27 12:00:00' 
and t.hostname ='cp_sdw29'
order by ctime


1. Output of gp_segment_configuration and pg_filesystem:
a. copy (select * from gp_segment_configuration join pg_filespace_entry on dbid=fsedbid) to '/var/tmp/SRxxxxxxxx/gp_configuration.log' ;
2. Output of configuration history:
a. copy (SELECT * from gp_configuration_history order by time desc) to '/var/tmp/SRxxxxxxxx/gp_configuration_history';
3. Database Logfiles for that day from:
a. Master
b. Primary
c. Mirror
Below example show how to collect logs for master,primary and mirror from mdw,sdw1 and sdw2 nodes.
 
From mdw:  Replace DATE with your date of logs collection.
gpssh -h cp_mdw -h cp_sdw6 -h cp_sdw7 'tar czvf /home/gpadmin/`hostname`_20150401_pglogs_1.tar.gz `find /gpdata/ -name "gpdb-2015-04-01*"`'


gpssh -h cp_sdw6 -h cp_sdw7 'tar czvf /home/gpadmin/`hostname`_20150401_pglogs_1.tar.gz `find /gpdata/ -name "gpdb-2015-04-01*"`'
 
Collect files from mdw, sdw1 and sdw2 from /var/tmp.
gpscp -h cp_sdw6 -h cp_sdw7 =:/home/gpadmin/*_20150401_pglogs_1.tar.gz /home/gpadmin/

4. gpAdminLogs from master:
Tar of $HOME/gpAdminLogs from Master
tar -czvf /home/gpadmin/`hostname`_gpAdminLogs_20150401.tar.gz $HOME/gpAdminLogs/*_20150401.log


alter RESOURCE QUEUE vgopsa_queue WITH (MAX_COST=100000000.0)
select * from queries_history where gp_segment_id ='0' and ctime >='2015-04-20 15:20:00' and ctime <='2015-04-21 02:00:00' order by ctime
select gp_segment_id,* from queries_history where ctime >='2015-04-20 15:20:00' and ctime <='2015-04-21 02:00:00' order by ctime


-------统计耗时SQL语句-------------
select username,db,substr(query_text,0,100),count(*) 
from queries_history 
where ctime >='2015-05-21 00:00:00' and tfinish-tstart > '00:30:00' 
group by username,db,substr(query_text,0,100) order by substr(query_text,0,100);


/usr/local/greenplum-db/bin/lib/gpcheckcat -B 100 -p 5432 ac >> /home/gpadmin/catalog_0402.log 2>&1 &


select * from pg_stat_activity order by backend_start;
select * from pg_stat_activity where usename='vgopsa' order by backend_start desc;
select * from pg_stat_activity where usename='vgopsa' and current_query <> '<IDLE>' and current_query like '%insert%' order by backend_start;
select * from pg_stat_activity where usename='vgopsa_syn' order by backend_start;

select * from pg_stat_activity where usename='vgopsa_load' order by backend_start;