Greenplum数据库要求定期执行特定的任务来达到最优性能。这里讨论的任务都是必须的，但数据库管理员可以使用标准的UNIX工具（如cron脚本）来自动化这些任务。管理员建立适当的脚本并且检查它们是否成功执行。关于用来保持Greenplum系统最优化运行的额外建议维护活动，请见Recommended Monitoring and Maintenance Tasks。

官方文档

一切尽在官方文档中......

http://docs-cn.greenplum.org/

http://docs-cn.greenplum.org/v6/homenav.html

https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/9ccd913ea8be24b9.md

日常运维查询命令

数据库启动：gpstart

常用可选参数： -a : 直接启动，不提示终端用户输入确认

-m:只启动master 实例，主要在故障处理时使用

数据库停止：gpstop

常用可选参数：-a：直接停止，不提示终端用户输入确认

-m：只停止master 实例，与gpstart –m 对应使用

-M fast：停止数据库，中断所有数据库连接，回滚正在运行的事务

-u：不停止数据库，只加载pg_hba.conf 和postgresql.conf中运行时参数，当改动参数配置时候使用。

-a用在shell里，最多用的还是-M fast。

查看实例配置和状态

select * from gp_segment_configuration order by 1 ;

1	select * from gp_segment_configuration order by 1 ;

主要字段说明：

Content：该字段相等的两个实例，是一对Ｐ（primary instance）和Ｍ（mirror Instance)

Isprimary：实例是否作为primary instance 运行

Valid：实例是否有效，如处于false 状态，则说明该实例已经down 掉。

Port：实例运行的端口

Datadir:实例对应的数据目录

集群状态查询

查询命令：gpstate

显示Greenplum数据库运行状态，详细配置等信息

常用可选参数：-c：primary instance 和 mirror instance 的对应关系

-m：只列出mirror 实例的状态和配置信息

-f：显示standby master 的详细信息

-Q：显示状态综合信息

该命令默认列出数据库运行状态汇总信息，常用于日常巡检。

评：最开始由于网卡驱动的问题，做了mirror后，segment经常down掉，用-Q参数查询综合信息还是比较有用的。

查看集群主实例与镜像实例同步状态

查询命令：gpstate -m

Status均为Passive，Data Status均为为Synchronized则正常，否则异常

负载查询

使用vmstat命令可以监控 CPU 使用、进程状态、内存使用、虚拟内存使用、硬盘输入/输出状态等信息，常使用方法为：vmstat [刷新延时刷新次数]

磁盘空间查询

数据库查看使用量


select dfsegment,dfhostname,dfdevice,round(dfspace/1024/1024,2) as free_disk_GB 
from gp_toolkit.gp_disk_free 
ORDER BY dfsegment  ;

-- dfspace 实例所在的文件系统的空闲磁盘空闲空间(千字节为单位)

select dfsegment,dfhostname,dfdevice,round(dfspace/1024/1024,2) as free_disk_GB

from gp_toolkit.gp_disk_free

ORDER BY dfsegment ;

-- dfspace 实例所在的文件系统的空闲磁盘空闲空间(千字节为单位)

系统命令行查看（在此例中“host_list”为所有服务器节点清单，“data”为数据存放目录，根据实际目录文件名查询即可）

gpssh -f host_list -e "df -h |grep data"

1	gpssh -f host_list -e "df -h \|grep data"

查询数据库、表占用空间

select pg_size_pretty(pg_relation_size('schema.tablename'));

select pg_size_pretty(pg_database_size('databasename'));

--  1.查看所有库大小
select sodddatname,sodddatsize/1024/1024 as db_size_MB 
from gp_toolkit.gp_size_of_database 
order by sodddatname;

-- 2.查看库下面的所有schema占用的磁盘空间
select sosdnsp,round(sosdschematablesize/1024/1024,2) schema_tb_size_MB,round(sosdschemaidxsize /1024/1024,2) as schema_idx_size_MB 
from gp_toolkit.gp_size_of_schema_disk;

-- 3.查看schema下所有表和索引占用的磁盘空间
SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024,2) as INDEX_SIZE_MB
FROM gp_toolkit.gp_size_of_all_table_indexes 
order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ;

-- 4.查看表占用空间(表大小)
select (pg_relation_size('public.tb_name'));

select sotuoid, sotuschemaname,sotutablename,round(cast(sotusize as integer )/1024/1024,2) as table_SIZE_MB  
from gp_toolkit.gp_size_of_table_uncompressed 
order by sotuschemaname, table_SIZE_MB desc,sotutablename

-- 5.查看膨胀表
select * from gp_toolkit.gp_bloat_diag 
order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname;

-- 没有统计信息且可能需要ANALYZE的表。
select * from gp_toolkit.gp_stats_missing order by smischema,smicols desc;

select pg_size_pretty(pg_relation_size('schema.tablename'));

select pg_size_pretty(pg_database_size('databasename'));

-- 1.查看所有库大小

select sodddatname,sodddatsize/1024/1024 as db_size_MB

from gp_toolkit.gp_size_of_database

order by sodddatname;

-- 2.查看库下面的所有schema占用的磁盘空间

select sosdnsp,round(sosdschematablesize/1024/1024,2) schema_tb_size_MB,round(sosdschemaidxsize /1024/1024,2) as schema_idx_size_MB

from gp_toolkit.gp_size_of_schema_disk;

-- 3.查看schema下所有表和索引占用的磁盘空间

SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024,2) as INDEX_SIZE_MB

FROM gp_toolkit.gp_size_of_all_table_indexes

order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ;

-- 4.查看表占用空间(表大小)

select (pg_relation_size('public.tb_name'));

select sotuoid, sotuschemaname,sotutablename,round(cast(sotusize as integer )/1024/1024,2) as table_SIZE_MB

from gp_toolkit.gp_size_of_table_uncompressed

order by sotuschemaname, table_SIZE_MB desc,sotutablename

-- 5.查看膨胀表

select * from gp_toolkit.gp_bloat_diag

order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname;

-- 没有统计信息且可能需要ANALYZE的表。

select * from gp_toolkit.gp_stats_missing order by smischema,smicols desc;

必须在数据库所对应的存储系统里，至少保留30%的自由空间，日常巡检，要检查存储空间的剩余容量。

评：可以查看任何数据库对象的占用空间，pg_size_pretty可以显示如mb之类的易读数据，另外，可以与pg_tables，pg_indexes之类的系统表链接，统计出各类关于数据库对象的空间信息。

查看GreenPlum表的分布键和压缩类型

SELECT
    n.nspname AS "schema",
    C.relname AS "tb_name",
CASE WHEN P.policytype = 'p' THEN '分区策略' 
        WHEN P.policytype = 'r' THEN '复制策略' 
END "policytype",
  P.numsegments,
    P.distkey,
    pg_get_table_distributedby ( C.oid ) "distributed_key",
    C.reloptions,
    case c.relstorage
  when 'a' then ' append-optimized'
  when 'c' then 'column-oriented'
  when 'h' then 'heap'
  when 'v' then 'virtual'
  when 'x' then 'external table'
 end as "data_storage_mode"
FROM
    pg_class  C 
