一、####集群启动
su - gpadmin
gpstart

GP服务启停
su - gpadmin
gpstart #正常启动
gpstop #正常关闭
gpstop -M fast #快速关闭
gpstop –r #重启
gpstop –u #重新加载配置文件

二、登录数据库
psql -d ***** #进入某个数据库
postgres=# \l # 查询数据库
postgres=# \i test.sql #执行sql
postgres=# copy 表名 to ‘/tmp/1.csv’ with ‘csv’; #快速导出单表数据
postgres=# copy 表名 from ‘/tmp/1.csv’ with ‘csv’; #快速导入单表数据
postgres=# \q #退出数据库
三、集群状态
gpstate -e #查看mirror的状态
gpstate -f #查看standby master的状态
gpstate -s #查看整个GP群集的状态
gpstate -i #查看GP的版本
gpstate --help #帮助文档，可以查看gpstate更多用法
四、常用命令
gpstate
命令 参数 作用
gpstate -b => 显示简要状态
gpstate -c => 显示主镜像映射
gpstart -d => 指定数据目录（默认值：$MASTER_DATA_DIRECTORY）
gpstate -e => 显示具有镜像状态问题的片段
gpstate -f => 显示备用主机详细信息
gpstate -i => 显示GRIPLUM数据库版本
gpstate -m => 显示镜像实例同步状态
gpstate -p => 显示使用端口
gpstate -Q => 快速检查主机状态
gpstate -s => 显示集群详细信息
gpstate -v => 显示详细信息

1.建表

create table test004(id int ,name varchar(128)) distributed randomly; CREATE TABLE
2.查询
\d test004
select id,name from testOOl order by id; //查询表中具体的

      gpconfig
    命令    参数                              作用
   gpconfig -c => --change param_name  通过在postgresql.conf 文件的底部添加新的设置来改变配置参数的设置。
   gpconfig -v => --value value 用于由-c选项指定的配置参数的值。默认情况下，此值将应用于所有Segment及其镜像、Master和后备Master。
  gpconfig -m => --mastervalue master_value 用于由-c 选项指定的配置参数的Master值。如果指定，则该值仅适用于Master和后备Master。该选项只能与-v一起使用。
  gpconfig -masteronly =>当被指定时，gpconfig 将仅编辑Master的postgresql.conf文件。
  gpconfig -r => --remove param_name 通过注释掉postgresql.conf文件中的项删除配置参数。
  gpconfig -l => --list 列出所有被gpconfig工具支持的配置参数。
  gpconfig -s => --show param_name 显示在Greenplum数据库系统中所有实例（Master和Segment）上使用的配置参数的值。如果实例中参数值存在差异，则工具将显示错误消息。使用-s=>选项运行gpconfig将直接从数据库中读取参数值，而不是从postgresql.conf文件中读取。如果用户使用gpconfig 在所有Segment中设置配置参数，然后运行gpconfig -s来验证更改，用户仍可能会看到以前的（旧）值。用户必须重新加载配置文件（gpstop -u）或重新启动系统（gpstop -r）以使更改生效。
  gpconfig --file => 对于配置参数，显示在Greenplum数据库系统中的所有Segment（Master和Segment）上的postgresql.conf文件中的值。如果实例中的参数值存在差异，则工具会显示一个消息。必须与-s选项一起指定。
  gpconfig --file-compare 对于配置参数，将当前Greenplum数据库值与主机（Master和Segment）上postgresql.conf文件中的值进行比较。
  gpconfig --skipvalidation 覆盖gpconfig的系统验证检查，并允许用户对任何服务器配置参数进行操作，包括隐藏参数和gpconfig无法更改的受限参数。当与-l选项（列表）一起使用时，它显示受限参数的列表。 警告： 使用此选项设置配置参数时要格外小心。
  gpconfig --verbose 在gpconfig命令执行期间显示额外的日志信息。
  gpconfig --debug 设置日志输出级别为调试级别。
  gpconfig -? | -h | --help 显示在线帮助。

   gpstart
  命令     参数   作用
  gpstart -a => 快速启动|
  gpstart -d => 指定数据目录（默认值：$MASTER_DATA_DIRECTORY）
  gpstart -q => 在安静模式下运行。命令输出不显示在屏幕，但仍然写入日志文件。
  gpstart -m => 以维护模式连接到Master进行目录维护。例如：$ PGOPTIONS='-c gp_session_role=utility' psql postgres
  gpstart -R => 管理员连接
  gpstart -v => 显示详细启动信息

  gpstop
  命令     参数   作用
  gpstop -a => 快速停止
  gpstop -d => 指定数据目录（默认值：$MASTER_DATA_DIRECTORY）
  gpstop -m => 维护模式
  gpstop -q => 在安静模式下运行。命令输出不显示在屏幕，但仍然写入日志文件。
  gpstop -r => 停止所有实例，然后重启系统
  gpstop -u => 重新加载配置文件 postgresql.conf 和 pg_hba.conf
  gpstop -v => 显示详细启动信息
  gpstop -M fast          => 快速关闭。正在进行的任何事务都被中断。然后滚回去。
  gpstop -M immediate     => 立即关闭。正在进行的任何事务都被中止。不推荐这种关闭模式，并且在某些情况下可能导致数据库损坏需要手动恢复。
  gpstop -M smart         => 智能关闭。如果存在活动连接，则此命令在警告时失败。这是默认的关机模式。
  gpstop --host hostname  => 停用segments数据节点，不能与-m、-r、-u、-y同时使用

  集群恢复
  命令     参数   作用
  gprecoverseg -a => 快速恢复
  gprecoverseg -i => 指定恢复文件
  gprecoverseg -d => 指定数据目录
  gprecoverseg -l => 指定日志文件
  gprecoverseg -r => 平衡数据
  gprecoverseg -s => 指定配置空间文件
  gprecoverseg -o => 指定恢复配置文件
  gprecoverseg -p => 指定额外的备用机
  gprecoverseg -S => 指定输出配置空间文件

  激活备库流程
  命令     参数   作用
  gpactivatestandby -d 路径 | 使用数据目录绝对路径，默认：$MASTER_DATA_DIRECTORY
  gpactivatestandby -f | 强制激活备份主机
  gpactivatestandby -v | 显示此版本信息


  初始化备Master
  命令     参数   作用
  gpinitstandby -s 备库名称 => 指定新备库
  gpinitstandby -D => debug 模式
  gpinitstandby -r => 移除备用机

    #创建用户
  create role dbdream password 'dbdream' createdb login;
  #查看用户
  select * from pg_user;
  #查看权限
  select * from information_schema.table_privileges;
  #修改文件允许远程登陆
  pg_hba.conf
