gp数据管理

2019独角兽企业重金招聘Python工程师标准>>>

1.gp数据库后台维护 
1.数据库启动：gpstart 
常用可选参数：  
-a:直接启动，不提示终端用户输入确认 
-m:只启动master实例，主要在故障处理时使用 

2.数据库停止：gpstop： 
常用可选参数： 
-a:直接停止，不提示终端用户输入确认 
-m:只停止master实例，与gpstart –m对应使用 
-M fast：停止数据库，中断所有数据库连接，回滚正在运行的事务 
-u:不停止数据库，只加载pg_hba.conf 和postgresql.conf中运行时参数，当改动参数配置时候使用。 
评：-a用在shell里，最多用的还是-M fast。 

3.gpstate：显示Greenplum数据库运行状态，详细配置等信息 
常用可选参数： 
-c：primary instance 和 mirror instance 的对应关系 
-m：只列出mirror实例的状态和配置信息 
-f：显示standby master 的详细信息 
-Q：显示状态综合信息 
评：该命令默认列出数据库运行状态汇总信息，常用于日常巡检。最开始由于网卡驱动的问题，做了mirror后，segment经常down掉，用-Q参数查询综合信息还是比较有用的。 

4.当通过gpstate检查有实例宕机，segment节点为failed状态，不能完成同步状态，执行以下操作步骤恢复实例： 
（1）停止master实例：gpstop -m 
（2）恢复segment节点：gprecoverseg 
（3）gpstate -m 检查同步情况，如果处于同步状态时，执行：gpstate -m  
（4）恢复primary角色：gprecoverseg -r 

5.若发现changing track存在。则表明有部分节点已经处于故障中。 
（1）gpstate –c检查各节点状态； 
（2）执行gprecoverseg –a进行节点故障恢复。 

6.如果没有master standby，建议采用定期手动备份的办法，对Master库进行完全备份,具体操作方式： 
（1）以gpadmin用户登录Master机器【机器名称为mdw】 
（2）切换到 /data/gpdata/gpmaster目录下，用tar命令打包。 
tar -cvf /home/gpadmin/gpmaster.tar gpseg-1 
（3）将/home/gpadmin/gpmaster.tar进行库外备份 

7.$MASTER_DATA_DIRECTORY目录下： 
（1）主机远程访问策略：pg_hba.conf 
（2）数据库运行参数：postgresql.conf 
（3）数据库服务器日志文件：cd $MASTER_DATA_DIRECTORY 
（4）greenplum管理脚本的日志文件：superuser_home/gpAdminLogs 
（5）标准的PostgreSQL系统目录：pg_* 

8.在master结点修改postgresql.conf文件，让所有segment同时修改postgresql.conf文件的操作： 
gpssh -f ~/seg_hosts "echo 'parameter=value'|cat ->>/gpdata/p*/*/postgresql.conf" 

9.收集统计信息，回收空间 
定期使用Vacuum analyze tablename 回收垃圾和收集统计信息，尤其在大数据量删除，导入以后，非常重要 
评：这个说的不全面，vacuum分两种，一种是analize，优化查询计划的，还有一种是清理垃圾数据，postres删除工作，并不是真正删除数据，而是在被删除的数据上，坐一个标记，只有执行vacuum时，才会真正的物理删除，这个非常重用，有些经常更新的表，各种查询、更新效率会越来越慢，这个多是因为没有做vacuum的原因。 

--回收所有垃圾，并显示详细信息，而且更新规划器统计（非常吃I/O，不推荐） 
VACUUM FULL VERBOSE ANALYZE  
--开启定时任务，回收经常更新的表的空间（推荐） 
VACUUM VERBOSE ANALYZE tablename 
--autovacuum守护进程，自动执行vacuum和analyze命令 

10.数据库备份：gp_dump 
常用参数： 
-s: 只导出对象定义（表结构，函数等） 
-a: 只导出数据，不导出对象定义 
-n: 只导出某个schema 

gp_dump -p 5353 -d NTP2000 -h 172.16.19.37 -s 
gp_dump -p 5353 -d NTP2000 -h 172.16.19.37 -a 
gp_dump -p 5353 -d NTP2000 -h 172.16.19.37 -a -t tablename 
gp_dump -h 172.16.19.37 -p 5432 -d dbname -a -t tablename --gp-c 