LEFT JOIN gp_distribution_policy P ON C.oid = P.localoid
LEFT JOIN pg_namespace n ON C.relnamespace = n.oid 
where n.nspname NOT IN ( 'pg_catalog', 'pg_toast','information_schema','gp_toolkit' )
and  C.relkind in ('r','m') 
and P.policytype is not null
order by n.nspname,C.relname;

SELECT

n.nspname AS "schema",

C.relname AS "tb_name",

CASE WHEN P.policytype = 'p' THEN '分区策略'

WHEN P.policytype = 'r' THEN '复制策略'

END "policytype",

P.numsegments,

P.distkey,

pg_get_table_distributedby ( C.oid ) "distributed_key",

C.reloptions,

case c.relstorage

when 'a' then ' append-optimized'

when 'c' then 'column-oriented'

when 'h' then 'heap'

when 'v' then 'virtual'

when 'x' then 'external table'

end as "data_storage_mode"

FROM

pg_class C

LEFT JOIN gp_distribution_policy P ON C.oid = P.localoid

LEFT JOIN pg_namespace n ON C.relnamespace = n.oid

where n.nspname NOT IN ( 'pg_catalog', 'pg_toast','information_schema','gp_toolkit' )

and C.relkind in ('r','m')

and P.policytype is not null

order by n.nspname,C.relname;

查询对象类型和个数

select 
    nsp.nspname as SchemaName
    ,case cls.relkind
        when 'r' then 'TABLE'
        when 'm' then 'MATERIALIZED_VIEW'
        when 'i' then 'INDEX'
        when 'S' then 'SEQUENCE'
        when 'v' then 'VIEW'
        when 'c' then 'composite type'
        when 't' then 'TOAST'
        when 'f' then 'foreign table'
        when 'p' then 'partitioned_table'
        when 'I' then 'partitioned_index'
        else cls.relkind::text
    end as ObjectType,
    COUNT(*) cnt
from pg_class cls
join pg_namespace nsp 
 on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')
  and nsp.nspname not like 'pg_toast%'
GROUP BY nsp.nspname,cls.relkind
UNION all
SELECT n.nspname as "Schema",
 'pg_proc' as "pg_proc",
 COUNT(*) cnt
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')
GROUP BY n.nspname 
order by SchemaName,ObjectType;

select

nsp.nspname as SchemaName

,case cls.relkind

when 'r' then 'TABLE'

when 'm' then 'MATERIALIZED_VIEW'

when 'i' then 'INDEX'

when 'S' then 'SEQUENCE'

when 'v' then 'VIEW'

when 'c' then 'composite type'

when 't' then 'TOAST'

when 'f' then 'foreign table'

when 'p' then 'partitioned_table'

when 'I' then 'partitioned_index'

else cls.relkind::text

end as ObjectType,

COUNT(*) cnt

from pg_class cls

join pg_namespace nsp

on nsp.oid = cls.relnamespace

where nsp.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')

and nsp.nspname not like 'pg_toast%'

GROUP BY nsp.nspname,cls.relkind

UNION all

SELECT n.nspname as "Schema",

'pg_proc' as "pg_proc",

COUNT(*) cnt

FROM pg_catalog.pg_proc p

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

WHERE pg_catalog.pg_function_is_visible(p.oid)

AND n.nspname not in ('information_schema', 'pg_catalog','gp_toolkit')

GROUP BY n.nspname

order by SchemaName,ObjectType;

查询数据库大小

参考：https://www.xmmup.com/greenplumchaxunshujukudaxiao.html

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
       END as "Size",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
UNION ALL
SELECT '总计' as "Name",
       '' as "Owner",
       ''  as "Encoding",
       ''  as "Collate",
       ''  as "Ctype",
       ''  AS "Access privileges",
       pg_catalog.pg_size_pretty(SUM((pg_catalog.pg_database_size(d.datname)))) as "Size",
       ''  as "Tablespace",
       ''  as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;

-- 或
select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database d order by d.sodddatname ;

-- 或
\l+

SELECT d.datname as "Name",

pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

d.datcollate as "Collate",

d.datctype as "Ctype",

pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",

CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

ELSE 'No Access'

END as "Size",

t.spcname as "Tablespace",

pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"

FROM pg_catalog.pg_database d

JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid

UNION ALL

SELECT '总计' as "Name",

'' as "Owner",

'' as "Encoding",

'' as "Collate",

'' as "Ctype",

'' AS "Access privileges",

pg_catalog.pg_size_pretty(SUM((pg_catalog.pg_database_size(d.datname)))) as "Size",

'' as "Tablespace",

'' as "Description"

FROM pg_catalog.pg_database d

JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid

ORDER BY 1;

-- 或

select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database d order by d.sodddatname ;

-- 或

\l+

查看表数据分布情况

两种方式：

Select gp_segment_id,count(*) from tablename group by 1 ;

在命令运行： gpskew -t public.ate -a postgres

Select gp_segment_id,count(*) from tablename group by 1 ;

在命令运行： gpskew -t public.ate -a postgres

如数据分布不均匀，将发挥不了并行计算的优势，严重影响性能。

评：非常有用，gp要保障数据分布均匀。

会话、锁信息管理

会话查询

select datid,  --数据库OID
      datname,  --数据库名
      pid,  --后台服务器进程号(即对应该会话在master节点操作系统进程ID号)
      sess_id,  --会话线程号
      usesysid,  --role ID号
      usename,  --role名称
      client_addr,  --客户端地址
      client_port,  --客户端端口
      application_name,  --客户端应用名称
      substr(query,1,80)  --请求SQL，较长SQL截取部分
      query_start,  --请求开始时间
      backend_start,  --backend进程开始时间，即用户连接数据库时建立会话的时间
      xact_start,  --事务开始时间
      waiting,  --是否等待True if waiting on a lock, false if not waiting
      waiting_reason  --等待原因. The value can be:lock, replication, or resgroup
    from pg_stat_activity where state<>'idle';

select datid, --数据库OID

datname, --数据库名

pid, --后台服务器进程号(即对应该会话在master节点操作系统进程ID号)

sess_id, --会话线程号

usesysid, --role ID号

usename, --role名称

client_addr, --客户端地址

client_port, --客户端端口

application_name, --客户端应用名称

substr(query,1,80) --请求SQL，较长SQL截取部分

query_start, --请求开始时间

backend_start, --backend进程开始时间，即用户连接数据库时建立会话的时间

xact_start, --事务开始时间

waiting, --是否等待True if waiting on a lock, false if not waiting

waiting_reason --等待原因. The value can be:lock, replication, or resgroup

from pg_stat_activity where state<>'idle';

pg_stat_activity 该表能查看到当前数据库连接的IP 地址，用户名，提交的查询等。另外也可以在master 主机上查看进程，对每个客户端连接，master 都会创建一个进程。ps -ef |grep -i postgres |grep -i con

锁查询