host    all     dbdream         10.9.15.20/32   md5
  gpstop –u
  #通过pg_roles字典开查看数据库的用户信息
  select rolname,oid from pg_roles;
  #查看表空间
  SELECT oid,* from pg_tablespace;
  #查看表空间及对应文件目录
  WITH spc AS (SELECT * FROM  gp_tablespace_location(24589))
    SELECT seg.role, spc.gp_segment_id as seg_id, seg.hostname, seg.datadir, tblspc_loc 
FROM spc, gp_segment_configuration AS seg 
WHERE spc.gp_segment_id = seg.content ORDER BY seg_id;

  #查看表、索引、视图对应的物理文件
  select oid,relfilenode,relname from pg_class where relname = 't2';
  #查看表oid
  SELECT a.oid,
   a.relname AS name,
   b.description AS comment
    FROM pg_class a
   LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid

WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname=‘public’) --用户表一般存储在public模式下
AND a.relkind=‘r’
ORDER BY a.relname

  #查看当前postgresql有几个数据库

SELECT datname FROM pg_database
#查看数据库大小
select pg_size_pretty(pg_database_size(‘gpdw’));
#查看表空间大小
select pg_size_pretty(pg_tablespace_size(‘pg_default’));
#查看索引大小
select pg_size_pretty(pg_indexes_size(‘t1’));
#查看表大小
select pg_size_pretty(pg_table_size(‘t2’));
#查看表索引
select
relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from
pg_stat_user_indexes
order by
idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
#查看指定schema 里所有的表大小，按从大到小的顺序排列
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=‘public’ order by pg_relation_size(relid) desc;
#查看表定义
SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = ‘t2’
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;

   1、表膨胀相关查询
  -- 该视图显示了那些膨胀的（在磁盘上实际的页数超过了根据表统计信息得到预期的页数）正规的堆存储的表。
  select * from gp_toolkit.gp_bloat_diag;

  -- 所有对象的膨胀明细
  select * from gp_toolkit.gp_bloat_expected_pages;


  2、表倾斜的相关信息
  -- 该视图通过计算存储在每个Segment上的数据的变异系数（CV）来显示数据分布倾斜。
  select * from gp_toolkit.gp_skew_coefficients;

  -- 该视图通过计算在表扫描过程中系统空闲的百分比来显示数据分布倾斜，这是一种数据处理倾斜的指示器。
  select * from gp_toolkit.gp_skew_idle_fractions;


  3、锁查询相关的信息
  -- 该视图显示了当前所有表上持有锁，以及查询关联的锁的相关联的会话信息。
  select * from gp_toolkit.gp_locks_on_relation;

  -- 该视图显示当前被一个资源队列持有的所有的锁，以及查询关联的锁的相关联的会话信息。
  select * from gp_toolkit.gp_locks_on_resqueue;


  4、日志查询相关的信息
  -- 该视图使用一个外部表来读取来自整个Greenplum（Master、Segment、镜像）的服务器日志文件并且列出所有的日志项。
  select * from gp_toolkit.gp_log_system;

  -- 该视图用一个外部表来读取在主机上的日志文件同时报告在数据库会话中SQL命令的执行时间
  select * from gp_toolkit.gp_log_command_timings;

  -- 该视图使用一个外部表来读取整个Greenplum系统（主机，段，镜像）的服务器日志文件和列出与当前数据库关联的日志的入口。
  select * from gp_toolkit.gp_log_database;

  -- 该视图使用一个外部表读取来自Master日志文件中日志域的一个子集。
  select * from gp_toolkit.gp_log_master_concise;


  5、资源队列相关查询信息
  -- gp_toolkit.gp_resgroup_config视图允许管理员查看资源组的当前CPU、内存和并发限制
  select * from gp_toolkit.gp_resgroup_config;

  -- gp_toolkit.gp_resgroup_status视图允许管理员查看资源组的状态和活动
  select * from gp_toolkit.gp_resgroup_status;

  -- 该视图允许管理员查看到一个负载管理资源队列的状态和活动。
  select * from gp_toolkit.gp_resqueue_status;

  -- 对于那些有活动负载的资源队列，该视图为每一个通过资源队列提交的活动语句显示一行。
  select * from gp_toolkit.gp_resq_activity;

  -- 对于有活动负载的资源队列，该视图显示了队列活动的总览。
  select * from gp_toolkit.gp_resq_activity_by_queue;

  -- 资源队列的执行优先级
  select * from gp_toolkit.gp_resq_priority_backend;

  -- 该视图为当前运行在Greenplum数据库系统上的所有语句显示资源队列优先级、会话ID以及其他信息
  select * from gp_toolkit.gp_resq_priority_statement;

  -- 该视图显示与角色相关的资源队列。
  select * from gp_toolkit.gp_resq_role;


  6、查看磁盘上(database,schema,table,indexs,view)等的占用大小的相关信息
  -- 外部表在活动Segment主机上运行df（磁盘空闲）并且报告返回的结果
  select * from gp_toolkit.gp_disk_free;

  -- 该视图显示数据库的总大小。
  select * from gp_toolkit.gp_size_of_database;

  -- 该视图显示当前数据库中schema在数据中的大小
  select * from gp_toolkit.gp_size_of_schema_disk;

  -- 该视图显示一个表在磁盘上的大小。
  select * from gp_toolkit.gp_size_of_table_disk;

  -- 该视图查看表的索引
  select * from gp_toolkit.gp_table_indexes;

  -- 该视图显示了一个表上所有索引的总大小。
  select * from gp_toolkit.gp_size_of_all_table_indexes;

  -- 该视图显示分区子表及其索引在磁盘上的大小。
  select * from gp_toolkit.gp_size_of_partition_and_indexes_disk;

  -- 该视图显示表及其索引在磁盘上的大小。
  select * from gp_toolkit.gp_size_of_table_and_indexes_disk;

  -- 该视图显示表及其索引的总大小
  select * from gp_toolkit.gp_size_of_table_and_indexes_licensing;

  -- 该视图显示追加优化（AO）表没有压缩时的大小。
  select * from gp_toolkit.gp_size_of_table_uncompressed;

  7、用户使用的工作空间大小信息
  -- 该视图为当前在Segment上使用磁盘空间作为工作文件的操作符包含一行。
  select * from gp_toolkit.gp_workfile_entries;

  -- GP工作文件管理器使用的磁盘空间
  select * from gp_toolkit.gp_workfile_mgr_used_diskspace;

  -- 每个查询的GP工作文件使用情况
  select * from gp_toolkit.gp_workfile_usage_per_query;

  -- 每个segment在GP工作文件中的使用量
  select * from gp_toolkit.gp_workfile_usage_per_segment;


  8、查看用户创建的信息(数据库,schema,表,索引,函数,视图)等信息
  -- gp 中所有的名字(索引、表、视图、函数)等的名字
  select * from gp_toolkit."__gp_fullname";
  -- gp 中AO表的名字
  select * from gp_toolkit."__gp_is_append_only";
  -- gp 中segment的个数
  select * from gp_toolkit."__gp_number_of_segments";
  -- gp 中用户表的个数
  select * from gp_toolkit."__gp_user_data_tables";
  -- GP用户数据表可读
  select * from gp_toolkit."__gp_user_data_tables_readable";
  -- 用户自己创建的schema信息
  select * from gp_toolkit."__gp_user_namespaces";
  -- 用户自己创建的表信息
  select * from gp_toolkit."__gp_user_tables";


  9、系统中维护的ID信息
  -- gp  本地维护的ID
  select * from gp_toolkit."__gp_localid";

  -- gp master外部的log信息
  select * from gp_toolkit."__gp_log_master_ext";

  -- gp segment外部的log信息
  select * from gp_toolkit."__gp_log_segment_ext";

  -- gp master 的id信息
  select * from gp_toolkit."__gp_masterid";


  10、系统查用的查询信息
  -- 该视图显示那些没有统计信息的表，因此可能需要在表上执行ANALYZE命令。
  select * from gp_toolkit.gp_stats_missing;

  -- 该视图显示系统目录中被标记为down的Segment的信息。
  select * from gp_toolkit.gp_pgdatabase_invalid;

  -- 那些被分类为本地（local）（表示每个Segment从其自己的postgresql.conf文件中获取参数值）的服务器配置参数，应该在所有Segment上做相同的设置。
  select * from gp_toolkit.gp_param_settings_seg_value_diffs;

  -- 该视图显示系统中所有的角色以及指派给它们的成员（如果该角色同时也是一个组角色）。
  select * from gp_toolkit.gp_roles_assigned;

  11、系统中常用查询的函数
   
  select * from gp_toolkit.gp_param_settings();
  select * from gp_toolkit.gp_skew_details(oid);
  select * from gp_toolkit."__gp_aocsseg"(IN  oid);
  select * from gp_toolkit."__gp_aovisimap"(IN  oid);
  select * from gp_toolkit.gp_param_setting(varchar);
  select * from gp_toolkit."__gp_skew_coefficients"();
  select * from gp_toolkit."__gp_workfile_entries_f"();
  select * from gp_toolkit."__gp_skew_idle_fractions"();
  select * from gp_toolkit."__gp_aocsseg_name"(IN  text);
  select * from gp_toolkit."__gp_aovisimap_name"(IN  text);
  select * from gp_toolkit."__gp_aocsseg_history"(IN  oid);
  select * from gp_toolkit."__gp_aovisimap_entry"(IN  oid);
  select * from gp_toolkit."__gp_aovisimap_hidden_typed"(oid);
  select * from gp_toolkit."__gp_param_local_setting"(varchar);
  select * from gp_toolkit."__gp_aovisimap_entry_name"(IN  text);
  select * from gp_toolkit."__gp_aovisimap_hidden_info"(IN  oid);
  select * from gp_toolkit."__gp_workfile_mgr_used_diskspace_f"();
  select * from gp_toolkit."__gp_aovisimap_hidden_info_name"(IN  text);
  select * from gp_toolkit.gp_skew_coefficient(IN targetoid oid, OUT skcoid oid, OUT skccoeff numeric);
  select * from gp_toolkit.gp_skew_idle_fraction(IN targetoid oid, OUT sifoid oid, OUT siffraction numeric);
  select * from gp_toolkit.gp_bloat_diag(IN btdrelpages int4, IN btdexppages numeric, IN aotable bool, OUT bltidx int4, OUT bltdiag text);
  select * from gp_toolkit."__gp_aovisimap_compaction_info"(IN ao_oid oid, OUT content int4, OUT datafile int4, OUT compaction_possible bool, OUT hidden_tupcount int8, OUT total_tupcount int8, OUT percent_hidden numeric);

  --查询某schema下所有函数的属主
  SELECT n.nspname AS "Schema",p.proname AS "Name",pg_catalog.pg_get_userbyid(p.proowner) AS "Owner" 
  FROM pg_catalog.pg_proc p 
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
  WHERE n.nspname='dwbi02' ORDER BY 2;

  --查询某schema下所有函数的详细信息
  SELECT n.nspname, p.proname, pg_catalog.pg_get_userbyid(p.proowner) AS "Owner",
   CASE WHEN proallargtypes IS NOT NULL THEN
