GP日常维护手册-常用命令

Greenplum 日常维护手册

1.数据库启动：gpstart

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

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

2.数据库停止：gpstop：

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

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

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

行的事务

-u：不停止数据库，只加载pg_hba.conf 和postgresql.conf中

运行时参数，当改动参数配置时候使用。

-f：强制停止数据库

-r：重启数据库

3.查看实例配置和状态

select * from gp_configuration order by 1 ;

select * from gp_configuration_history order by 1 ;

主要字段说明：

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

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

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

Port：实例运行的端口

Datadir:实例对应的数据目录

注 4.0后，实例配置的数据表：gp_segment_configuration 、pg_filespace_entry、gp_fault_strategy;

其它常用的系统表：pg_class,pg_attribute,pg_database,pg_tables……

可以用tab来匹配表名；

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

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

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

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

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

--version,查看数据库version

（也可使用pg_controldata查看数据库版本和postgresql版本）该命令默认列出数据库运行状态汇总信息，常用于日常巡检。

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

select * from pg_stat_activity该表能查看到当前数据库连接的IP 地址，用户

名，提交的查询等。另外也可以在master 主机上查看进程，对每个客户端连接，master 都会创建一个进程。ps -ef |grep -i postgres |grep -i con

pg_backend_pid()；

pg_cancel_backend(integer)

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

select pg_size_pretty(pg_relation_size('schema.tablename'));(与pg_tables子查询查看所有表的占用空间)

select pg_size_pretty(pg_database_size('zctt'));

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

7.收集统计信息，回收空间

定期使用Vacuum analyze tablename 回收垃圾和收集统计信息，尤其在大数据量删除，导入以后，非常重要、需要定期对数据字典做vacuum和reindex ，如

pg_class,pg_attribute等系统表。

可以gp_toolkit.gp_bloat_diag看膨胀很大的表；

8.查看数据分布情况

两种方式：

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

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

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

9.登录每一个实例数据库的方法

PGOPTIONS='-c gp_session_role=utility' psql dbname –p xxxx

可以通过这个命令登录实例，一般用于检测单个实例是否运行正常，还有就是用于在集群发生不一致时（只有在非常特殊的情况下才有可能发生，如表不一致等），进行表维护

10.实例恢复：gprecoverseg

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

gprecoverseg -F强制恢复

11.查看锁信息：

注：在Greenplum里（截至到4.0版本），INSERT 和COPY都是行级锁，

UPDATE/DELETE都是表级锁；

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;

主要字段说明：

relname: 表名

locktype、mode 标识了锁的类型

注：也可以通过pgadminIII的工具->服务器状态查看当前运行的SQL及锁等待情况。

发生Deadlock时，系统自动让其中一个SQL失败，并提示deadlock信息。

12.explain：在提交大的查询之前，使用explain分析执行计划、发现

潜在优化机会，避免将系统资源熬尽。

explain analyze sql,将执行一次SQL，并行将过程中的具体cost和运行情况记录下来

也可以用pgadminIII进行explain的图形化显示，进而来优化执行路径，如表关联策略等。

注：可以在Master的postgresql.conf中修改

log_min_duration_statement=5000s，SQL运行耗时超过这个值得都在pg_log纪录SQL的实际耗时，用来监控耗时的SQL。

13.数据库并行备份

gp_dump：

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

-n: 只导出某个schema

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

gp_catalog_1__ ：关于数据库系统配置的备份文件

gp_cdatabase_1__：数据库创建语句的备份文件

gp_dump_1__：数据库对象ddl语句

gp_dump_status_1__：备份操作的日志

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

gp_dump_0__：用户数据备份文件

gp_dump_status_0__：备份日志

gpcorndump：

可以用于备份数据库配置文件和全局数据（如角色、资源队列等）

Example:gpcrondump -x mydatabase –u /xxx/ -c -g –G –a –q >>$DATE.log

-g 备份postgresql.conf、pg_hba.conf、pg_ident.conf

-G 备份全局对象

-u 备份到的目录(如果没有-u，则数据备份到每个数据目录里面)

gpcrondump的文件可以用gp_restore恢复

gp_restore --gp-c --gp-d=/data/backup/db_dumps/20110423/ --gp-k=20110423103328 -d gpadmin

也可以用gpdbresotre恢复：(需要数据在一个Host上)

gpdbrestoree -R master=/data/backup/db_dumps/20110423

14.数据库恢复gp_restore

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

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

15.串行备份

pg_dump/pg_dumpall

一般用于环境迁移和DDL、FUNCTION等备份；

如：pg_dump -s -n public demodb –f demodb.sql ,将只备份demodb数据库中的public 下的所有DDL和FUNCTION，-s表示不备份数据；

恢复时，使用psql -d demodb –f demodb.sql

16.数据库僵尸进程处理

首先使用kill -15 去停止进程，kill -15 不响应，再用kill -11;一般情况下，不要用kill -9，否则可能导致数据库宕机且状态不一致，严重的导致数据库不能重新启动，可能需要用pg_resetxlog去重置日志，有可能导致数据丢失；

17.参数更改

通过gpconfig配置Master和所有Segment的postgresql.conf中参数。

EXAMPLES

