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