pg_catalog.array_to_string(ARRAY(
  SELECT
    CASE
      WHEN p.proargmodes[s.i] = 'i' THEN ''
      WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
      WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
      WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '
    END ||
    CASE
      WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
      ELSE p.proargnames[s.i] || ' ' 
    END ||
    pg_catalog.format_type(p.proallargtypes[s.i], NULL)
  FROM
    pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)
), ', ')

ELSE
pg_catalog.array_to_string(ARRAY(
SELECT
CASE
WHEN COALESCE(p.proargnames[s.i+1], ‘’) = ‘’ THEN ‘’
ELSE p.proargnames[s.i+1] || ’ ’
END ||
pg_catalog.format_type(p.proargtypes[s.i], NULL)
FROM
pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
), ', ')
END AS “data_types”
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE 1=1
AND n.nspname <> ‘pg_catalog’
AND n.nspname <> ‘information_schema’
AND n.nspname = ‘dmc’ order by 2,3;

  --某schema下，表数量统计（正则过滤分区表子分区）
  SELECT count(*) FROM pg_tables WHERE tablename !~ '.*_1+_prt_.*$'AND schemaname = 'ctl';

  --查询一个库下各schema占用的空间
  SELECT pg_size_pretty(cast( sum(pg_total_relation_size( schemaname  || '.' || tablename)) AS bigint)), schemaname
  FROM pg_tables t INNER JOIN pg_namespace d ON t.schemaname=d.nspname  GROUP BY schemaname;

  SELECT pg_size_pretty(cast( sum(pg_total_relation_size( schemaname  || '.' || tablename)) AS bigint)), schemaname
  FROM pg_tables t INNER JOIN pg_namespace d ON t.schemaname=d.nspname  WHERE schemaname in ('mad','mad_ods','dwbi_ods','coupon_ods') GROUP BY schemaname;

  SELECT pg_size_pretty(cast( sum(pg_total_relation_size( schemaname  || '.' || tablename)) AS bigint)), schemaname
  FROM pg_tables t INNER JOIN pg_namespace d ON t.schemaname=d.nspname  WHERE schemaname = 'mad' GROUP BY schemaname;

  --统计某张表的分区数量
  SELECT p.schemaname, p.tablename, count(*) prt_count
  FROM pg_partitions p
  WHERE p.tablename='表名（不含schema名称）' 
  GROUP BY 1,2
  ORDER BY 2;

  --统计AO分区表数量（不含子表）
  SELECT count(*) FROM pg_class c 
  LEFT JOIN pg_namespace n 
  ON c.relnamespace = n.oid 
  WHERE relstorage IN ('c','a') 
  AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule);

  --统计AO表分区数量
  SELECT count(*) FROM pg_class c 
  LEFT JOIN pg_namespace n 
  ON c.relnamespace = n.oid 
  WHERE relstorage IN ('c','a') 
  AND c.oid IN(SELECT parchildrelid FROM pg_partition_rule);

  --查询分区数量大于100的AO表
  SELECT * FROM
  (
  SELECT n.nspname, c.relname, count(*) part_count FROM pg_class c 
  LEFT JOIN pg_namespace n 
  ON c.relnamespace = n.oid 
  INNER JOIN pg_partitions p
  ON p.tablename=c.relname
  AND p.schemaname=n.nspname 
  WHERE c.relstorage IN ('c','a') 
  AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule) 
  GROUP BY n.nspname, c.relname 
  ) a
  WHERE part_count >= 100
  ORDER BY 3 DESC;

  --统计分区表大小（不含索引）
  SELECT p.schemaname,p.tablename,sum(sotdsize) 
  FROM pg_partitions p LEFT JOIN  (SELECT autnspname, autrelname, sotdsize  FROM gp_toolkit.__gp_user_tables a, gp_toolkit.gp_size_of_table_disk b  
  WHERE a.autoid = b.sotdoid) b on p.partitionschemaname = b.autnspname AND p.partitiontablename = b.autrelname 
  GROUP BY 1,2 
  ORDER BY 1,2;

  --统计某分区表大小（含索引）
  SELECT p.schemaname, p.tablename, round(sum(pg_total_relation_size(p.schemaname || '.' || p.partitiontablename))/1024/1024) "MB"
  FROM pg_partitions p
  WHERE p.tablename='employee_daily' 
  GROUP BY 1,2
  ORDER BY 2;

  --统计某schema下各分区表大小（含索引）
  SELECT p.schemaname, p.tablename, round(sum(pg_total_relation_size(p.schemaname || '.' || p.partitiontablename))/1024/1024) "MB"
  FROM pg_partitions p
  WHERE p.schemaname ='mad' 
  GROUP BY 1,2 
 ORDER BY 2;

  --查询所有非分区表名
  SELECT t.schemaname, t.tablename 
  FROM pg_tables t, pg_partitions p 
  WHERE t.tablename <> p.tablename AND t.tablename <> p.partitiontablename AND t.schemaname='mad' 
  GROUP BY 1,2
  ORDER BY 2;

  --查询所有表名（不含分区表子表）
  SELECT n.nspname,c.relname 
  FROM pg_class c, pg_namespace n 
  WHERE c.relnamespace = n.oid AND n.nspname NOT LIKE 'pg_%' AND n.nspname NOT LIKE 'gp_%' 
  AND n.nspname <> 'information_schema' AND relkind IN('r') AND relstorage <> 'x' 
  AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule);

  --Greenplum查询争用（表锁）
  SELECT l.locktype, l.database, c.relname, l.relation, l.transactionid, 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;

  --用函数杀SQL
  -------------------------------------
  --一般查询SQL
  SELECT pg_cancel_backend(procpid);

  --其他SQL
  SELECT pg_terminate_backend(procpid);
  -------------------------------------

  --查询某张表的数据分布均衡性
  SELECT gp_segment_id,count(*) FROM schemaname.tablename GROUP BY gp_segment_id ORDER BY count(*) DESC;

  --查询数据分布均匀性
  SELECT * FROM gp_toolkit.gp_skew_idle_fractions; --siffraction字段值大于0.1的需要重新选择分布键

  --保证全库统计信息是最新的情况下，查询表膨胀情况
  SELECT * FROM gp_toolkit.gp_bloat_diag; --bdidiag字段内容显示膨胀程度

  --查询用户情况
  SELECT rolname,rolsuper,rolinherit,rolcreaterole,rolcanlogin,rolconnlimit,rolresqueue,oid FROM pg_roles ORDER BY 8;

  --查询所有外部表
  SELECT n.nspname,c.relname 
  FROM pg_class c, pg_namespace n 
  WHERE c.relnamespace = n.oid AND n.nspname NOT LIKE 'pg_%' AND n.nspname NOT LIKE 'gp_%' 
  AND n.nspname <> 'information_schema' AND relkind IN('r') AND relstorage = 'x' 
  AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule);

  --重组表 消除膨胀
  \d+ dwbi01_ods.boh__bohrscsales
  ALTER TABLE dwbi01_ods.boh__bohrscsales SET WITH (REORGANIZE=TRUE) DISTRIBUTED RANDOMLY;
  ALTER TABLE dwbi01_ods.boh__bohrscsales SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (transaction_code);
  ANALYZE dwbi01_ods.boh__bohrscsales;

  --检测字段是否适合用作分布键
  select COUNT(bizdate), gp_segment_id from dwbi01_ods.boh__bohrscsales group by 2;

  --查询角色配置情况（使用资源组时）
  SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcanlogin, rsgname FROM pg_roles, pg_resgroup WHERE pg_roles.rolresgroup=pg_resgroup.oid;


  --查询膨胀率大于20的AO表
  SELECT nspname, relname, AVG(percent_hidden)
  FROM
  (

SELECT n.nspname, c.relname, (gp_toolkit.__gp_aovisimap_compaction_info(c.oid)).percent_hidden percent_hidden
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid
AND relstorage IN (‘c’,‘a’)
AND c.oid NOT IN(SELECT parchildrelid FROM pg_partition_rule)
) t
WHERE t.percent_hidden > 20
GROUP BY nspname, relname
ORDER BY 3 DESC;

        --查询溢出文件大小（详细）
        select * from gp_toolkit.gp_workfile_entries;

  --查询溢出文件大小
  select * from gp_toolkit.gp_workfile_usage_per_query;

  --查询每个Segment上的溢出文件大小
  select * from gp_toolkit.gp_workfile_usage_per_segment;

  --查询表的分布键
  SELECT att.nspname,att.relname,string_agg (a.attname, ',') attby 
  FROM 
  (
   SELECT c.oid,n.nspname,c.relname,regexp_split_to_table (array_to_string (d.attrnums, ','),',')::int as attnu
   FROM gp_distribution_policy d 
   LEFT JOIN pg_class c ON c.oid = d.localoid 
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
   WHERE c.oid = 'ods.oracletest'::regclass
  ) att
  LEFT JOIN pg_attribute a ON a.attrelid = att.oid
  WHERE att.attnu = a.attnum
  GROUP BY 1,2;

  --查询前20张大表
  SELECT tabs.nspname AS schema_name,
   COALESCE(parts.tablename, tabs.relname) AS table_name,
   ROUND(SUM(sotaidtablesize) / 1024 / 1024, 3) AS table_MB,
   ROUND(SUM(sotaidtablesize) / 1024 / 1024 / 1024, 3) AS table_GB,
   ROUND(SUM(sotaididxsize) / 1024 / 1024 / 1024, 3) AS index_GB,
   ROUND(SUM(sotaididxsize) / 1024 / 1024, 3) AS index_MB
  FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotd,