gpconfig -c work_mem -v 120MB –masteronly

修改master上的work_mem =120MB

gpconfig -c max_connections -v 100 -m 10

修改Master上max_connections=10，Segment上修改成100

gpconfig -r default_statistics_target

注释参数，使用缺省参数

gpconfig –l

列出所有的参数

gpconfig -s max_connections

显示某个参数

最大连接数：show max_connections;

最大事务数：show max_prepared_transactions;

18.Greenplum其它常用命令

gpssh -f all_hosts—通过SSH同时访问多个节点，并可同时执行shell

gpcheckperf --检查网络和硬盘性能

checkos -f all_hosts –检查OS内核和参数

如：

检查Disk性能：

gpcheckperf -d /data/gpdb_p1 -d /data/gpdb_p2 -d /data/gpdb_p3 -d /data/gpdb_p4 -d /data/gpdb_p5 -d /data/gpdb_p6 -d /data/gpdb_p7 -d /data/gpdb_p8 -S 64GB -r ds -D -v -f hosts-setup

检查网络性能：

gpcheckperf -d /data/gpdb_p1 -r N -f hosts-net0

gpcheckperf -d /data/gpdb_p1 -r N -f hosts-net1

gpcheckperf -d /data/gpdb_p1 -r N -f hosts-net2

gpcheckperf -d /data/gpdb_p1 -r N -f hosts-net3

gpssh –h sdw1 –v –e ‘mkdir /data/test’

gpssh –h sdw1 –v –e ‘mkdir /data/test’

gpssh -h sdw1 -v -e 'mkdir /data/test'

19 copy导出、导入数据文件：

COPY public.ods_data_msg TO '/zte/gpdata/ocs_data_msg.txt' delimiter'|';

COPY public.ods_data_msg from '/zte/gpdata/ocs_data_msg.txt' WITH delimiter'|';

20 进程监控：

select * from pg_stat_activity where waiting ='t' ORDER BY current_query;

select * from pg_stat_activity where waiting ='t' ORDER BY sess_id;

select * from pg_stat_activity where waiting ='f' ORDER BY current_query;

select * from pg_stat_activity where waiting ='f' ORDER BY sess_id;

21 添加默认分区

ALTER TABLE INF.EVENT_TGPS_201307 ADD default PARTITION PDEFAULT ;

22 杀进程

登录数据库后先使用

select pg_cancel_backend(客户端进程ID);

如果无法杀掉则使用

select pg_terminate_backend(客户端进程ID);

23.导出对象

pg_dump -h 136.6.160.196 -p 5432 -U dsj -n dsj -s -T dsj.hot_contents_user_20130710 -T dsj.tbd_bwt_evt_pd_lo_evt_20130709 --gp-syntax cqdsj > db.sql

pg_dump -h 136.6.160.196 -p 5432 -U gpadmin -n public -s --gp-syntax xdcx > xdcx_public.sql pg_dump -h 136.6.160.196 -p 5432 -U gpadmin -n inf -s --gp-syntax xdcx > xdcx_inf.sql

pg_dump -h 136.6.160.196 -p 5432 -U gpadmin -n mid -s --gp-syntax xdcx > xdcx_mid.sql

pg_dump -h 136.6.160.196 -p 5432 -U inf -n public -s --gp-syntax xdcx > xdcx_public.sql

24.表的字段：

SELECT * FROM PG_ATTRIBUTE WHERE ATTRELID IN(SELECT RELFILENODE FROM PG_CLASS WHERE UPPER(RELNAME)=UPPER('DS_EVT_CALL_AREA_201312'))

and attstattarget='-1' order by attnum;

25.新建登陆用户

CREATE ROLE cqcis LOGIN

ENCRYPTED PASSWORD 'md5265eb7390afca3acc1f251fe55e63c00'

NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE RESOURCE QUEUE pg_default;

GRANT USAGE ON SCHEMA inf TO cqcis;

GRANT USAGE ON SCHEMA mid TO cqcis;

GRANT USAGE ON SCHEMA public TO cqcis;

26.模式管理（命令行）

查询当前可以直接访问的模式：SHOW search_path;

当前可以直接访问的模式：1、SET search_path TO temp,cqdmkt,public;---session级

2、alter database cqdmkt_new SET search_path TO temp,cqdmkt,public;---database级

---第一个模式为默认模式，即在新建表或者函数式，不加模式名的情况，都默认到第一个模式下。

27. 表的字段名称及注释的系统表查询

SELECT

a.nspname AS schemaname

,b.relname AS tablename

,d.attnum AS columnid

,d.attname AS columnname

,FORMAT_TYPE(d.atttypid,d.atttypmod) AS columntype

,e.description

FROM pg_catalog.pg_namespace a

JOIN pg_catalog.pg_class b ON a.oid=b.relnamespace AND b.relkind='r'

JOIN pg_catalog.pg_attribute d ON b.oid=d.attrelid AND d.attnum>=0

LEFT JOIN pg_catalog.pg_description e ON b.oid=e.objoid

AND b.tableoid=e.classoid

AND d.attnum=e.objsubid

where a.nspname= 'cqdmkt' --write your schema_name

and b.relname= 'wid_chn_prd_serv_mkt_day' --write your table_name

;

28.