Greenplum(gp) 维护使用笔记

1. 数据库启动：gpstart

常用可选参数：

-a : 直接启动，不提示终端用户输入确认

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

我的常用：gpstart -a

2.数据库停止：gpstop

常用可选参数：

-a：直接停止，不提示终端用户输入确认

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

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

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

-f：强制停止数据库

-r：重启数据库

我的常用：gpstop -M fast

3.查看实例配置和状态

 select * from gp_segment_configuration order by 1

1 2	select * from gp_segment_configuration order by 1

4.gpstate

常用可选参数：

-m：只列出mirror 实例的状态和配置信息-->常用gpstate -m 检查数据库是否正常

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

-s：查看详细状态，如在同步，可显示数据同步完成百分比

-e: 查看同步进程

-p: ...

-c: ...

我的常用：gpstate -m

5.查看用户会话和提交的查询等信息

select * from pg_stat_activity : 该表能查看到当前数据库连接的IP 地址，用户名，提交的查询等。

另外也可以在master 主机上查看进程，对每个客户端连接，master 都会创建一个进程。

ps -ef |grep -i postgres|grep -i con

6.查看数据库、表占用空间

SELECT tablename,pg_relation_size(tablename) FROM pg_tables WHERE schemaname = 'smartinsight'  limit 10;

1 2	SELECT tablename,pg_relation_size(tablename) FROM pg_tables WHERE schemaname = 'smartinsight' limit 10;

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

https://www.mobibrw.com/2018/16112

7.查看数据分布情况

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

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

8.实例恢复:gprecoverseg

通过 gpstate -m 或查询g p_segement_configuration 系统表发现有实例down 掉以后，使用该命令进行恢复。

首先产生一个恢复的配置文件： gprecoverseg -o ./recov

查询文件是否为恢复节点：cat recov

进行恢复：gprecoverseg -i ./recov

ps:gprecoverseg -F 强制恢复 -->不到迫不得已请勿执行，会停库

9.导出表结构

pg_dump命令可以导出数据库中的表结构

-s 选项用来只导出表结构，而不会导出表中的数据

-t 选项用来指定要导出的数据库表

pg_dump -s -t tablename dbname > /home/gpadmin/temp.sql

dbname 是数据库，tablename 是dbname 中的表，/home/gpadmin/temp.sql是定向到的文件.

如果数据库是远程的，需要指定主机，端口，用户等

pg_dump -h host -p port -U username -s -t tablename dbname > /home/gpadmin/temp.sql

10.数据库僵尸进程处理

首先使用kill -15 去停止进程，kill -15 不响应，再用kill -11;

一般情况下，不要用kill -9，否则可能导致数据库宕机且状态不一致，严重的导致数据库不能重新启动，可能需要用pg_resetxlog去重置日志，有可能导致数据丢失；

11.copy导出、导入数据文件

导出：
copy (select的结果集) to '导出文件存放路径' with 文件类型 header;
copy (select * from ditie) to '/home/gpadmin/ditie.csv' with csv header;

导入:
copy 表名 from '数据源路径' with 数据源文件类型 header;
copy ditie from '/home/gpadmin/ditie.csv' with csv header;

//将文件ditie.csv导入到数据表ditie,DELIMITER选项将不同的字符指定为值分隔符* 
copy ditie to '/home/gpadmin/ditie.csv' WITH DELIMITER AS ',';

导出：

copy (select的结果集) to '导出文件存放路径' with 文件类型 header;

copy (select * from ditie) to '/home/gpadmin/ditie.csv' with csv header;

导入:

copy 表名 from '数据源路径' with 数据源文件类型 header;

copy ditie from '/home/gpadmin/ditie.csv' with csv header;

//将文件ditie.csv导入到数据表ditie,DELIMITER选项将不同的字符指定为值分隔符*

copy ditie to '/home/gpadmin/ditie.csv' WITH DELIMITER AS ',';