(
SELECT c.oid, c.relname, n.nspname
FROM pg_class c, pg_namespace n
WHERE n.oid = c.relnamespace
AND c.relname NOT LIKE '%_err'
) tabs
  LEFT JOIN pg_partitions parts 
  ON tabs.nspname = parts.schemaname
  AND tabs.relname = parts.partitiontablename
  WHERE sotd .sotaidoid = tabs.oid
  GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)
  ORDER BY 4 DESC
  LIMIT 20;

  --资源队列属性视图
  SELECT * FROM pg_catalog.pg_resqueue_attributes;



  --查询Filespace和Locating
  SELECT a.dbid,a.content,a.role,a.port,a.hostname,b.fsname,c.fselocation FROM gp_segment_configuration a,pg_filespace b,pg_filespace_entry c WHERE a.dbid=c.fsedbid AND b.oid=c.fsefsoid ORDER BY 2,6;

  --日期计算：
  riqi::date + interval '1 month' --"riqi"增加1月
  riqi::date - interval '1 day'   --"riqi"减去1天

  1.最后分析或真空或创建表或等...
     Select * from pg_stat_operations where schemaname='SCHEMA NAME '
   and actionname in ('ANALYZE','VACUUM') order by statime; 

  2.长时间查询空闲：
    Select * from pg_stat_activity order by query_start,backend_start;



  gpdb=# Select * from pg_stat_activity order by query_start,backend_start;
   datid |  datname  | procpid | sess_id | usesysid | usename |