SELECT pg_locks.pid as pid,  --进程ID
           transactionid as transaction_id, --事务ID
           nspname as schemaname,  --schema名
           relname as object_name,  --对象名
           locktype as lock_type,  --锁类型
           mode lock_mode,  --锁模式
           CASE
             WHEN granted = 'f' THEN
              'get_lock'
             WHEN granted = 't' THEN
              'wait_lock'
           END lock_satus,   --锁状态：持有锁|等待锁
           CASE
             WHEN waiting = 'false' THEN
              'already get lock,sql executing'
             WHEN waiting = 'true' THEN
              'waiting get lock,sql waiting execute'
           END waitting_satus,  --当前会话状态：执行中|等待中
           least(query_start, xact_start) AS query_start,  --query请求开始时间
           substr(query, 1, 25) AS query_text   --当前SQL语句
      FROM pg_locks
      LEFT OUTER JOIN pg_class
        ON (pg_locks.relation = pg_class.oid)
      LEFT OUTER JOIN pg_namespace
        ON (pg_namespace.oid = pg_class.relnamespace),  pg_stat_activity
     WHERE NOT pg_locks.pid = pg_backend_pid()
       AND pg_locks.pid = pg_stat_activity.pid
     ORDER BY query_start;

-- 正被锁定的关系
select 
lorlocktype 被锁的关系的类型,
lordatabase 对象标识符_共享对象为0,
lorrelname 关系的名字,
lorrelation 关系的对象标识符,
lortransaction 锁影响到的事务标识符,
lorpid 获取或等待锁的服务器进程号_准备型0,
lormode 锁模式,
lorgranted 锁是T否F,
lorcurrentquery 会话当前的查询
from gp_toolkit.gp_locks_on_relation;

select 
lorusename 当前会话用户,
lorrsqname 资源队列,
lorlocktype 锁对象类型,
lorobjid 锁定的事务对象标识符,
lortransaction 锁影响到的事务标识符,
lorpid 锁影响到的事务的服务器进程号,
lormode 锁模式的名字,
lorgranted 锁是True否alse,
lorwaiting 会话是否正在等待
 from gp_toolkit.gp_locks_on_resqueue;

-- GP 6.0不能使用了
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;

SELECT
    max_conn 最大连接数,
    now_conn 当前连接数,
    max_conn - now_conn 剩余连接数 
FROM
    ( SELECT setting::int8  AS max_conn, ( SELECT COUNT (*) FROM pg_stat_activity ) AS now_conn FROM pg_settings WHERE NAME = 'max_connections' ) T;

select a.*   from pg_stat_activity  a ;
-- select pg_terminate_backend(a.pid) from pg_stat_activity   a;
-- where pid<>pg_backend_pid();

SELECT pg_locks.pid as pid, --进程ID

transactionid as transaction_id, --事务ID

nspname as schemaname, --schema名

relname as object_name, --对象名

locktype as lock_type, --锁类型

mode lock_mode, --锁模式

CASE

WHEN granted = 'f' THEN

'get_lock'

WHEN granted = 't' THEN

'wait_lock'

END lock_satus, --锁状态：持有锁|等待锁

CASE

WHEN waiting = 'false' THEN

'already get lock,sql executing'

WHEN waiting = 'true' THEN

'waiting get lock,sql waiting execute'

END waitting_satus, --当前会话状态：执行中|等待中

least(query_start, xact_start) AS query_start, --query请求开始时间

substr(query, 1, 25) AS query_text --当前SQL语句

FROM pg_locks

LEFT OUTER JOIN pg_class

ON (pg_locks.relation = pg_class.oid)

LEFT OUTER JOIN pg_namespace

ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity

WHERE NOT pg_locks.pid = pg_backend_pid()

AND pg_locks.pid = pg_stat_activity.pid

ORDER BY query_start;

-- 正被锁定的关系

select

lorlocktype 被锁的关系的类型,

lordatabase 对象标识符_共享对象为0,

lorrelname 关系的名字,

lorrelation 关系的对象标识符,

lortransaction 锁影响到的事务标识符,

lorpid 获取或等待锁的服务器进程号_准备型0,

lormode 锁模式,

lorgranted 锁是T否F,

lorcurrentquery 会话当前的查询

from gp_toolkit.gp_locks_on_relation;

select

lorusename 当前会话用户,

lorrsqname 资源队列,

lorlocktype 锁对象类型,

lorobjid 锁定的事务对象标识符,

lortransaction 锁影响到的事务标识符,

lorpid 锁影响到的事务的服务器进程号,

lormode 锁模式的名字,

lorgranted 锁是True否alse,

lorwaiting 会话是否正在等待

from gp_toolkit.gp_locks_on_resqueue;

-- GP 6.0不能使用了

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;

SELECT

max_conn 最大连接数,

now_conn 当前连接数,

max_conn - now_conn 剩余连接数

FROM

( SELECT setting::int8 AS max_conn, ( SELECT COUNT (*) FROM pg_stat_activity ) AS now_conn FROM pg_settings WHERE NAME = 'max_connections' ) T;

select a.* from pg_stat_activity a ;

-- select pg_terminate_backend(a.pid) from pg_stat_activity a;

-- where pid<>pg_backend_pid();

会话kill

— 停止会话

当报Too many sessions会话数满或数据库无响应或事务被锁需要中断锁源头时，可以使用杀会话方式临时解决。优先建议使pg_cancel_backend()函数(长事务会话需要等待较长时间中断)，在无法等待较长时间等待时，可pg_terminate_backend()函数(该方式杀会话有极小概率导致系统元数据不一致)。在数据库响应等极端情况可，方可考虑使用 kill -9 pid进行强制会话进程中断，一般先使用 kill -15 pid终端会话进程。

正常取消会话

select pg_cancel_backend(pid);

1	select pg_cancel_backend(pid);

强制中断会话

select pg_terminate_backend(pid);

1	select pg_terminate_backend(pid);

其中：pid为pg_stat_activity视图中的pid查询结果，如：

 gpdw=# select pg_cancel_backend(2243);
     pg_cancel_backend
    -------------------
     t
    (1 row)
    gpdw=# select pg_terminate_backend(2243);
     pg_terminate_backend
    ----------------------
     t
    (1 row)

gpdw=# select pg_cancel_backend(2243);

pg_cancel_backend

-------------------

(1 row)

gpdw=# select pg_terminate_backend(2243);

pg_terminate_backend

----------------------

(1 row)

查询空闲会话ID并使用杀会话方式进行会话资源释放。

select pid from pg_stat_activity where state='idle';

1	select pid from pg_stat_activity where state='idle';

查询某时间段之后的会话

select pid from pg_stat_activity where query_start>CAST('2022-06-26 11:12:10' AS TIMESTAMP);

1	select pid from pg_stat_activity where query_start>CAST('2022-06-26 11:12:10' AS TIMESTAMP);

查询30分钟内会话

