GreenPlum 实用手册（日常维护）
1.0 数据库的连接设置
1.0 数据库安装成功后，首先编辑连接配置:
打开$MASTER_DATA_DIRECTORY/pg_hba.conf进行编辑，拿当前安装的数据库举例：
vi /opt/greenplum/master/gpseg-1/pg_hba.conf
添加：
#host database role CIDR-address authentication-method
host all gpadmin 0.0.0.0/0 trust
或
host all all 0.0.0.0/0 md5
其中：trust表示信任关系，连接时无需输入密码；而为md5时，需要输入密码。
2.0 编辑结束保存后，执行如下命令：
gpstop -u
-u：不停止数据库，只加载pg_hba.conf 和postgresql.conf中运行时参数，当改动参数配置时候使用。
//表示gp重新加载
2.0 限制数据库的并发连接
配置文件所在位置：$MASTER_DATA_DIRECTORY/postgresql.conf，打开进行编辑（一般默认250，也可不做修改），如：
vi /opt/greenplum/master/gpseg-1/postgresql.conf
修改master主机配置为：
max_connections=100
max_prepared_transactions=100
修改segment主机配置为：
max_connections=500
max_prepared_transactions=100
变更允许连接数，步骤如下：
a) 在master主机上用gpadmin用户登录；
b) 执行：source /usr/local/greenplum-db/greenplum_path.sh
c) 设置max_connections、参数值
$ gpconfig -c max_connections -v 100 -m 500
$ gpconfig -c max_prepared_transactions -v 100
d) 重启GP数据库
gpstop –r
e) 在master主机与segment主机执行下面操作来进行check，
gpconfig -s max_connections
3.0 数据库的登录与退出
#正常登陆
psql gpdb
psql -d gpdb -h gphostm -p 5432 -U gpadmin
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "gpadmin")
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "gpadmin")
如：启动template1默认的数据库 psql -d template1
进入已连接的数据库，查看帮助信息，如下：
template1=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
#使用utility方式
PGOPTIONS="-c gp_session_role=utility" psql -h -d dbname hostname -p port
#退出
在psql命令行执行\q
4.0 创建数据库
4.1 创建用户及权限
进入template1数据库后，：
create user noas with LOGIN CONNECTION LIMIT 250 ENCRYPTED PASSWORD 'noas' ;
4.2 创建gpfilespace，如下：
gpadmin@linux-82:~> gpfilespace -o gpfilespace_config
20140418:14:21:07:058871 gpfilespace:linux-82:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.
20140418:14:21:07:058871 gpfilespace:linux-82:gpadmin-[INFO]:-getting config
Enter a name for this filespace
> noas
Checking your configuration:
Your system has 1 hosts with 2 primary and 2 mirror segments per host.
Your system has 1 hosts with 0 primary and 0 mirror segments per host.
Configuring hosts: [linux-84]
Please specify 2 locations for the primary segments, one per line:
primary location 1> /opt/greenplum/noas/primary
primary location 2> /opt/greenplum/noas/primary
Please specify 2 locations for the mirror segments, one per line:
mirror location 1> /opt/greenplum/noas/mirror
mirror location 2> /opt/greenplum/noas/mirror
Configuring hosts: [linux-82]
Enter a file system location for the master
master location> /opt/greenplum/master_noas
20140418:14:32:03:058871 gpfilespace:linux-82:gpadmin-[INFO]:-Creating configuration file...
20140418:14:32:03:058871 gpfilespace:linux-82:gpadmin-[INFO]:-[created]
20140418:14:32:03:058871 gpfilespace:linux-82:gpadmin-[INFO]:-
To add this filespace to the database please run the command:
gpfilespace --config /home/gpadmin/gpfilespace_config
4.3 创建日志目录：
gpadmin@linux-82:~> gpfilespace -c gpfilespace_config
利用上面已建的filespace，创建表空间tablespace：
create tablespace noas owner noas filespace noas;
4.4 后，建立数据库noas：
template1=# create database noas owner noas template template1 tablespace noas connection limit 250;
CREATE DATABASE
4.5 配置文件pg_hba.conf编辑
建好后需修改配置pg_hba.conf，
vi /opt/greenplum/master/gpseg-1/pg_hba.conf
添加下面的数据：
local all noas md5
5.0 数据库的常用操作
5.1 登录默认的数据库
gpadmin@linux-82:~> psql -d template1
psql (8.2.15)
Type "help" for help.
5.2 查看已启动数据库list：
template1=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+---------------------
noas | noas | UTF8 |
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
: admin=CTc/gpadmin
(4 rows)
5.3 创建schema
登录上面新建的数据库noas：
gpadmin@linux-82:~> psql -d noas -U noas
Password for user noas:
psql (8.2.15)
Type "help" for help.
创建schema，语法如下：
CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]，如：
noas=> create schema noas_app AUTHORIZATION noas;
CREATE SCHEMA
查看所有表列表：
noas=> select * from pg_tables;
6.0 数据库的启停
6.1 数据库的启动gpstart：
gpadmin@linux-82:~> gpstart
20140418:17:16:08:063671 gpstart:linux-82:gpadmin-[INFO]:-Starting gpstart with args:
20140418:17:16:08:063671 gpstart:linux-82:gpadmin-[INFO]:-Gathering information and validating the environment...
20140418:17:16:08:063671 gpstart:linux-82:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 4.3.0.0POC3 build 45206'
20140418:17:16:08:063671 gpstart:linux-82:gpadmin-[INFO]:-Greenplum Catalog Version: '201310150'
20140418:17:16:08:063671 gpstart:linux-82:gpadmin-[INFO]:-Starting Master instance in admin mode
20140418:17:16:09:063671 gpstart:linux-82:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20140418:17:16:09:063671 gpstart:linux-82:gpadmin-[INFO]:-Obtaining Segment details from master...
20140418:17:16:10:063671 gpstart:linux-82:gpadmin-[INFO]:-Setting new master era
20140418:17:16:10:063671 gpstart:linux-82:gpadmin-[INFO]:-Master Started...
20140418:17:16:10:063671 gpstart:linux-82:gpadmin-[INFO]:-Shutting down master
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:---------------------------
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:-Master instance parameters
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:---------------------------
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:-Database = template1
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:-Master Port = 5432
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:-Master directory = /opt/greenplum/master/gpseg-1
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:-Timeout = 600 seconds
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:-Master standby = Off
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:---------------------------------------
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:-Segment instances that will be started
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:---------------------------------------
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:- Host Datadir Port Role
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/primary/gpseg0 40000 Primary
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/mirror/gpseg0 50000 Mirror
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/primary/gpseg1 40001 Primary
20140418:17:16:11:063671 gpstart:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/mirror/gpseg1 50001 Mirror
Continue with Greenplum instance startup Yy|Nn (default=N):
> y
20140418:17:17:06:063671 gpstart:linux-82:gpadmin-[INFO]:-Commencing parallel primary and mirror segment instance startup, please wait...
................
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:-Process results...
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:-----------------------------------------------------
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:- Successful segment starts = 4
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:- Failed segment starts = 0
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:- Skipped segment starts (segments are marked down in configuration) = 0
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:-----------------------------------------------------
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:-
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:-Successfully started 4 of 4 segment instances
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:-----------------------------------------------------
20140418:17:17:22:063671 gpstart:linux-82:gpadmin-[INFO]:-Starting Master instance linux-82 directory /opt/greenplum/master/gpseg-1
20140418:17:17:23:063671 gpstart:linux-82:gpadmin-[INFO]:-Command pg_ctl reports Master linux-82 instance active
20140418:17:17:23:063671 gpstart:linux-82:gpadmin-[INFO]:-No standby master configured. skipping...
20140418:17:17:23:063671 gpstart:linux-82:gpadmin-[INFO]:-Database successfully started
6.2 数据库的停止gpstop：
gpadmin@linux-82:~> gpstop
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:-Starting gpstop with args:
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:-Gathering information and validating the environment...
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:-Obtaining Segment details from master...
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.0.0POC3 build 45206'
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:---------------------------------------------
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:-Master instance parameters
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:---------------------------------------------
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Master Greenplum instance process active PID = 6118
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Database = template1
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Master port = 5432
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Master directory = /opt/greenplum/master/gpseg-1
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Shutdown mode = smart
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Timeout = 600
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Shutdown Master standby host = Off
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:---------------------------------------------
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:-Segment instances that will be shutdown:
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:---------------------------------------------
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- Host Datadir Port Status
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/primary/gpseg0 40000 u
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/mirror/gpseg0 50000 u
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/primary/gpseg1 40001 u
20140418:17:15:18:061624 gpstop:linux-82:gpadmin-[INFO]:- linux-84 /opt/greenplum/mirror/gpseg1 50001 u
Continue with Greenplum instance shutdown Yy|Nn (default=N):
> y
20140418:17:15:31:061624 gpstop:linux-82:gpadmin-[INFO]:-There are 0 connections to the database
20140418:17:15:31:061624 gpstop:linux-82:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='smart'
20140418:17:15:31:061624 gpstop:linux-82:gpadmin-[INFO]:-Master host=linux-82
20140418:17:15:31:061624 gpstop:linux-82:gpadmin-[INFO]:-Commencing Master instance shutdown with mode=smart
20140418:17:15:31:061624 gpstop:linux-82:gpadmin-[INFO]:-Master segment instance directory=/opt/greenplum/master/gpseg-1
20140418:17:15:32:061624 gpstop:linux-82:gpadmin-[INFO]:-No standby master host configured
20140418:17:15:32:061624 gpstop:linux-82:gpadmin-[INFO]:-Commencing parallel primary segment instance shutdown, please wait...
...
20140418:17:15:35:061624 gpstop:linux-82:gpadmin-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait...
...
20140418:17:15:38:061624 gpstop:linux-82:gpadmin-[INFO]:-----------------------------------------------------
20140418:17:15:38:061624 gpstop:linux-82:gpadmin-[INFO]:- Segments stopped successfully = 4
20140418:17:15:38:061624 gpstop:linux-82:gpadmin-[INFO]:- Segments with errors during stop = 0
20140418:17:15:38:061624 gpstop:linux-82:gpadmin-[INFO]:-----------------------------------------------------
20140418:17:15:38:061624 gpstop:linux-82:gpadmin-[INFO]:-Successfully shutdown 4 of 4 segment instances
20140418:17:15:38:061624 gpstop:linux-82:gpadmin-[INFO]:-Database successfully shutdown with no errors reported
7.0 其他
确认监控采集进程是否已经启动：
ps -ef |grep gpmmon
gpstate ：显示Greenplum数据库运行状态，详细配置等信息
常用可选参数：-c：primary instance 和 mirror instance 的对应关系
-m：只列出mirror 实例的状态和配置信息
-f：显示standby master 的详细信息
-Q：显示状态综合信息
该命令默认列出数据库运行状态汇总信息，常用于日常巡检。
使用维护模式启动master
某种情况下，你可能只想启动master，这种情况称为维护模式。在维护模式下，你只能以工具模式连接master数据库实例，可以编辑系统目录表中的设置，这些操作是不会影响segment实例上的数据的。
使用维护模式启动master的命令：
1.Run gpstart using the -m option:
$ gpstart -m
2.连接到工具模式master进行目录表维护. 例如:
$ PGOPTIONS='-c gp_session_role=utility' psql template1
3.完成管理任务后，必须停止工具模式的master，才能以工作模式启动。
$ gpstop -m
本文来源：https://blog.csdn.net/u012671748/article/details/24036133
相关文章