current_query                            | waiting |          query_start

| backend_start | client_addr | client_port | application_name
| xact_start | waiting_reason | rsgid | rsgname | rsgqueueduratio
n
-------±----------±--------±--------±---------±--------±----------------------
---------------------------------------------±--------±---------------------------
—±------------------------------±--------------±------------±-----------------
±------------------------------±---------------±------±--------±---------------

| gpperfmon | 31604 | 2822 | 16558 | gpmon |
| f | 2019-03-20 21:19:40.079557-
| 2019-03-19 05:45:25.082823-04 | 192.168.0.221 | 62596 | gpcc
| | | 0 | unknown |
| gpperfmon | 7652 | 207 | 16558 | gpmon |
| f | 2019-03-20 21:19:45.883945-
| 2019-03-19 01:52:55.080215-04 | 192.168.0.221 | 45824 | gpcc
| | | 0 | unknown |
| gpdb | 6961 | 27890 | 10 | gpadmin | Select * from pg_stat_
activity order by query_start,backend_start; | f | 2019-03-20 21:19:47.667488-
| 2019-03-20 21:19:20.171974-04 | | -1 | psql
| 2019-03-20 21:19:47.667488-04 | | 0 | unknown |
(3 rows)

  3.如何在数据库中找到最大的表？
     SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;



  gpdb=#  SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
                        relname                             | relpages

----------------------------------------------------------------±---------
test_1 | 1672
test_index_1 | 1672
test99 | 1000
gp_disk_free | 1000
__gp_log_segment_ext | 1000
t1 | 1000
__gp_localid | 1000
__gp_masterid | 1000
__gp_log_master_ext | 1000
test_index_1_idx | 56
pg_proc | 20
pg_rewrite | 19
pg_attribute | 14
pg_depend | 14
pg_depend_reference_index | 13
pg_depend_depender_index | 13
pg_proc_proname_args_nsp_index | 9
test66 | 8
pg_statistic | 6
gp_persistent_relation_node | 6
pg_description | 6
pg_attribute_relid_attnam_index | 6
pg_attribute_relid_attnum_index | 5
pg_description_o_c_o_index | 5
test1 | 5
pg_proc_oid_index | 5
test2 | 4
test110 | 4
pg_operator | 4
gpcrondump_history | 3
pg_type | 3

  4.数据库中的前5个最大表
     SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5; 



  gpdb=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 5;
   relname       | relpages