select * from pg_stat_activity where backend_start>(now() - interval '30 min'

1	select * from pg_stat_activity where backend_start>(now() - interval '30 min'

操作系统层面查看idle会话进程 kill后台进程

 ps -ef |grep -i postgres |grep -i idle|grep -v grep | awk '{print $2}' |xargs kill -15

1	ps -ef \|grep -i postgres \|grep -i idle\|grep -v grep \| awk '{print $2}' \|xargs kill -15

慢查询SQL

SELECT pgsa.pid,
      pgsa.datname AS datname, 
      pgsa.usename AS usename, 
      pgsa.client_addr client_addr, 
      pgsa.application_name AS application_name, 
      pgsa.state AS state, 
            pgsa.waiting,
            pgsa.waiting_reason,
      pgsa.backend_start AS backend_start, 
      pgsa.xact_start AS xact_start, 
      EXTRACT(
        epoch 
        FROM  (NOW() - pgsa.xact_start)
      ) AS xact_stay, 
      pgsa.query_start AS query_start, 
      EXTRACT(
        epoch 
        FROM  (NOW() - pgsa.query_start)
      ) AS query_stay, 
            state_change,
      REPLACE( QUERY, chr(10),  ' ') AS QUERY  
    FROM 
      pg_stat_activity AS pgsa 
    WHERE 
      pgsa.state not in ( 'idle' ,'idle in transaction','idle in transaction (aborted)' )
ORDER BY  query_stay DESC 
LIMIT  100;

SELECT pgsa.pid,

pgsa.datname AS datname,

pgsa.usename AS usename,

pgsa.client_addr client_addr,

pgsa.application_name AS application_name,

pgsa.state AS state,

pgsa.waiting,

pgsa.waiting_reason,

pgsa.backend_start AS backend_start,

pgsa.xact_start AS xact_start,

EXTRACT(

epoch

FROM (NOW() - pgsa.xact_start)

) AS xact_stay,

pgsa.query_start AS query_start,

EXTRACT(

epoch

FROM (NOW() - pgsa.query_start)

) AS query_stay,

state_change,

REPLACE( QUERY, chr(10), ' ') AS QUERY

FROM

pg_stat_activity AS pgsa

WHERE

pgsa.state not in ( 'idle' ,'idle in transaction','idle in transaction (aborted)' )

ORDER BY query_stay DESC

LIMIT 100;

登陆与退出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

#正常登陆

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

参数查询

psql -c 'SHOW ALL;' -d gpdb

gpconfig --show max_connections

psql -c 'SHOW ALL;' -d gpdb

gpconfig --show max_connections

这个有用，可以管道给grep。

创建数据库

createdb -h localhost -p 5432 dhdw

1	createdb -h localhost -p 5432 dhdw

创建GP文件系统


文件系统名

gpfsdw

子节点，视segment数创建目录

mkdir -p /gpfsdw/seg1
mkdir -p /gpfsdw/seg2
chown -R gpadmin:gpadmin /gpfsdw

主节点

mkdir -p /gpfsdw/master
chown -R gpadmin:gpadmin /gpfsdw
gpfilespace -o gpfilespace_config
gpfilespace -c gpfilespace_config

文件系统名

gpfsdw

子节点，视segment数创建目录

mkdir -p /gpfsdw/seg1

mkdir -p /gpfsdw/seg2

chown -R gpadmin:gpadmin /gpfsdw

主节点

mkdir -p /gpfsdw/master

chown -R gpadmin:gpadmin /gpfsdw

gpfilespace -o gpfilespace_config

gpfilespace -c gpfilespace_config

创建GP表空间


psql gpdb

create tablespace TBS_DW_DATA filespace gpfsdw;

SET default_tablespace = TBS_DW_DATA;

psql gpdb

create tablespace TBS_DW_DATA filespace gpfsdw;

SET default_tablespace = TBS_DW_DATA;

删除GP数据库


gpdeletesystem -d /gpmaster/gpseg-1 -f

1 2	gpdeletesystem -d /gpmaster/gpseg-1 -f

查看GP日志


SELECT * FROM gp_toolkit.__gp_log_master_ext d 
where d.logseverity not in ('LOG') 
and d.logtime >= now()-interval '7 day'
order by d.logtime desc 
limit 50;

SELECT * FROM gp_toolkit.__gp_log_segment_ext d
where d.logseverity not in ('LOG') 
and d.logtime >= now()-interval '7 day'
order by d.logtime desc 
limit 50;

SELECT * FROM gp_toolkit.__gp_log_master_ext d

where d.logseverity not in ('LOG')

and d.logtime >= now()-interval '7 day'

order by d.logtime desc

limit 50;

SELECT * FROM gp_toolkit.__gp_log_segment_ext d

where d.logseverity not in ('LOG')

and d.logtime >= now()-interval '7 day'

order by d.logtime desc

limit 50;

表数据分布

SELECT gp_segment_id, count(*) FROM <table_name> GROUP BY gp_segment_id;

1	SELECT gp_segment_id, count(*) FROM <table_name> GROUP BY gp_segment_id;

表占用空间


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

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

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

队列


SELECT * FROM pg_resqueue_status;

1 2	SELECT * FROM pg_resqueue_status;

gpfdist外部表


# 启动服务

gpfdist -d /share/txt -p 8081 –l /share/txt/gpfdist.log &

# 创建外部表，分隔符为’/t’

drop EXTERNAL TABLE TD_APP_LOG_BUYER;
CREATE EXTERNAL TABLE TD_APP_LOG_BUYER (
IP text,
ACCESSTIME text,
REQMETHOD text,
URL text,
STATUSCODE int,
REF text,
name text,
VID text)
LOCATION ('gpfdist://gphostm:8081/xxx.txt')
FORMAT 'TEXT' (DELIMITER E'/t'
FILL MISSING FIELDS) SEGMENT REJECT LIMIT 1 percent;

# 创建普通表

create table test select * from TD_APP_LOG_BUYER;

# 索引
CREATE INDEX idx_test ON test USING bitmap (ip);

# 查询数据

select ip , count(*) from test group by ip order by count(*);

# 启动服务

gpfdist -d /share/txt -p 8081 –l /share/txt/gpfdist.log &

# 创建外部表，分隔符为’/t’

drop EXTERNAL TABLE TD_APP_LOG_BUYER;

CREATE EXTERNAL TABLE TD_APP_LOG_BUYER (

IP text,

ACCESSTIME text,

REQMETHOD text,

URL text,

STATUSCODE int,

REF text,

name text,

VID text)

LOCATION ('gpfdist://gphostm:8081/xxx.txt')

FORMAT 'TEXT' (DELIMITER E'/t'

FILL MISSING FIELDS) SEGMENT REJECT LIMIT 1 percent;

# 创建普通表

create table test select * from TD_APP_LOG_BUYER;

# 索引

CREATE INDEX idx_test ON test USING bitmap (ip);

# 查询数据

select ip , count(*) from test group by ip order by count(*);


# 创建可写外部表

CREATE WRITABLE EXTERNAL TABLE unload_expenses
( LIKE expenses )
LOCATION ('gpfdist://etlhost-1:8081/expenses1.out',
'gpfdist://etlhost-2:8081/expenses2.out')
FORMAT 'TEXT' (DELIMITER ',')
DISTRIBUTED BY (exp_id);

# 写权限

GRANT INSERT ON writable_ext_table TO <name>;

# 写数据

INSERT INTO writable_ext_table SELECT * FROM regular_table;

# 创建可写外部表

CREATE WRITABLE EXTERNAL TABLE unload_expenses

( LIKE expenses )

LOCATION ('gpfdist://etlhost-1:8081/expenses1.out',

'gpfdist://etlhost-2:8081/expenses2.out')

FORMAT 'TEXT' (DELIMITER ',')

DISTRIBUTED BY (exp_id);

# 写权限

GRANT INSERT ON writable_ext_table TO <name>;

# 写数据

INSERT INTO writable_ext_table SELECT * FROM regular_table;

gpload


# 创建控制文件

# 加载数据

gpload -f my_load.yml

# 创建控制文件

# 加载数据

gpload -f my_load.yml

copy


COPY country FROM '/data/gpdb/country_data'
WITH DELIMITER '|' LOG ERRORS INTO err_country
SEGMENT REJECT LIMIT 10 ROWS;

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/home/gpadmin/a_list_countries.out';

COPY country FROM '/data/gpdb/country_data'

WITH DELIMITER '|' LOG ERRORS INTO err_country

SEGMENT REJECT LIMIT 10 ROWS;

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/home/gpadmin/a_list_countries.out';

执行sql文件


psql gpdbname –f yoursqlfile.sql

1 2	psql gpdbname –f yoursqlfile.sql

或者psql登陆后执行

i yoursqlfile.sql

1	i yoursqlfile.sql

查询gp最后vacuum时间

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables 
ORDER BY last_vacuum;

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze

FROM pg_stat_user_tables

ORDER BY last_vacuum;

磁盘空间不足

1.vacuum
-- 生成 vacuum ANALYZE命令（或者使用 VACUUM FULL）
select 'VACUUM FULL' || ' gpmg.' || relname || ';' as vacuum_sql from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'gpmg' and a.relkind='r';

-- 脚本执行命令
-- VCOMMAND="VACUUM ANALYZE"
-- psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from pg_class a,pg_namespace b   where a.relnamespace=b.oid and b.nspname= 'pg_catalog' and a.relkind='r'" $DBNAME | psql -a  $DBNAME

 2. 清理日志
-- pg_log目录下并使用约定命名方式：gpdb-YYYY-MM-DD.log

1.vacuum

-- 生成 vacuum ANALYZE命令（或者使用 VACUUM FULL）

select 'VACUUM FULL' || ' gpmg.' || relname || ';' as vacuum_sql from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'gpmg' and a.relkind='r';

-- 脚本执行命令

-- VCOMMAND="VACUUM ANALYZE"

-- psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'pg_catalog' and a.relkind='r'" $DBNAME | psql -a $DBNAME

2. 清理日志

-- pg_log目录下并使用约定命名方式：gpdb-YYYY-MM-DD.log

将查询结果导出到文件

nohup psql -c "show all;" >./test.log &
-- ps -fe | grep test.log

1 2	nohup psql -c "show all;" >./test.log & -- ps -fe \| grep test.log

例行清理和分析

Greenplum数据库中使用的MVCC事务并发模型的设计意味着被删除或者被更新的数据行仍在磁盘上占据物理空间，即便它们已经对新事务不可见。如果数据库进行了很多更新和删除，会有很多过期行存在并且它们所使用的空间必须使用 VACUUM命令来回收。VACUUM命令还会收集表级的统计信息，例如行数和页面数，因此即便无需从被更新或者被删除行回收空间，也还是有必要去清理追加优化表。

清理一个追加优化表遵循一种和清理堆表不同的处理逻辑。在每一个Segment上，会创建一个新的Segment文件并且把可见行从当前Segment复制到该文件中。当Segment文件被拷贝完时，将会安排删除原始的Segment文件并且让新的Segment文件变得可用。这要求足够的可用磁盘空间用于拷贝可见行，直到原始的Segment文件被删除为止。

如果一个Segment文件中隐藏行和所有行的比率低于一个阈值（默认是10），该Segment文件不会被紧缩。该阈值可以通过gp_appendonly_compaction_threshold服务器配置参数配置。VACUUM FULL 忽略gp_appendonly_compaction_threshold的值并且不管该比率为多少都会重写Segment文件。

可以使用gp_toolkit模式中的__gp_aovisimap_compaction_info()函数来查看追加优化表上的VACUUM操作的效果。

有关__gp_aovisimap_compaction_info()函数的信息请见Greenplum Database Reference Guide “检查追加优化表”部分

可以使用gp_appendonly_compaction服务器配置参数为追加优化表禁用 VACUUM操作。

有关清理数据库的细节请见清理数据库。

有关gp_appendonly_compaction_threshold服务器配置参数和VACUUM 命令的信息请见Greenplum Database Reference Guide。

事务ID管理

Greenplum的MVCC事务机制依赖于比较事务ID（XID）号来判断当前数据对于其他事务的可见性。事务ID号使用一种模232的算法来比较，因此一个运行了超过二十亿事务的 Greenplum系统可能会遇到事务ID回卷，即过去的事务变成了未来的事务。这意味着过去的事务的输出变得不可见。因此，每过二十亿个事务就有必要VACUUM每个数据库中的每个表至少一次。

Greenplum数据库只对涉及DDL或者DML操作的事务分配XID值，通常也只有这些事务需要XID。

Important: Greenplum数据库会监控事务ID。如果没有定期清理数据库，Greenplum 数据库将产生警告和错误。

当事务ID中相当多的一部分变得不再可用并且事务ID回卷还没有发生时，Greenplum数据库会发出下面的警告：

WARNING: database “database_name” must be vacuumed within number_of_transactions transactions

当这个警告被发出时，需要一次VACUUM操作。如果没有执行VACUUM 操作，当Greenplum数据库在事务ID回卷发生前达到一个限制，它会停止创建新的事务。在停止创建事务以避免可能的数据丢失时，Greenplum数据库会发出这个错误：

FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"

1	FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"

Greenplum数据库配置参数xid_warn_limit控制何时显示该警告。参数 xid_stop_limit控制何时Greenplum数据库停止创建事务。

从一次事务ID限制错误中恢复

当Greenplum数据库由于不频繁的VACUUM维护而达到 xid_stop_limit事务ID限制时，它会变得没有响应。为了从这种情况中恢复过来，作为数据库管理员执行下面的步骤：

关闭Greenplum数据库。
临时将xid_stop_limit降低10,000,000。
启动Greenplum数据库。
在所有受影响的数据库上运行VACUUM FREEZE。
将xid_stop_limit重置为原来的值。
重启Greenplum数据库。

有关这些配置参数的信息请见Greenplum Database Reference Guide。

有关事务ID回卷的信息请见PostgreSQL documentation.

系统目录维护

多次使用CREATE和DROP命令的数据库更新会增长系统目录尺寸并且影响系统性能。例如，运行很多次DROP TABLE语句会降低总体系统性能，因为在目录表上的元数据查询期间会需要更多扫描时间。性能损失会在数千次或者数万次DROP TABLE语句之间发生，具体时间取决于系统。

应该定期维护系统目录来回收已删除对象所占据的空间。如果长时间没有运行这种定期回收操作，那可能需要运行一个更彻底的回收操作来清理系统目录。这个主题会描述这两种方式。

常规系统目录维护

推荐周期性地在系统目录上运行REINDEX和VACUUM来清理系统索引和表中已删除对象所占用的空间。如果常规的数据库操作包括很多DROP语句，那么每天在非峰值时间用VACUUM命令运行一次系统目录维护是安全且适当的。用户可以在系统可用时执行这种操作。

下面是Greenplum数据库系统目录维护步骤。

在系统表上执行REINDEX命令以重建系统表索引。该操作移除索引膨胀并提高VACUUM 性能。
Note: 当在系统表上执行REINDEX操作时，表上会产生锁，此时可能会对当前正在运行的查询产生比较大的影响。建议在系统负载较低时执行REINDEX操作，以避免对正在运行的业务操作产生较大的干扰。
在系统表上执行VACUUM操作。
在系统表上执行ANALYZE操作，用以更新统计信息。

下面的示例脚本在一个Greenplum数据库系统目录上执行一次REINDEX、VACUUM 以及ANALYZE操作：将脚本中的替换为真实数据库名。

#!/bin/bash
DBNAME="<database-name>"
SYSTABLES="' pg_catalog.' || relname || ';' FROM pg_class a, pg_namespace b 
WHERE a.relnamespace=b.oid AND b.nspname='pg_catalog' AND a.relkind='r'"
reindexdb --system -d $DBNAME
psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME
analyzedb -s pg_catalog -d $DBNAME

#!/bin/bash

DBNAME="<database-name>"

SYSTABLES="' pg_catalog.' || relname || ';' FROM pg_class a, pg_namespace b

WHERE a.relnamespace=b.oid AND b.nspname='pg_catalog' AND a.relkind='r'"

reindexdb --system -d $DBNAME

psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME

analyzedb -s pg_catalog -d $DBNAME

Note: 如果在系统维护时间内已经开始了正常的系统目录维护操作，但是由于时间原因想要停止某一维护进程，此时可以运行Greenplum数据库函数pg_cancel_backend() 以安全停止该Greenplum数据库进程。

深度系统目录维护

如果很长时间都没有执行一次系统目录维护操作，该目录可能因为废弃空间而膨胀。这会导致简单的元数据操作都会等待很长时间。在psql中用\d命令列出用户表需要超过两秒的等待，就是目录膨胀的一种征兆。

如果发现系统目录膨胀的征兆，就必须在计划好的停机时段用VACUUM FULL执行一次深度系统目录维护操作。在这一时段中，停止系统上的所有目录活动，这种VACUUM FULL 系统目录维护过程会对系统目录加排他锁。

运行定期系统目录维护操作可以防止对这种更高开销操作的需求。

以下是深度系统目录维护操作的步骤。

停止Greenplum数据库系统上的所有活动元数据操作。
在系统表上执行REINDEX操作以重建系统表索引。该操作移除索引上的膨胀并提高 VACUUM操作性能。
在系统表上执行VACUUM FULL命令，具体查看下面的注释。
在系统表上执行ANALYZE命令以更新系统表的统计信息。

Note: 通常来说，表pg_attribute是最大的系统表。如果pg_attribute 表膨胀的特别厉害，该表上的VACUUM FULL操作可能需要更长的时间并且可能需要分开执行。以下两种情况的出现预示着pg_attribute表膨胀很厉害并且需要长时间的VACUUM FULL 操作：

pg_attribute表包含大量记录。
gp_toolkit.gp_bloat_diag视图中的信息显示pg_attribute的状态为significant amount of bloat

为查询优化进行清理和分析

Greenplum数据库使用一种基于代价的查询优化器，它依赖于数据库的统计信息。准确的统计信息帮助查询优化器更好的评估选择度以及一个查询操作检索的行数。这些评估会帮助它选择最有效的查询计划。ANALYZE 命令会为查询优化器收集列级的统计信息。

可以在同一个命令中同时执行VACUUM和ANALYZE操作。例如：

=# VACUUM ANALYZE mytable;

1	=# VACUUM ANALYZE mytable;

当在一个显著膨胀的表（显著膨胀的表磁盘空间被已删除或者已废弃行占据）上运行VACUUM ANALYZE 命令时，该命令可能会产生不正确的统计信息。对于大型的表，ANALYZE命令会从行的一个随机采样中计算统计信息。它会通过计算采样中每页的平均行数与表中实际页数的成绩来估算表中的总行数。如果采样包含很多空页，估计出的行计数可能会不准确。

对于一个表，可以在gp_toolkit视图gp_bloat_diag中查看有关未使用磁盘空间（被已删除或者已废弃行占据的空间）量的信息。如果一个表的bdidiag列包含值 significant amount of bloat suspected，那么相当多的表磁盘空间由未使用空间组成。在一个表被清理后，相关项会被加入到gp_bloat_diag视图中。

要从表中移除未使用的磁盘空间，可以在该表上运行命令VACUUM FULL。由于对表锁的需求， VACUUM FULL可能无法在非维护时段运行。

作为一种临时的变通方案，可以运行ANALYZE来计算列统计信息，然后在该表上运行 VACUUM来生成准确的行计数。这个例子在cust_info表上先运行ANALYZE，然后运行VACUUM。

ANALYZE cust_info;VACUUM cust_info;

1	ANALYZE cust_info;VACUUM cust_info;

Important: 如果想要在启用了GPORCA（默认启用）的情况下对分区表执行查询，必须用ANALYZE命令在分区表根分区上收集统计信息。有关GPORCA的信息，请见GPORCA概述。

Note: 可以使用Greenplum数据库工具analyzedb来更新表统计信息。表可以被并行地分析。对于追加优化表，只有统计信息不是当前值时，analyzedb才会更新统计信息。有关analyzedb 工具的信息，请见analyzedb。

常规的索引重建

对于B-树索引，一个刚刚构建的索引访问起来比一个已经更新过很多次的索引要快一点，因为在新构建的索引中逻辑上相邻的页面在物理上也相邻。定期地重建旧的索引可以提升访问速度。如果一个页面上除了索引以外的数据都被删除，那么索引页上对应的也会产生浪费的空间。重建索引操作会回收这些被浪费的空间。在Greenplum数据库中，删除索引 (DROP INDEX)再重建 (CREATE INDEX)通常比直接使用REINDEX 命令要快。

对于有索引的表列，批量更新或者插入之类的一些操作可能会执行得更慢，因为需要更新索引。为了提高带有索引的表上的批量操作性能，可以先删除掉索引，然后执行批量操作，最后再重建索引。

管理Greenplum数据库日志文件

数据库服务器日志文件

Greenplum数据库的日志输出常常会体量很大，尤其是在调试级别时，用户不需要无限期保存它。管理员可以应该定期清理日志文件。

Greenplum数据库在Master和所有的Segment实例上都启用了日志文件轮转。日志文件被创建在Master以及每个Segment的数据目录中的pg_log子目录下，这些文件使用命名规则：gpdb-YYYY-MM-DD_hhmmss.csv。数据库管理员需要编写脚本或程序定期清理Master以及每个Segment日志目录pg_log下的旧文件。

日志轮转可以被当前日志文件的大小或当前日志文件的年龄触发。log_rotation_size 配置参数设置触发日志轮转的单个日志文件大小。当日志文件大小等于或大于特定的大小时，该文件会停止写入，系统重新创建一个新的日志文件。log_rotation_size的值的单位为KB。默认为1048576，即1GB。如果log_rotation_size设置为0，表示基于文件大小的日志轮转被禁用。

log_rotation_age配置参数定义了出发日志轮转的日志文件年龄。当文件自创建时开始到达特定长度的时间时，该文件停止写入，系统重新创建一个新的日志文件。默认的log_rotation_age 时间为1d，即当前日志文件创建24小时之后。如果log_rotation_age设置为0，表示基于时间的轮转被禁用。

更多查看数据库日志文件的信息，请见查看数据库服务器日志文件.

管理工具日志文件

reenplum数据库管理工具的日志文件默认被写入到~/gpAdminLogs。管理日志文件的命名规则是：

script_name_date.log

1	script_name_date.log

日志详情的格式是：

timestamp:utility:host:user:[INFO|WARN|FATAL]:message

1	timestamp:utility:host:user:[INFO\|WARN\|FATAL]:message

每次一个工具运行时，就会向其每日的日志文件中增加与其执行相关的日志信息。

收集统计信息，回收空间

定期使用Vacuum analyze tablename 回收垃圾和收集统计信息，尤其在大数据量删除，导入以后，非常重要

评：这个说的不全面，vacuum分两种，一种是analize，优化查询计划的，还有一种是清理垃圾数据，postres删除工作，并不是真正删除数据，而是在被删除的数据上，坐一个标记，只有执行vacuum时，才会真正的物理删除，这个非常重用，有些经常更新的表，各种查询、更新效率会越来越慢，这个多是因为没有做vacuum的原因。

分析执行计划

explain：在提交大的查询之前，使用explain分析执行计划、发现潜在优化机会，避免将系统资源熬尽。

少写了个analyze，如果只是explain，统计出来的执行时间，是非常坑爹的，如果希望获得准确的执行时间，必须加上analyze。

集群修复

查看节点状态


select * from gp_segment_configuration where status='d' or mode <>'s' --查看segment处于down的状态

select * from gp_segment_configuration where status='d' or  mode <>'s'--观察数据库是否发生切换

select * from gp_segment_configuration where status='d' or mode <>'s' --查看segment处于down的状态

select * from gp_segment_configuration where status='d' or mode <>'s'--观察数据库是否发生切换

修复前负载判定(是否是实例宕机，是否需要kill会话,是否需要重启等)

在查看集群状态为异常后，进一步查看是否存在超过一个小时以上的会话，是否存在锁等情况

select * from gp_segment_configuration;
-- 以上语句查询结果中status存在'd'结果，则是存在实例宕机，可以往下继续判断
-- 如果均为'u'，可以参考后面的2.6小节
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;

select * from gp_segment_configuration;

-- 以上语句查询结果中status存在'd'结果，则是存在实例宕机，可以往下继续判断

-- 如果均为'u'，可以参考后面的2.6小节

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;

查看节点负载，可以使用1.2中的vmstat命令，也可以使用nmon监控工具（需安装）

c 查看CPU相关信息
m 查看内存相关信息

d 查看磁盘相关信息

n 查看网络相关信息

t 查看相关进程信息

h 查看帮助相关信息

日志备份([可选]判定实例>判定路径>备份日志)

    Select con.time,
    con.dbid,
    seg.content,
    seg.status,
    seg.port,
    seg.hostname,
    seg.datadir ,
    con."desc"
    from gp_segment_configuration seg ,gp_configuration_history con
    where con.dbid=seg.dbid and seg.status='d' order by con.time desc limit 10;

Select con.time,

con.dbid,

seg.content,

seg.status,

seg.port,

seg.hostname,

seg.datadir ,

con."desc"

from gp_segment_configuration seg ,gp_configuration_history con

where con.dbid=seg.dbid and seg.status='d' order by con.time desc limit 10;

集群实例宕机后首要是先恢复集群状态，但这恢复的时候数据节点日志往往会被清理掉，所以可以先备份当天节点日志后，先恢复集群，后面再查看日志寻找宕机的具体原因，根据seg.hostname和seg.datadir，以及 con.time备份对应实例pg_log目录下对应日期的日志

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

常规修复(以防突然断电或远程断开等情况，一般建议后台运行)

通过gpstate 或gp_configuration 发现有实例down 掉以后，使用该命令进行恢复。

nohup gprecoverseg -a &

1	nohup gprecoverseg -a &

若存在主备切换，则需要在修复完成后进行实例切回

 nohup gprecoverseg -ra &

1	nohup gprecoverseg -ra &

查看修复进度

gpstate -e

1	gpstate -e

全量修复方式

nohup gprecoverseg -Fa &

1	nohup gprecoverseg -Fa &

若存在主备切换，则需要在修复完成后进行实例切回

nohup gprecoverseg -ra &

1	nohup gprecoverseg -ra &

非实例宕机的

如果集群状态显示异常，但在2.1中查询结果并不存在实例宕机的情况，可以先查看是否是集群用户存在密码过期导致无法互信的情况

查看互信情况，发现其中一台服务器无法连接，ssh时并提示密码过期
这样就需要重新设置改服务器gpadmin用户的密码或者过期时间即可

用户管理

创建role/schema

创建用户可以使用CREATE USER 或者CREATE ROLE命令，唯一区别是CREATE USER默认情况下假定LOGIN，而CREATE ROLE默认情况下假定NOLOGIN.
如创建test用户可以登陆资源队列为 pg_default，密码为passwd：

 create role test with login  resource queue pg_default password 'passwd';

1	create role test with login resource queue pg_default password 'passwd';

创建模式

 Create schema test;

1	Create schema test;

如果是为角色创建一个同名模式：如

Create schema  authorization test;

1	Create schema authorization test;

创建资源队列

create resource queue rq_df with(active_statements=1,priority=medium);

1	create resource queue rq_df with(active_statements=1,priority=medium);

修改用户资源队列

alter role test with resource queue rq_df;

1	alter role test with resource queue rq_df;

查看用户资源队列对应情况

select rolname,rsqname from pg_roles,gp_toolkit.gp_resqueue_status where pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

1	select rolname,rsqname from pg_roles,gp_toolkit.gp_resqueue_status where pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

授权与回收权限

根据需要授权用户使用相应权限

GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |
    TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }
        ON { [TABLE] table_name [, ...]
             | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]
    GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] }
        ON { SEQUENCE sequence_name [, ...]
             | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |

TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }

