GreenPlum管理数据库
GreenPlum管理数据库
1.管理 Greenplum 集群
1.1.启动数据库
启动Greenplum数据库
gpstart -a
对于要求重启数据库生效
gpstop -r
仅重新载入更改配置文件生效
gpstop -u
1.2.维护模式启动Master
使用-m模式运行gpstart：
gpstart -m
以维护模式连接到Master进行目录维护。例如：
PGOPTIONS='-c gp_session_role=utility' psql postgres
在完成管理任务后，停止处于维护模式的额Master。然后以生产模式重启它。
gpstop -mr
1.3.停止Greenplum数据库
# 停止Greenplum数据库
gpstop
# 快速模式停止Greenplum数据库
gpstop -M fast
1.4.激活备库流程
激活数据库存储目录
gpactivatestandby -d $MASTER_DATA_DIRECTORY
用 -f 选项运行gpstate来检查状态：
gpstate -f
在后备Master主机上，移动或者移除数据目录gpseg-1。这个例子移动该目录：
mv /data/master/gpseg-1 /data/master/backup_gpseg-1
1.5.日常维护
集群状态查询
[gpadmin@gp-mdw ~]$ gpstate
20220925:22:52:20:056550 gpstate:gp-mdw:gpadmin-[INFO]:-Starting gpstate with args:
20220925:22:52:20:056550 gpstate:gp-mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.22.0 build commit:4b6c079bc3aed35b2f161c377e208185f9310a69 Open Source'
20220925:22:52:20:056550 gpstate:gp-mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.22.0 build commit:4b6c079bc3aed35b2f161c377e208185f9310a69 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit compiled on Sep 8 2022 22:39:10'
20220925:22:52:20:056550 gpstate:gp-mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20220925:22:52:20:056550 gpstate:gp-mdw:gpadmin-[INFO]:-Gathering data from segments...
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:-Greenplum instance status summary
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:-----------------------------------------------------
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Master instance = Active
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Master standby = gp-smdw
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Standby master state = Standby host passive
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total segment instance count from metadata = 18
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:-----------------------------------------------------
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Primary Segment Status
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:-----------------------------------------------------
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total primary segments = 18
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total primary segment valid (at master) = 18
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total primary segment failures (at master) = 0
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number of postmaster.pid files missing = 0
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number of postmaster.pid files found = 18
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number of postmaster.pid PIDs missing = 0
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number of postmaster.pid PIDs found = 18
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number of /tmp lock files missing = 0
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number of /tmp lock files found = 18
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number postmaster processes missing = 0
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Total number postmaster processes found = 18
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:-----------------------------------------------------
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Mirror Segment Status
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:-----------------------------------------------------
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:- Mirrors not configured on this array
20220925:22:52:21:056550 gpstate:gp-mdw:gpadmin-[INFO]:-----------------------------------------------------
[gpadmin@gp-mdw ~]$
磁盘空间查询
# 数据库查看使用量
select * from gp_toolkit.gp_disk_free;
# 系统命令行查看
gpssh -f host.list -e “df -h |grep data”
查询当前连接
psql -c "select * from pg_stat_activity;"
查看数据库
psql -c "select pg_size_pretty(pg_database_size('test'));"
表占用空间
psql -c "select pg_size_pretty(pg_relation_size('schema.test'));"
表统计
select relname from pg_class t where t.relname like 'ods%';
select relname from pg_class t where t.relname like 'kn%';
统计资源
select gp_segment_id,count(*) from test group by 1 ;
查看实例配置和状态
select * from gp_segment_configuration order by 1;
收集统计信息，回收空间 定期使用回收垃圾和收集统计信息，尤其在大数据量删除，导入以后，非常重要
Vacuum analyze tablename
进程监控：
select * from pg_stat_activity where waiting ='t' ORDER BY current_query; select * from pg_stat_activity where waiting ='t' ORDER BY sess_id;
select * from pg_stat_activity where waiting ='f' ORDER BY current_query; select * from pg_stat_activity where waiting ='f' ORDER BY sess_id;
select * from pg_tablespace;
select * from pg_filespace;
查看数据分布
select * from pg_filespace_entry;
SELECT spcname, fsname,fsedbid,fselocation FROM pg_tablespace pgts, pg_filespace pgfs,pg_filespace_entry pgfse WHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid ORDER BY 1,3;
查看数据库备份
select pg_start_backup('backup baseline');
select pg_stop_backup();
常看数据库.conf配置
show all
查看当前日期属于一年中第几周
select EXTRACT(week from TIMESTAMP '2018-05-11');
1.6.集群异常修复
修复前负载判定(是否需要kill会话,是否需要重启)，查看是否存在超过一个小时以上的会话，
select pid,usename,query_start,client_addr,xact_start,waiting ,waiting_reason,query from pg_stat_activity where state <>'idle' and query_start < now()-interval '1 hour' order by query_start;
查看节点负载，可以使用vmstat命令，也可以使用nmon监控工具（需安装）
节点日志备份([可选]判定实例>判定路径>备份日志，在恢复前备份日志)
select time,hostname,status,datadir,content from gp_configuration_history h,gp_segment_configuration c where h.dbid=c.dbid and "desc" like '%d, and%' order by time desc limit 10;
根据hostname和datadir，以及time备份对应实例pg_log目录下对应日期的日志
a.常规修复方式(后台运行)
nohup gprecoverseg -a &
若存在主备切换，则需要在修复完成后进行实例切回
nohup gprecoverseg -ra &
查看修复进度
gpstate -e
b. 全量修复方式(后台运行)
nohup gprecoverseg -Fa &
若存在主备切换，则需要在修复完成后进行实例切回
nohup gprecoverseg -ra &
2.管理数据库
2.1.并发控制
Greenplum数据库和PostgreSQL不为并发控制使用锁。它们使用一种多版本模型来维护数据一致性，即多版本并发控制（MVCC）。MVCC为每一个数据库会话实现了事务隔离，并且每一个查询事务会看到一个数据的快照。这保证该事务会看到一致的不受其他并发事务影响的数据。
因为MVCC不会为并发控制使用显式锁，锁竞争被最小化并且Greenplum数据库在多用户环境中维持了合理的性能。为查询（读取）数据获得的锁不与为写数据获得的锁冲突。
Greenplum数据库提供了多种锁模式来控制对表中数据的并发访问。大部分Greenplum数据库的SQL命令自动获取适当的锁来确保在命令执行期间被引用的表不会被删除或者被以不兼容的方式被修改。对于不能轻易适应于MVCC行为的应用，可以使用LOCK命令来获取显式锁。不过，MVCC的正确使用通常能提供更好的性能。
锁模式 | 相关的SQL命令 | 冲突的锁 |
|---|---|---|
ACCESSSHARE | SELECT | ACCESS EXCLUSIVE |
ROW SHARE | SELECT FOR UPDATE, SELECT FOR SHARE | EXCLUSIVE, ACCESS EXCLUSIVE |
ROWEXCLUSIVE | INSERT, COPY | SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
SHAREUPDATEEXCLUSIVE | VACUUM (without FULL), ANALYZE | SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
SHARE | CREATE INDEX | ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
SHARE ROWEXCLUSIVE | ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE | |
EXCLUSIVE | DELETE, UPDATE | ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
Greenplum数据库会为UPDATE、DELETE和SELECT FOR UPDATE获取更加严格的EXCLUSIVE锁（而不是PostgreSQL中的ROW EXCLUSIVE）。
2.2.插入行
使用INSERT命令在一个表中创建行。这个命令要求该表的名称和表中每一个列的值，可以选择性地以任意顺序指定列名。如果没有指定列名，以那些列在表中的顺序列出数据值，用逗号分隔它们。
例如，指定要插入的列名和值：
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
只指定要插入的值：
INSERT INTO products VALUES (1, 'Cheese', 9.99);
通常，数据值都是常量，但也可以使用标量表达式。例如：
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod <
'2016-05-07';
可以在单个命令中插入多行。例如：
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);
要插入数据到一个分区表，应指定根分区表，即用CREATE TABLE命令创建的表。也可以在一个INSERT命令中指定该分区表的一个叶子子表。如果数据对于指定的叶子子表无效，会返回一个错误。不支持在INSERT命令中指定一个不是叶子子表的子表。
要插入大量数据，使用外部表或者COPY命令。对于插入大量行，这些装载机制比INSERT更加有效。
追加优化表的存储模型是为批量数据装载而优化。Greenplum不推荐对追加优化表的单行INSERT语句。对于追加优化表，Greenplum数据库支持最多127个并发INSERT事务插入到一个追加优化表。
2.3.更新现有行
UPDATE命令在一个表中更新行。可以更新一个表中所有的行、所有行的一个子集或者单个行。可以单独更新每一列而不影响其他列。
要执行一次更新，需要：
- 要更新的表和列的名称
- 这些列的新值
- 指定要更新的行的一个或者更多条件。
例如，下面的命令把所有价格为5的产品更新为价格为10：
UPDATE products SET price = 10 WHERE price = 5;
在Greenplum数据库中使用UPDATE由下列限制：
- Greenplum的分布键列不能被更新。
- 如果使用了镜像，不能在UPDATE语句中使用STABLE或VOLATILE函数。
- Greenplum数据库不支持RETURNING子句。
- Greenplum数据库的分区列不能被更新。
2.4.删除行
DELETE命令从一个表中删除行。指定一个WHERE子句可以删除满足特定条件的行。如果不指定WHERE子句，该表中所有的行都会被删除。其结果是一个合法的但为空的表。例如，从产品表中删除所有价格为10的行：
DELETE FROM products WHERE price = 10;
要从一个表中删除所有行：
DELETE FROM products;
在Greenplum数据库中使用DELETE具有和使用UPDATE类似的限制：
- 如果使用了镜像，不能在DELETE语句中使用STABLE或VOLATILE函数。
- Greenplum数据库不支持RETURNING子句。
2.5.截断一个表
使用TRUNCATE命令可以快速地移除一个表中的所有行。例如：
TRUNCATE mytable;
这个命令在一次操作中清空一个表的所有行。注意TRUNCATE不扫描该表，因此它不会处理继承的子表或者ON DELETE的重写规则。该命令只截断所提到的表中的行。
2.6.使用事务
事务允许用户把多个SQL语句捆绑在一个要么全做要么全不做的操作中。
下面是Greenplum数据库的SQL事务命令：
- BEGIN或者START TRANSACTION 开始一个事务块。
- END或者COMMIT提交一个事务的结果。
- ROLLBACK放弃一个事务而不做任何更改。
- SAVEPOINT在一个事务中标记一个位置并且允许做部分回滚。用户可以回滚在一个保存点之后执行的命令但保留该保存点之前执行的命令。
- ROLLBACK TO SAVEPOINT 回滚一个事务到一个保存点。
- RELEASE SAVEPOINT 销毁一个事务内的保存点。
2.7.事务隔离级别
Greenplum数据库接受下列标准SQL事务级别：
- 读未提交和读已提交的行为像标准的读已提交
- 不允许可重复读。如果要求可重复读的行为，可使用可序列化。
- 可序列化的行为像SQL标准的可序列化
下列信息描述了Greenplum事务级别的行为：
- 读已提交/读未提交 — 提供快速、简单、部分的事务隔离。使用读已提交和读未提交事务隔离，SELECT、UPDATE和DELETE事务在一个查询开始时取得的数据库快照上操作。
一个SELECT查询：
- 看得见该查询开始前被提交的数据。
- 看得见在该事务内执行的更新。
- 看不见事务外未提交的数据。
- 如果并发事务在该查询所在事务最初的读操作之前就被提交，该查询可能会看到这个并发事务所作的更改。
如果其他并发事务在同一个事务中后续的SELECT查询开始前提交更改，这些查询能够看到不同的数据。UPDATE和DELETE命令只找在该命令开始前提交的行。
读已提交或读未提交事务隔离允许并发事务在UPDATE或者DELETE找到行之前修改或者锁定该行。读已提交或读未提交事务隔离可能不适合执行复杂查询和更新并且要求该数据库的一致性视图的应用。
- 可序列化 — 提供了严格的事务隔离，在其中事务的执行就好像没有并行化而是一个接一个执行。可序列化级别上的应用必须被设计为在序列化失败的情况下重试事务。在Greenplum数据库中，SERIALIZABLE能防止脏读、不可重复读和幻读，而且不需要昂贵的锁定，但是在Greenplum数据库的一些SERIALIZABLE事务之间可能发生其他的相互影响而阻止它们变成真正地可序列化。并发运行的事务应该被检查来识别出不会因为不允许对同一数据的并发更新而被阻止的相互影响。通过使用显式表锁或者要求冲突事务更新一个被引入来表示该冲突的虚拟行可以阻止所发现的问题。
一个SELECT查询：
- 看得到一个事务开始时（不是该事务中当前查询开始时）的数据快照。
- 只看得到在查询开始前被提交的数据。
- 看得到该事务内执行的更新。
- 看不到该事务外部的未提交数据。
- 看不到并发事务所作的更改。一个单一事务中的后续SELECT命令总是看到相同的数据。UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令只会发现在该命令开始前被提交的行。如果一个目标行被找到时一个并发事务已经更新、删除或者锁定该行，可序列化或者可重复读事务会等待该并发事务更新该行、删除该行或者回滚。如果该并发事务更新或者删除该行，可序列化或者可重复读事务会回滚。如果并发事务回滚，那么可序列化或者可重复读事务更新或者删除该行。
Greenplum数据库中的默认事务隔离级别是读已提交。要为一个事务更改隔离级别，在BEGIN该事务时声明隔离级别或者在事务开始后使用SET TRANSACTION命令设置隔离级别。
3.回收空间和分析
- 事务ID管理 在每个数据库每2百万个事务的时候，对每张表执行VACUUM是很有必要的。
- 系统目录维护 大量的CREATE和DROP命令会导致系统表的迅速膨胀，以至于影响系统性能。
- 由于MVCC事务并发模型的原因，已经删除或者更新的记录仍然占据着磁盘空间。
- 如果数据库有大量的更新和删除操作，将会产生大量的过期记录
- 定期的运行VACUUM命令可以删除过期记录，回收空间。
虽然新事务看不到被删除或者被更新的数据行，但是它们仍然在磁盘上占用物理空间。周期性地运行VACUUM命令可以移除这些过期的行。例如：
# 回收空间
VACUUM mytable;
# 收集查询优化器需要的统计信息
ANALYZE mytable;
VACUUM命令会收集表级别的统计信息，例如行数和页数。在装载数据后清理所有的表，包括追加优化表。有关推荐的例行清理操作的信息。重要：如果在数据库数据上频繁地执行更新和删除， VACUUM、VACUUM FULL和VACUUM ANALYZE命令应该被用来维护Greenplum数据库中的数据。有关使用VACUUM命令的信息请见Greenplum数据库参考指南。
配置空闲空间映射
过期行被放在空闲空间映射中。空闲空间映射的尺寸应该足够大以保存数据库中所有的过期行。如果空间不足，一个普通的VACUUM命令也不能回收使空闲空间映射溢出的过期行所占用的空间。
VACUUM FULL会回收所有的过期行空间，但是它是一种很昂贵的操作，并且在大型的分布式的Greenplum数据库表上可能会花很长的不可接受的时间来完成。如果空闲空间映射溢出，可以用一个CREATE TABLE AS 语句重建该表并且删除旧表。不鼓励使用VACUUM FULL。用下列服务器配置参数调整空闲空间映射的尺寸：
- max_fsm_pages
- max_fsm_relations
为优化查询进行回收和分析
GP使用基于成本的查询优化器
ANALYZE命令收集查询优化器需要的统计信息
VACUUM ANALYZE可以一起执行：
=# VACUUM ANALYZE tb_cp_02;
4.管理日志文件
数据库服务日志文件
- GP的日志输出量大而且不需要无期限的保存这些日志，管理员需要定期的滚动日志文件
- GP在Master和所有Segment实例上开启了日志文件按天滚动
- 服务器日志文件存放在每个实例数据目录的 pg_log 目录下，格式：gpdb-YYYY-MM-DD_TIME.csv
控制写到服务器日志里的信息的详细程度。有效值是 DEBUG5， DEBUG4，DEBUG3，DEBUG2， DEBUG1，INFO，NOTICE， WARNING ，ERROR，LOG， FATAL，和 PANIC。每个级别都包含它后面的级别。越靠后的数值发往服务器日志的信息越少。缺省是 NOTICE。请注意 LOG 和 client_min_messages 里面的同名级别优先级不同。只有超级用户可以修改这个设置。
show log_min_messages;
这个选项控制那些信息发送到客户端。有效的数值是 DEBUG5，DEBUG4， DEBUG3，DEBUG2， DEBUG1，LOG，NOTICE， WARNING 和 ERROR。每个级别包含所有它后面的级别，级别越靠后，发送的信息越少。缺省是 NOTICE。这里的 LOG 和 log_min_messages 里面的有不同的级别。
show client_min_messages;
搜索数据库服务日志文件
通过gplogfilter工具来查找匹配指定标准的日志数据;默认只查找默认目录下的Master日志文件。 例如， 显式master日志文件的最近3行记录
$ gplogfilter –n 3
也可以使用gplogfilter+gpssh工具组合在所有segment节点进行查找 例如，显式每个segment日志文件的最近三行
$ gpssh –f seg_host_file
=> gplogfilter –n 3 /data/primary/*/pg_log/gpdb*.csv
程序日志文件
缺省位于~/gpAdminLogs目录下 命令方式：<script_name>_<date>.log 日志记录的格式： <timestamp>:<utility>:<host>:<user>:[INFO|WARN|FATAL]:<message>
5.系统数据字典存
所有的系统数据字典存放在pg_catalog模式下
- 标准的PostgreSQL系统数据字典为 pg_*
- GP特有的字典目录：
- gp_configuration
- gp_distribution_policy
- gp_distributed_log
- gp_id
- gp_version_at_initdb
- gp_master_mirroring
- gp_pgdatabase
在psql中列出所有的系统字典：\dtS 在psql中列出所有的系统视图：\dvS
腾讯云开发者