12.建立分区/删除分区

删除分区：
ALTER TABLE 表名 DROP PARTITION p_200601;
ALTER TABLE 表名 DROP PARTITION p_20060102;
ALTER TABLE 表名 DROP PARTITION p_20060102_15;
alter table sts_misc5m_s6a_lu_1h drop partition p_20180122_00;

新增/建立分区：
ALTER TABLE yj_v2_raw_cgi_quality ADD PARTITION p_201902 START (text '20190201') INCLUSIVE END (text '20190301') EXCLUSIVE;

删除分区：

ALTER TABLE 表名 DROP PARTITION p_200601;

ALTER TABLE 表名 DROP PARTITION p_20060102;

ALTER TABLE 表名 DROP PARTITION p_20060102_15;

alter table sts_misc5m_s6a_lu_1h drop partition p_20180122_00;

新增/建立分区：

ALTER TABLE yj_v2_raw_cgi_quality ADD PARTITION p_201902 START (text '20190201') INCLUSIVE END (text '20190301') EXCLUSIVE;

13.创建用户

CREATE ROLE select_user login password 'Js_gp123!@#';
或者下面都可以：
CREATE USER select_user WITH PASSWORD 'Js_gp123!@#';

把USAGE权限给到select_user
grant usage on schema smartinsight to select_user;

#单独赋查询的权限
GRANT SELECT ON TABLE zz_pm_nr_nsa_nrcellcu_minute20200923 TO select_user;

GP集群虽然和pgsql差不多 但是也有本质区别因此不能使用：
grant select on all tables in schema smartinsight to select_user;
这个语句无效的详情：https://blog.csdn.net/weixin_42658788/article/details/88040209

解决办法-创建一个函数：
create or replace function grant_on_all_tables(schema text, usr text) 
returns setof text as $$ 
declare 
   r record ; 
   grantstmt text; 
begin 
   for r in select * from pg_class c, pg_namespace nsp 
       where c.relnamespace = nsp.oid AND c.relkind='r' AND nspname = schema and (relname like '%JS_MR_SERVICE_CELL_MINUTE_%' or relname like '%JS_PM_LTE_EUTRANCELL_MINUTE_%')
   loop 
     grantstmt = 'GRANT SELECT ON "'|| quote_ident(schema) || '"."' || 
quote_ident(r.relname) || '" to "' || quote_ident(usr) || '"'; 

     EXECUTE grantstmt; 
     return next grantstmt; 
   end loop; 
end; 

$$ language plpgsql;

CREATE ROLE select_user login password 'Js_gp123!@#';

或者下面都可以：

CREATE USER select_user WITH PASSWORD 'Js_gp123!@#';

把USAGE权限给到select_user

grant usage on schema smartinsight to select_user;

#单独赋查询的权限

GRANT SELECT ON TABLE zz_pm_nr_nsa_nrcellcu_minute20200923 TO select_user;

GP集群虽然和pgsql差不多但是也有本质区别因此不能使用：

grant select on all tables in schema smartinsight to select_user;

这个语句无效的详情：https://blog.csdn.net/weixin_42658788/article/details/88040209

解决办法-创建一个函数：

create or replace function grant_on_all_tables(schema text, usr text)

returns setof text as $$

declare

r record ;

grantstmt text;

begin

for r in select * from pg_class c, pg_namespace nsp

where c.relnamespace = nsp.oid AND c.relkind='r' AND nspname = schema and (relname like '%JS_MR_SERVICE_CELL_MINUTE_%' or relname like '%JS_PM_LTE_EUTRANCELL_MINUTE_%')

loop

grantstmt = 'GRANT SELECT ON "'|| quote_ident(schema) || '"."' ||

quote_ident(r.relname) || '" to "' || quote_ident(usr) || '"';

EXECUTE grantstmt;

return next grantstmt;

end loop;

end;

$$ language plpgsql;