ON { [TABLE] table_name [, ...]

| ALL TABLES IN SCHEMA schema_name [, ...] }

TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]

GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] }

ON { SEQUENCE sequence_name [, ...]

| ALL SEQUENCES IN SCHEMA schema_name [, ...] }

TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

例如：
授权用户使用模式权限(用户需要先有schema的使用权限，才能使用其他权限)

 GRANT USAGE ON SCHEMA schema to user;

1	GRANT USAGE ON SCHEMA schema to user;

授权用户查询表权限

grant select on tablename to user;

1	grant select on tablename to user;

postgresql 9.0 版本以后，如果是一个用户对另一个用户新建的表需要有查询等权限，可以使用一些语句，不用再对新建表去重新授权

alter default privileges for user test in schema test grant select on tables to test1;
--举例中，test用户在test模式下再建新表时，test1用户自动对test模式下新建的表有select权限

1 2	alter default privileges for user test in schema test grant select on tables to test1; --举例中，test用户在test模式下再建新表时，test1用户自动对test模式下新建的表有select权限

回收权限则是revoke命令，后面to 改为from即可，如回收用户使用模式权限

REVOKE  USAGE ON SCHEMA schema from user;

1	REVOKE USAGE ON SCHEMA schema from user;

在上例中，回收上面的权限的语句为：

