GreenPlum 实用手册(日常维护)

2023年 8月 12日 86.3k 0

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

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论