默认在master的data 目录上产生这些文件： 
（1）gp_catalog_1_<dbid>_<timestamp> ：关于数据库系统配置的备份文件 
（2）gp_cdatabase_1_<dbid>_<timestamp>：数据库创建语句的备份文件 
（3）gp_dump_1_<dbid>_<timestamp>：数据库对象ddl语句 
（4）gp_dump_status_1_<dbid>_<timestamp>：备份操作的日志 
在每个segment instance 上的data目录上产生的文件： 
（1）gp_dump_0_<dbid>_<timestamp>：用户数据备份文件 
（2）gp_dump_status_0_<dbid>_<timestamp>：备份日志 

11.数据库恢复gp_restore 
必选参数： 
--gp-k=key ：key为gp_dump导出来的文件的后缀时间戳 
-d dbname ：将备份文件恢复到dbname 

12.登陆与退出Greenplum 
#正常登陆 
psql gpdb 
psql -d gpdb -h gphostm -p 5432 -U gpadmin 
#使用utility方式 
PGOPTIONS="-c gp_session_role=utility" psql -h-d dbname hostname -p port 
#退出 
在psql命令行执行\q 

13.参数查询 
psql -c 'SHOW ALL;' -d gpdb 
gpconfig --show max_connections 
评：这个有用，可以管道给grep。 

14.操作gpdb 
（0）创建数据库 
createdb -h localhost -p 5432 dhdw 
创建GP文件系统 
（1）#子节点，视segment数创建目录 
mkdir -p /gpfsdw/seg1 
mkdir -p /gpfsdw/seg2 
chown -R gpadmin:gpadmin /gpfsdw 
（2）#主节点 
mkdir -p /gpfsdw/master 
chown -R gpadmin:gpadmin /gpfsdw 
gpfilespace -o gpfilespace_config 
gpfilespace -c gpfilespace_config 
（3）创建GP表空间 
psql gpdb 
create tablespace TBS_DW_DATA filespace gpfsdw; 
SET default_tablespace = TBS_DW_DATA; 
（4）删除GP数据库 
gpdeletesystem -d /gpmaster/gpseg-1 -f 

15.导出数据： 
#####shell脚本--start####### 
#!/bin/bash 
psql -d NTP2000 -h 196.168.1.11 -p 5532  -c "\copy (select * from tablename) to /home/gpadmin/tablename.txt" 
echo "导出完成." 
#####shell脚本-- end ####### 

16.导入数据： 
#####shell脚本--start####### 
#!/bin/bash 
psql -d NTP2000 -h 196.168.1.11 -p 5532  -c "\copy tablename from '/home/gpadmin/tablename.txt' with delimiter ';';" 
echo "导出完成." 
#####shell脚本-- end ####### 

2.gp数据库客户端维护 
1.查看实例配置和状态（对应后台执行：gpstate -c） 
select * from gp_configuration order by 1 
主要字段说明： 
Content：该字段相等的两个实例，是一对P（primary instance）和M（mirror instance) 
Isprimary：实例是否作为primary instance 运行 
Valid：实例是否有效，如处于false 状态，则说明该实例已经down 掉。 
Port：实例运行的端口 
Datadir:实例对应的数据目录 

2.查看用户会话和提交的查询等信息 
该表能查看到当前数据库连接的IP地址，用户名，提交的查询等。 
select * from pg_stat_activity  
另外也可以在master主机上查看进程，对每个客户端连接，master 都会创建一个进程。 
ps -ef |grep -i postgres |grep -i con 
评：常用的命令，我经常用这个查看数据库死在那个sql上了。 