alter default privileges for user test in schema test revoke select on tables from test1;

1	alter default privileges for user test in schema test revoke select on tables from test1;

权限查看

查看哪些用户对具体表有哪些权限

select * from information_schema.table_privileges where table_name ='test';

1	select * from information_schema.table_privileges where table_name ='test';

查看具体用户有哪些表的哪些权限

select * from information_schema.table_privileges where grantee='test' or grantor='test';

1	select * from information_schema.table_privileges where grantee='test' or grantor='test';

锁定role

—锁定用户

alter role test with nologin;

1	alter role test with nologin;

删除schema

—删除schema

DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT];

1	DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE \| RESTRICT];

删除role

—删除用户

DROP ROLE [IF EXISTS] test;

1	DROP ROLE [IF EXISTS] test;

访问策略管理

策略文件说明

Greenplum数据库连接访问及验证机制由pg_hba.conf配置文件控制，在Greenplum数据库系统中，master主机上的pg_hba.conf文件控制客户端的访问及验证，而segment节点上的pg_hba.conf文件已经默认只与master主机连接，不接受其他外部客户端的连接。pg_hba.conf目录为$MASTER_DATA_DIRECTORY。更新该策略文件后，使用gpstop -u生效。

—pg_hba.conf格式说明

该文件有如下5列，列间为空格，不区分大小写。

 登陆客户端类型 允许访问数据库 数据库角色名 允许连接客户端IP(段) 验证方式
    登陆客户端类型：host|local    --远程登录|本地登录
    允许访问数据库：dbname|all  --具体数据库名|所有数据库
    数据库角色名：rolename|all  --具体用户名|所有用户
    允许连接客户端IP(段)        --设置IP地址或IP地址段,格式如下：
    192.168.32.100/32        --指定IP地址192.168.32.100
    192.168.32.0/24            --指定192.168.32.0网段
    192.168.0.0/16            --指定192.168.0.0网段
    192.0.0.0/8                --指定192.0.0.0网段
    验证方式：ident|md5|password|trust|reject  --说明如下：
    ident：本地验证，即使用操作系统gpadmin用户验证。
    md5：密码验证
    password：发送明文密码至数据库验证(建议不用)
    trust:免密登录
    reject:拒绝登录，即黑名单。建议黑名单放在pg_hba.conf文件最末

