Greenplum(gp) 维护使用笔记

本文最后更新于 1315 天前，其中的信息可能已经有所发展或是发生改变。

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

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;

SELECT tablename FROM pg_tables WHERE schemaname = 'smartinsight'  order by tablename

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

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

7.查看数据分布情况

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

8.实例恢复:gprecoverseg

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

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

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

进行恢复：gprecoverseg -i ./recov

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

primary mirror角色对调（可选的）:gprecoverseg -r

9.导出表结构

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

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

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

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

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

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

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

pg_dump -h localhost -U gpadmin lte_mr -t \"JS_PM_LTE_EUTRANCELLFDD_MINUTE_20210215\"> /data1/data_bk_tmp/lte_min/JS_PM_LTE_EUTRANCELLFDD_MINUTE_20210215.csv

pg_dump -h localhost -U gpadmin lte_mr -t \"JS_PM_LTE_EUTRANCELLFDD_MINUTE_20210216\" | gzip > /data1/data_bk_tmp/lte_min/JS_PM_LTE_EUTRANCELLFDD_MINUTE_20210216.sql.gz

#导出数据
pg_dump -h localhost -U gpadmin lte_mr -t zz_jiahuo202012 > zz_jiahuo202012.sql

#导入
psql -d lte_mr -f kpi_lte_nsa_daysummary_202011.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 ',';

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;

新增/建立分区：

CREATE TABLE mr_5g_coverage_not_all_data (
    CityID text,
    Time text,
    CityName text,
    County text,
    GridNum text,
    TLLongitude numeric,
    TLLatitude numeric,
    BRLongitude numeric,
    BRLatitude numeric,
    ReportNum_MR numeric,
    MRCnt_yd numeric,
    MRCnt_110_yd numeric,
    RSRPValue_yd numeric,
    RSRQValue_yd numeric,
    MRCnt_lt numeric,
    MRCnt_110_lt numeric,
    MRCnt_113_lt numeric,
    RSRPValue_lt numeric,
    RSRQValue_lt numeric,
    MRCnt_dx numeric,
    MRCnt_110_dx numeric,
    MRCnt_113_dx numeric,
    RSRPValue_dx numeric,
    RSRQValue_dx numeric,
    PCells text,
    PCellsMRCnt text,
    PCellsRSRPValue text,
    PCellsRSRQValue text
) DISTRIBUTED BY (Time) PARTITION BY RANGE(Time)        
(
PARTITION p_20210728 START (text '20210728') INCLUSIVE,
PARTITION p_20210811 START (text '20210811') INCLUSIVE,
PARTITION p_20210812 START (text '20210812') INCLUSIVE,
PARTITION p_20210813 START (text '20210813') INCLUSIVE,
PARTITION p_20210814 START (text '20210814') INCLUSIVE,
PARTITION p_20210815 START (text '20210815') INCLUSIVE,
PARTITION p_20210816 START (text '20210816') INCLUSIVE
END (text '20210817') EXCLUSIVE );

CREATE TABLE mr_5g_coverage (
    Time text,
    GridNum text,
    ECI text,
    MRCnt_yd numeric,
    MRCnt_110_yd numeric,
    MRCnt_113_yd numeric,
    RSRPValue_yd numeric,
    RSRQValue_yd numeric,
    MRCnt_lt numeric,
    MRCnt_110_lt numeric,
    MRCnt_113_lt numeric,
    RSRPValue_lt numeric,
    RSRQValue_lt numeric,
    MRCnt_dx numeric,
    MRCnt_110_dx numeric,
    MRCnt_113_dx numeric,
    RSRPValue_dx numeric,
    RSRQValue_dx numeric
) DISTRIBUTED BY (Time) PARTITION BY RANGE(Time)        
(
PARTITION p_20210728 START (text '20210728') INCLUSIVE,
PARTITION p_20210808 START (text '20210811') INCLUSIVE,
PARTITION p_20210809 START (text '20210811') INCLUSIVE,
PARTITION p_20210810 START (text '20210811') INCLUSIVE,
PARTITION p_20210811 START (text '20210811') INCLUSIVE,
PARTITION p_20210812 START (text '20210812') INCLUSIVE,
PARTITION p_20210813 START (text '20210813') INCLUSIVE,
PARTITION p_20210814 START (text '20210814') INCLUSIVE,
PARTITION p_20210815 START (text '20210815') INCLUSIVE,
PARTITION p_20210816 START (text '20210815') INCLUSIVE,
PARTITION p_20210817 START (text '20210816') INCLUSIVE
END (text '20210818') EXCLUSIVE );



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 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 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 tablename FROM pg_tables WHERE schemaname = schema  
   loop 
     grantstmt = 'GRANT SELECT ON "'|| quote_ident(schema) || '".' || 
quote_ident(r.tablename) || ' to "' || quote_ident(usr) || '"'; 

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

$$ language plpgsql;

--天津
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 tablename FROM pg_tables WHERE schemaname = schema  and tablename not like 'ext%' and tablename not like '%_1_prt_p%' and tablename not like 'icos%'  
   loop 
     grantstmt = 'GRANT SELECT ON "'|| quote_ident(schema) || '".' || 
quote_ident(r.tablename) || ' to "' || quote_ident(usr) || '"'; 

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

$$ language plpgsql;

14.正在执行语句

select *  from pg_stat_activity where datname = 'lte_mr' and current_query !='<IDLE>';

select *  from pg_stat_activity where datname = 'ids' and current_query !='<IDLE>';

15.linux 使远程连接

psql -U gpadmin -h 172.28.18.51 lte_mr
然后输入密码即可使用户testuser连接到主机为172.28.18.51上的testdb。

16.pgsql 解决锁表的问题

-查询是否锁表了
select oid from pg_class where relname='可能锁表了的表'
select pid from pg_locks where relation='上面查出的oid'
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid)

17.修改密码

ALTER USER gpadmin PASSWORD 'DK*vTfixW8Zz';

18进程占用内存(MEM)最高的前10个进程:

ps aux|head -1;ps aux|grep -v PID|sort -rn -k +4|head

19获取pgsql表的字段名、类型、注释、是否为空

SELECT a.attname,col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull FROM pg_class as c,pg_attribute as a where c.relname = 'mr_lte_day_202107' and a.attrelid = c.oid and a.attnum>0;

查询列名

select * from information_schema.columns where table_schema='smartinsight' and table_name='mr_lte_day_202107';

20 删除外部表

删除外部表 
drop external table if exists tableName

21.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

22.查询所有索引

SELECT
    n.nspname AS schemaname,
    c.relname AS tablename,
    i.relname AS indexname,
    t.spcname AS tablespace,
    pg_get_indexdef(i.oid) AS indexdef
FROM pg_index x
    JOIN pg_class c ON c.oid = x.indrelid
    JOIN pg_class i ON i.oid = x.indexrelid
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND i.relkind = 'i'::"char"
and c.relname like 'end2end%'

23.建分布式表+表压缩

create table tableName(
    a text,
    b text
)WITH (
APPENDONLY=true, COMPRESSLEVEL=5, ORIENTATION=column, COMPRESSTYPE=zlib,OIDS=FALSE
)
DISTRIBUTED BY (a,b);

24.表在各个节点数据的分布情况

select gp_segment_id,count(*) from table_name group by gp_segment_id;

25.杀掉greenplum、postgresql的会话进程

在linux命令行执行kill -s SIGTERM 杀掉该会话的进程

用函数杀sql

对于查询SQL
select pg_cancel_backend(procpid);

其他SQL
select pg_terminate_backend(procpid);