---------------------±---------
test_index_1 | 1672
test_1 | 1672
__gp_localid | 1000
__gp_masterid | 1000
__gp_log_master_ext | 1000
(5 rows)

   relname– 关系/表的名称. 
   relpages - 关系页面（页数，默认情况下页面为8kb）
   pg_class– 系统表，维护关系的细节 
   limit 5 – 限制输出只显示5行。
        5.如何计算磁盘中的数据库大小？
    SELECT pg_database_size('Database Name' );
    SELECT pg_size_pretty(pg_database_size( 'Database Name' ));

gpdb=# SELECT pg_database_size(‘gpdb’ );
pg_database_size

(1 row)

gpdb=# SELECT pg_database_size(‘postgres’ );
pg_database_size

(1 row)

gpdb=# SELECT pg_size_pretty(pg_database_size( ‘gpdb’ ));
pg_size_pretty

MB
(1 row)

gpdb=# SELECT pg_size_pretty(pg_database_size( ‘postgres’ ));
pg_size_pretty

MB
(1 row)

        6.如何计算磁盘中的表大小？
    SELECT pg_size_pretty(pg_total_relation_size('public.test1'));

gpdb=# SELECT pg_size_pretty(pg_total_relation_size(‘public.test1’));
pg_size_pretty

kB
(1 row)

        7.如何查找表的大小（不包括索引）？
    SELECT pg_size_pretty(pg_relation_size('public.test1')); 

gpdb=# SELECT pg_size_pretty(pg_relation_size(‘public.test1’));
pg_size_pretty

kB
(1 row)

  8.如何生成一系列数字并将其插入表格中？
    INSERT INTO test2  (id) VALUES ( generate_series(1,1000));  



  gpdb=# INSERT INTO test3 (id) VALUES ( generate_series(1,1000));
  INSERT 0 1000
  gpdb=# SELECT * from test3 limit 3;
   id | name
  ----+------
  |
  |
  |
  (3 rows)

  9.如何计算表中的总行数？
    select count(*) from  test1;

gpdb=# select count(*) from test2;
count

(1 row)

  10.具有特定列值的行总数不为空。
   select count(id) from public.test1; 

gpdb=# select count(id) from public.test1;
count

(1 row)

  11.如何获取表中列的最大值？ - 第一秒
   select max( col_name) from  table ;
   SELECT MIN(col name) from table_namewhere num > (select MIN(col 

name) from table_name);
12.如何获得表中列的第二个最小值？ - 第一个和第二个
select min(col_name) from table;
SELECT MIN(col name) from table_namewhere num > (select MIN(col
name) from table_name);
13.如何查看GP中的基本可用数据类型。
SELECT typname,typlen from pg_type where typtype=‘b’;
typname – 数据类型的名称
typlen – 数据类型的长度

  gpdb=# SELECT typname,typlen from pg_type where typtype='b';
   typname       | typlen

---------------------±-------
bool | 1
bytea | -1
char | 1
name | 64
int8 | 8
int2 | 2
int2vector | -1
int4 | 4
regproc | 4
text | -1
oid | 4
tid | 6
xid | 4
cid | 4
oidvector | -1
xml | -1
_xml | -1
json | -1
_json | -1
complex | 16
_complex | -1
smgr | 2
point | 16
lseg | 32
path | -1
box | 32
polygon | -1
line | 32
_line | -1
float4 | 4
float8 | 8
abstime | 4
reltime | 4

  14.显示已关闭的segments。
  Select * from gp_segment_configuration where status='d'; 
  15.查找当前用户：
  SELECT SESSION_USER, CURRENT_USER; 

gpdb=# SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
--------------±-------------
gpadmin | gpadmin
(1 row)

  16.检查活动会话（工作负载）：
 SELECT * FROM pg_stat_activity; 

gpdb=# SELECT * FROM pg_stat_activity;
datid | datname | procpid | sess_id | usesysid | usename | current_query
| waiting | query_start | backend_start
| client_addr | client_port | application_name | xact_start | w
aiting_reason | rsgid | rsgname | rsgqueueduration
-------±----------±--------±--------±---------±--------±----------------------
----------±--------±------------------------------±------------------------------
±--------------±------------±-----------------±------------------------------±-
--------------±------±--------±-----------------
| gpperfmon | 7652 | 207 | 16558 | gpmon |
| f | 2019-03-20 21:28:10.079528-04 | 2019-03-19 01:52:55.080215-04
| 192.168.0.221 | 45824 | gpcc | |
| 0 | unknown |
| gpperfmon | 31604 | 2822 | 16558 | gpmon |
| f | 2019-03-20 21:28:25.079502-04 | 2019-03-19 05:45:25.082823-04
| 192.168.0.221 | 62596 | gpcc | |
| 0 | unknown |
| gpdb | 6961 | 27890 | 10 | gpadmin | SELECT * FROM pg_stat_
activity; | f | 2019-03-20 21:28:29.916239-04 | 2019-03-20 21:19:20.171974-04
| | -1 | psql | 2019-03-20 21:28:29.916239-04 |
| 0 | unknown |
(3 rows)

  17.正在队列中等待的查询
 SELECT * FROM gp_tookit.gp_resqueue_status; 

  18.查看数据库列表
  SELECT datname from pg_database;

gpdb=# SELECT datname from pg_database;
datname

template1
template0
postgres
gpdb
gpperfmon
test2
(6 rows)

  1.如何获取查询运行时和已用时间。


  例子:

Select tstart, tfinish, (tfinish-tstart) as total_time, trim(query_text)
from queries_history
Where tstart >= ‘2011-07-07 11:00:00’ and tstart < ‘2011-07-07 13:00:00’
and db =‘yourdatabasename’
Order by tstart;

  2。
  *查看上次执行的操作

gpdb=# SELECT schemaname as schema, objname as table, usename as role, actionname as action,
gpdb-# subtype as type, statime as time
gpdb-# FROM pg_stat_operations
gpdb-# WHERE objname=‘cust’;
schema | table | role | action | type | time
--------±------±-----±-------±-----±-----
(0 rows)

  *最后分析或创建表或ETC...