登陆客户端类型允许访问数据库数据库角色名允许连接客户端IP(段) 验证方式

登陆客户端类型：host|local --远程登录|本地登录

允许访问数据库：dbname|all --具体数据库名|所有数据库

数据库角色名：rolename|all --具体用户名|所有用户

允许连接客户端IP(段) --设置IP地址或IP地址段,格式如下：

192.168.32.100/32 --指定IP地址192.168.32.100

192.168.32.0/24 --指定192.168.32.0网段

192.168.0.0/16 --指定192.168.0.0网段

192.0.0.0/8 --指定192.0.0.0网段

验证方式：ident|md5|password|trust|reject --说明如下：

ident：本地验证，即使用操作系统gpadmin用户验证。

md5：密码验证

password：发送明文密码至数据库验证(建议不用)

trust:免密登录

reject:拒绝登录，即黑名单。建议黑名单放在pg_hba.conf文件最末

策略配置及生效

gpstop -u

gpstop -u

策略登录验证

psql -d gp  -h ip -U test

1	psql -d gp -h ip -U test

正常连接到数据库则策略添加成功

备份恢复

数据库备份 gp_dump

常用参数：-s: 只导出对象定义（表结构，函数等）

-n: 只导出某个schema

gp_dump 默认在master 的data 目录上产生这些文件：