3.查看数据库、表占用空间 
--必须在数据库所对应的存储系统里，至少保留30%的自由空间，日常巡检，要检查存储空间的剩余容量。 
select pg_size_pretty(pg_relation_size('schema.tablename')); 
select pg_size_pretty(pg_database_size('databasename)); 
评：可以查看任何数据库对象的占用空间，pg_size_pretty可以显示如mb之类的易读数据.另外，可与pg_tables，pg_indexes之类的系统表链接，统计出各类关于数据库对象的空间信息。 
---表占用空间大小排名： 
SELECT relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as other 
FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class 
WHERE sotd.sotdoid = pg_class.oid ORDER BY relname; 
---索引占用空间 
SELECT soisize/1024/1024 as size_MB, relname as indexname 
FROM pg_class, gp_toolkit.gp_size_of_index 
WHERE pg_class.oid = gp_size_of_index.soioid 
AND pg_class.relkind='i'; 
---OBJECT的操作统计 
SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time 
FROM pg_stat_operations 
WHERE objname = '<name>'; 

4.查看锁信息： 
SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, 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; 
主要字段说明： 
relname: 表名 
locktype、mode 标识了锁的类型 

5.查看资源队列的状态： 
资源队列和属性：select * from pg_resqueue 
资源队列的分配角色：select * from pg_roles 
队列存在等待的状态：select * from pg_locks 
执行和等待的查询进程信息：select * from pg_stat_activity 
队列限制，执行和等待查询的数量：select * from pg_resqueue_status 

pg状态：select * from pg_stats 
pg设置：select * from pg_settings 
查看segment配置：select * from gp_segment_configuration; 
文件系统：select * from pg_filespace_entry; 
磁盘、数据库空间 
SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment; 
SELECT * FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname; 

select * from gp_configuration 
select * from gp_distribution_policy 
select * from gp_id 
select * from gp_version_at_initdb 
select * from pg_exttable 
select * from pg_class 

6.查看数据分布情况 
在数据库客户端执行： 
select gp_segment_id,count(*) from tablename group by 1; 
评：非常有用，gp要保障数据分布均匀。如数据分布不均匀，将发挥不了并行计算的优势，严重影响性能。 

7.explain：在提较大的查询之前，使用explain分析执行计划、发现潜在优化机会，避免将系统资源熬尽。 
explain analyze select * from tablename 
评：少写了个analyze，如果只是explain，统计出来的执行时间，是非常坑爹的，如果希望获得准确的执行时间，必须加上analyze。 

3.gp自带测试命令： 
测试硬盘的读写速度：gpcheckperf 
测试硬盘存储的带宽：gpcheckperf 
测试服务器间的网络传输速度：gpchecknet/gpcheckperf 
验证操作系统的环境设置：gpcheckos 
压力测试：bonnie++ 

4.postgres常用数学函数 
%：求余，7%2=1 
^:平方，2^2=4 
|/:开平方，|/9=3 
||/:开三次根号，||/8=2 
!：阶乘，!3=6 
&,|,#,~：与，或，亦或，非 
<<,>>：左移，右移 
abs()：绝对值，abs(-999.9)=999.9 
ceiling():上限取整，ceiling(48.2)=49 
floor()：下限取整，floor(48.2)=48 
pi()：常数，3.1415926 
random():介于0到1的随机数 
round():四舍五入函数,round(22.7)=23 
||：字符串连接符，'my'||'my'=mymy 
Char_length（string）或者Length(string):字符串长度，Char_length('mymy')=4 
Position(string in string):字符位置，Position('my' in 'ohmy')=3 
Lower('MYMY')='mymy' 
Upper('mymy')='MYMY' 
Substring(string from n for n)：字串，Substring('myohmy' from 3 for 2)='oh' 

Trim(both,leading,trailing from string)：裁减，Trim('  mymy   ')='mymy'

select age(current_timestamp,'2008-08-12')   ---4 years 8 mons 8 days 10 hours 17 mins 24.464 secs 
select extract(day from current_date)   ---20 

5.psql语法 
查看数据库中所有schema列表：\dn 
查看表的结构：\d+ table_name 
列出所有系统目录表：\dt S 
列出数据库的外部表：\dx 
查看所有的视图：\dv 
查看所有的视图定义：\d+ view_name 
显示所有的索引：\di 
查看一个索引的定义：\d+ index_name 
显示所有的序列：\ds 
查看一个序列的定义：\d+ sequence_name 
查看系统目录中的统计视图和表：\dtvS pg_stat* 
显示系统清单：\dtS 
显示系统视图清单：\dvS

转载于:https://my.oschina.net/goopand/blog/362290