Select * from pg_stat_operations
where schemaname='SCHEMA NAME ’
and actionname in (‘ANALYZE’,‘VACUUM’)
order by statime;

gpdb=# Select * from pg_stat_operations
gpdb-# where schemaname='SCHEMA NAME ’
gpdb-# and actionname in (‘ANALYZE’,‘VACUUM’)
gpdb-# order by statime;
classname | objname | objid | schemaname | usestatus | usename | actionname | subty
pe | statime
-----------±--------±------±-----------±----------±--------±-----------±-----
—±--------
(0 rows)

  3.显示已关闭的细分。
  select * from gp_segment_configuration where status='d';
  4.如何计算磁盘中的数据库大小？
  select pg_size_pretty(pg_database_size('test1')); 
  5.如何计算磁盘中的表大小？
  select pg_size_pretty(pg_relation_size('gpdb' ));  
  6.对于分区表，您需要使用下面的sql来查找累积表大小
  select sum(pg_total_relation_size(tablename)) from pg_tables where tablename like 'table_name%' ;
  7。
  *授予/撤消模式中对象的权限的功能

  *撤销模式对象（表，视图，序列）权限的功能

  8. GreenPlum数据库GUI工具

  9.您可能喜欢的数据偏斜查询



  gpdb=# select gp_segment_id, count(*) , abs(count(*) - avg(count(*)) over(order by gp_segment_id rowsbetween unbounded preceding and unbounded following))/count(*) skewfrom  test1group by gp_segment_idorder by skew desclimit  10;
   gp_segment_id | count |          skew

---------------±------±-----------------------
| 2 | 0.40000000000000000000
| 1 | 0.20000000000000000000
| 1 | 0.20000000000000000000
| 1 | 0.20000000000000000000
| 1 | 0.20000000000000000000
(5 rows)

  10。
  *segments

gpdb=# SELECT count(*)::smallint AS numsegments FROM gp_segment_configurationWHERE gp_segment_configuration.preferred_role = ‘p’::“char” ANDgp_segment_configuration.content >= 0;
numsegments

(1 row)

*segments的大小

gpdb=# SELECT pg_size_pretty(dfspace)FROM gp_toolkit.gp_disk_freeORDER BY dfsegment;
pg_size_pretty

MB
MB
MB
MB
MB
MB
MB
MB
(8 rows)

        11.确定当前的主段配置

gpdb=# SELECT dbid, content, address as host_address, port, replication_port, fselocation as datadirFROM gp_segment_configuration, pg_filespace_entryWHERE dbid=fsedbidORDER BY dbid;
dbid | content | host_address | port | replication_port | datadir

------±--------±-------------±------±-----------------±------------------------

| -1 | greenplum01 | 5432 | | /greenplum/data/master/g
pseg-1
| 0 | greenplum02 | 6000 | 34000 | /greenplum/data/primary/
gpseg0
| 1 | greenplum02 | 6001 | 34001 | /greenplum/data/primary/
gpseg1
| 2 | greenplum02 | 6002 | 34002 | /greenplum/data2/primary
/gpseg2
| 3 | greenplum02 | 6003 | 34003 | /greenplum/data2/primary
/gpseg3
| 4 | greenplum03 | 6000 | 34000 | /greenplum/data/primary/
gpseg4
| 5 | greenplum03 | 6001 | 34001 | /greenplum/data/primary/
gpseg5
| 6 | greenplum03 | 6002 | 34002 | /greenplum/data2/primary
/gpseg6
| 7 | greenplum03 | 6003 | 34003 | /greenplum/data2/primary
/gpseg7
| 0 | greenplum03 | 43000 | 44000 | /greenplum/data/mirror/g
pseg0
| 1 | greenplum03 | 43001 | 44001 | /greenplum/data/mirror/g
pseg1
| 2 | greenplum03 | 43002 | 44002 | /greenplum/data2/mirror/
gpseg2
| 3 | greenplum03 | 43003 | 44003 | /greenplum/data2/mirror/
gpseg3
| 4 | greenplum02 | 43000 | 44000 | /greenplum/data/mirror/g
pseg4
| 5 | greenplum02 | 43001 | 44001 | /greenplum/data/mirror/g
pseg5
| 6 | greenplum02 | 43002 | 44002 | /greenplum/data2/mirror/

        12.时区设置：


  gpdb=# BEGIN; 
  gpdb=# SELECT NOW(); 
  gpdb=# SET timezone TO '-8';                               
  gpdb=# SELECT NOW();   

        13.Re-create GP Toolkit objects:

     连接gp命令:  
    psql -d 库名 -h ip地址 -p 端口号 -U 用户名;

    gp 添加分区: 
    alter table 表名 add partition d20190611 values('20190611') ;

    gp 添加字段:
    alter table 表名 add column update_flag varchar(255);

    gp 重命名:
    alter table 表名 rename column old_column_name to new_column_name;  

    gp 删除字段:
    ALTER TABLE 表名 DROP COLUMN rt_gather_price_remark;

    gp 修改字段的类型:
    alter table 表名 alter column rt_gather_price_remark varchar(200) text; 

    gp 删除表:
    drop table if exists 表名;

    gp 删除分区:
    alter table 表名 DROP partition d20190729;

    gp 清空分区数据:
    alter table 表名 truncate partition d20191010;

    gp 删除进程:
    select pg_cancel_backend(407367);

    gp 查看表结构:
    SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a 
    .atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where 
    c.relname='表名' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid order by attnum ;





    七、GreenPlum查看表和数据库大小
    表大小
    zwcdb=# select pg_size_pretty(pg_relation_size('gp_test'));
     pg_size_pretty 
    ----------------
     1761 MB
    (1 row)
    表和索引

    zwcdb=# select pg_size_pretty(pg_total_relation_size('gp_test'));
     pg_size_pretty 
    ----------------
     2186 MB
    查看指定数据库

    zwcdb=# select pg_size_pretty(pg_database_size('zwcdb'));
     pg_size_pretty 
    ----------------
     2241 MB
    所有数据库

    zwcdb=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
      datname  | pg_size_pretty 
    -----------+----------------
     zwcdb     | 2241 MB
     postgres  | 47 MB
     template1 | 47 MB
     template0 | 45 MB
     gpperfmon | 67 MB
    (5 rows)
    查看数据分布情况和磁盘空间

    zwcdb=# select gp_segment_id,count(*) from gp_test group by gp_segment_id order by 1;
       gp_segment_id |  count  
    ---------------+---------
         0 | 5000000
         1 | 4999999
         2 | 5000001
         3 | 5000000
      (4 rows)
    zwcdb=# select dfhostname, dfspace,dfdevice from gp_toolkit.gp_disk_free order by dfhostname;
     dfhostname | dfspace  |  dfdevice  
    ------------+----------+------------
      sdw1      | 12273372 |  /dev/sdb1
      sdw1      | 12273372 |  /dev/sdb1
      sdw2      | 12273404 |  /dev/sdb1
      sdw2      | 12273404 |  /dev/sdb1