gp_catalog_1_<dbid>_<timestamp> ：关于数据库系统配置的备份文件

gp_cdatabase_1_<dbid>_<timestamp>：数据库创建语句的备份文件

gp_dump_1_<dbid>_<timestamp>：数据库对象ddl语句

gp_dump_status_1_<dbid>_<timestamp>：备份操作的日志

gp_dump 默认在master 的data 目录上产生这些文件：

gp_catalog_1_<dbid>_<timestamp> ：关于数据库系统配置的备份文件

gp_cdatabase_1_<dbid>_<timestamp>：数据库创建语句的备份文件

gp_dump_1_<dbid>_<timestamp>：数据库对象ddl语句

gp_dump_status_1_<dbid>_<timestamp>：备份操作的日志

在每个segment instance 上的data目录上产生的文件：

gp_dump_0_<dbid>_<timestamp>：用户数据备份文件

gp_dump_status_0_<dbid>_<timestamp>：备份日志

gp_dump_0_<dbid>_<timestamp>：用户数据备份文件

gp_dump_status_0_<dbid>_<timestamp>：备份日志

数据库恢复gp_restore

必选参数：--gp-k=key ：key 为gp_dump 导出来的文件的后缀时间戳

-d dbname ：将备份文件恢复到dbname

参考

https://mp.weixin.qq.com/s/G5cPCt7remAuDJHGadOI2w
http://t.zoukankan.com/butterflies-p-12056514.html

https://blog.csdn.net/weixin_45428187/article/details/125600114

https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/74e3477d2a3f3548.md

打赏赞(3)

标签： DBA脚本 Greenplum 日常维护日常运维脚本脚本分享

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

GreenPlum数据库日常维护运维（持续更新）

官方文档

日常运维查询命令

数据库启动：gpstart

数据库停止：gpstop

查看实例配置和状态

集群状态查询

查看集群主实例与镜像实例同步状态

负载查询

磁盘空间查询

查询数据库、表占用空间

查看GreenPlum表的分布键和压缩类型

查询对象类型和个数

查询数据库大小

查看表数据分布情况

会话、锁信息管理

会话查询

锁查询

会话kill

慢查询SQL

登陆与退出Greenplum

参数查询

创建数据库

创建GP文件系统

创建GP表空间

删除GP数据库

查看GP日志

表数据分布

表占用空间

索引占用空间

OBJECT的操作统计

队列

gpfdist外部表

gpload

copy

执行sql文件

查询gp最后vacuum时间

磁盘空间不足

将查询结果导出到文件

例行清理和分析

事务ID管理

从一次事务ID限制错误中恢复

系统目录维护

常规系统目录维护

深度系统目录维护

为查询优化进行清理和分析

常规的索引重建

管理Greenplum数据库日志文件

数据库服务器日志文件

管理工具日志文件

收集统计信息，回收空间

分析执行计划

集群修复

查看节点状态

修复前负载判定(是否是实例宕机，是否需要kill会话,是否需要重启等)

日志备份([可选]判定实例>判定路径>备份日志)

常规修复(以防突然断电或远程断开等情况，一般建议后台运行)

查看修复进度

全量修复方式

非实例宕机的

用户管理

创建role/schema

授权与回收权限

权限查看

锁定role

删除schema

删除role

访问策略管理

备份恢复

数据库备份 gp_dump

数据库恢复gp_restore

参考

相关文章

您可能还喜欢...

GreenPlum数据库之gp_segment_configuration视图信息

MPP架构之Greenplum的安装配置

GreenPlum中的序列

发表回复 取消回复

博客寄语

博客概况

发表回复取消回复