greenplum集群状态恢复与同步
osc_r590b6ja
2019/05/26 15:38
阅读数 247
####查看集群状态

gpstate -m
或者也可以直接在gp集群里用sql查询

select * from gp_segment_configuration where status <>‘u’;
查看集群节点和实例的状态，发现有一台机子挂掉掉了，需要恢复节点和实例的状态。保守起见，在晚上没人用的时候停库恢复。

####集群启动

su - gpadmin
gpstart
####生成恢复文件

gprecoverseg -o ./reseg
####恢复

gprecoverseg -i ./reseg
####全部Synchronized后，改回节点状态

gprecoverseg -r
在集群恢复同步的过程中，可以通过 gpstate -m查看恢复进度，等down掉的实例状态从synchronizing变成synchronized，则同步完成

####master节点，关键恢复日志

20190522:19:24:08:006085 gprecoverseg:gp-master:gpadmin-[INFO]:-Starting gprecoverseg with args: -o ./reseg
20190522:19:24:08:006085 gprecoverseg:gp-master:gpadmin-[INFO]:-local Greenplum Version: ‘postgres (Greenplum Database) 4.3.29.0 build 1’
20190522:19:24:09:006085 gprecoverseg:gp-master:gpadmin-[INFO]:-master Greenplum Version: ‘PostgreSQL 8.2.15 (Greenplum Database 4.3.29.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Aug 22 2018 23:17:57’
20190522:19:24:12:006085 gprecoverseg:gp-master:gpadmin-[INFO]:-Configuration file output to ./reseg successfully.

20190522:19:24:57:031070 gprecoverseg:gp-master:gpadmin-[INFO]:-Starting gprecoverseg with args: -i ./reseg
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:-Greenplum instance recovery parameters
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:-Recovery 1 of 16
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:----------------------------------------------------------
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Synchronization mode = Incremental
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Failed instance host = gp-seg4
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Failed instance address = gp-seg4
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Failed instance directory = /data/mirror/gpseg16
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Failed instance port = 41000
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Failed instance replication port = 43000
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Recovery Source instance host = gp-seg3
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Recovery Source instance address = gp-seg3
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Recovery Source instance directory = /data/primary/gpseg16
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Recovery Source instance port = 40000
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Recovery Source instance replication port = 42000
20190522:19:25:02:031070 gprecoverseg:gp-master:gpadmin-[INFO]:- Recovery Target = in-place

20190522:19:25:20:031070 gprecoverseg:gp-master:gpadmin-[INFO]:-Updating configuration with new mirrors
20190522:19:25:21:031070 gprecoverseg:gp-master:gpadmin-[INFO]:-Updating mirrors
20190522:19:25:27:031070 gprecoverseg:gp-master:gpadmin-[INFO]:-Starting mirrors
####master节点，集群状态关键日志

20190522:16:29:20:062535 gpstate:gp-master:gpadmin-[INFO]:-# of up segments, from configuration table = 48
20190522:16:29:20:062535 gpstate:gp-master:gpadmin-[INFO]:-# of down segments, from configuration table = 16
20190522:16:29:20:062535 gpstate:gp-master:gpadmin-[INFO]:- Down Segment Datadir Port
20190522:16:29:20:062535 gpstate:gp-master:gpadmin-[INFO]:- gp-seg4 /data/mirror/gpseg16 41000
20190522:16:29:20:062535 gpstate:gp-master:gpadmin-[INFO]:- gp-seg4 /data/mirror/gpseg17 41001

20190522:19:33:31:006939 gpstate:gp-master:gpadmin-[WARNING]:-gp-seg1 /data/mirror/gpseg24 41000 Failed <<<<<<<<
20190522:19:34:16:007035 gpstate:gp-master:gpadmin-[INFO]:- gp-seg1 /data/mirror/gpseg24 41000 Passive Resynchronizing
20190522:19:35:17:007202 gpstate:gp-master:gpadmin-[INFO]:- gp-seg1 /data/mirror/gpseg24 41000 Passive Synchronized

####down掉的节点，关键恢复日志

20190522:19:25:27:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[INFO]:-Starting gpsegstart.py with args: -C en_US.utf8:en_US.utf8:en_US.utf8 -M quiescent -V postgres (Greenplum Database) 4.3.29.0 build 1 -n 32 --era

20190522:19:25:28:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[INFO]:-Validating directories…
20190522:19:25:28:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[INFO]:-Validating directory: /data/mirror/gpseg19
20190522:19:25:28:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[WARNING]:-postmaster.pid file exists, checking if recovery startup required
20190522:19:25:28:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[INFO]:-No db instance process, entering recovery startup mode
20190522:19:25:28:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[INFO]:-Clearing db instance pid file

20190522:19:25:30:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[INFO]:-Postmaster /data/mirror/gpseg19 is running (pid 25822)
20190522:19:25:31:025719 gpsegstart.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[INFO]:-
COMMAND RESULTS
STATUS–DIR:/data/mirror/gpseg19–STARTED:True–REASONCODE:0–REASON:Start Succeeded

20190522:16:27:56:024030 gpgetstatususingtransition.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[WARNING]:-Error getting data from segment /data/mirror/gpseg16; it is not running
20190522:16:27:56:024030 gpgetstatususingtransition.py_gp-seg4:gpadmin:gp-seg4:gpadmin-[WARNING]:-Error gettin