Snova运维篇（一）：基础维护工具和命令

原创

snova-最佳实践

修改于 2019-12-27 12:02:51

6420

修改于 2019-12-27 12:02:51

文章被收录于专栏：Snova最佳实践系列 Snova最佳实践系列Snova最佳实践系列

本节主要从基础维护工具和命令开始逐步深入GP集群的维护工作。

目录：

启停数据库
访问数据库

基本概念：

JDBC	Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口
ODBC	ODBC（Open Database Connectivity，开放数据库互连）提供了一种标准的API（应用程序编程接口）方法来访问数据库管理系统（DBMS）。

1.启停数据库

gpstart	/usr/local/greenplum-db/bin/gpstart
gpstop	/usr/local/greenplum-db/bin/gpstop

启动Greenplum数据库

gpstart工具来启动一个已经由gpinitsystem工具初始化好但已经被gpstop工具停止的Greenplum数据库系统

[gpadmin@gp-master ~]$ gpstart
20191224:14:52:37:011410 gpstart:gp-master:gpadmin-[INFO]:-Starting gpstart with args:
20191224:14:52:37:011410 gpstart:gp-master:gpadmin-[INFO]:-Gathering information and validating the environment...
20191224:14:52:37:011410 gpstart:gp-master:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191224:14:52:37:011410 gpstart:gp-master:gpadmin-[INFO]:-Greenplum Catalog Version: '301705051'
20191224:14:52:37:011410 gpstart:gp-master:gpadmin-[INFO]:-Starting Master instance in admin mode
20191224:14:52:38:011410 gpstart:gp-master:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191224:14:52:38:011410 gpstart:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191224:14:52:38:011410 gpstart:gp-master:gpadmin-[INFO]:-Setting new master era
20191224:14:52:38:011410 gpstart:gp-master:gpadmin-[INFO]:-Master Started...
20191224:14:52:39:011410 gpstart:gp-master:gpadmin-[INFO]:-Shutting down master
20191224:14:52:40:011410 gpstart:gp-master:gpadmin-[INFO]:---------------------------
20191224:14:52:40:011410 gpstart:gp-master:gpadmin-[INFO]:-Master instance parameters
20191224:14:52:40:011410 gpstart:gp-master:gpadmin-[INFO]:---------------------------

重启gp

停止Greenplum数据库系统然后重新启动它。

[gpadmin@gp-master ~]$ gpstop -r
20191224:14:54:52:011895 gpstop:gp-master:gpadmin-[INFO]:-Starting gpstop with args: -r
20191224:14:54:52:011895 gpstop:gp-master:gpadmin-[INFO]:-Gathering information and validating the environment...
20191224:14:54:52:011895 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191224:14:54:52:011895 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191224:14:54:52:011895 gpstop:gp-master:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191224:14:54:52:011895 gpstop:gp-master:gpadmin-[INFO]:---------------------------------------------
20191224:14:54:52:011895 gpstop:gp-master:gpadmin-[INFO]:-Master instance parameters

重载配置文件，系统不中断

[gpadmin@gp-master ~]$ gpstop -u
20191224:14:56:52:012449 gpstop:gp-master:gpadmin-[INFO]:-Starting gpstop with args: -u
20191224:14:56:52:012449 gpstop:gp-master:gpadmin-[INFO]:-Gathering information and validating the environment...
20191224:14:56:52:012449 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191224:14:56:52:012449 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191224:14:56:52:012449 gpstop:gp-master:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191224:14:56:52:012449 gpstop:gp-master:gpadmin-[INFO]:-Signalling all postmaster processes to reload

以维护模式启动master

慎用此模式，生产环境不支持此模式，因为如果是启用了HA的话，有可能造成脑裂。

[gpadmin@gp-master ~]$ gpstart -m
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[INFO]:-Starting gpstart with args: -m
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[INFO]:-Gathering information and validating the environment...
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[INFO]:-Greenplum Catalog Version: '301705051'
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[WARNING]:-****************************************************************************
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[WARNING]:-Master-only start requested in a configuration with a standby master.
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[WARNING]:-This is advisable only under the direct supervision of Greenplum support.
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[WARNING]:-This mode of operation is not supported in a production environmentand
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[WARNING]:-may lead to a split-brain condition and possible unrecoverable dataloss.
20191224:15:01:40:013480 gpstart:gp-master:gpadmin-[WARNING]:-********************************************************************

停止gp

gpstop工具可以停止或者重启Greenplum数据库系统，它总是运行在Master主机上，gpstop会停止系统中所有的postgres进程，包括Master和所有的Segment实例。

[gpadmin@gp-master ~]$ gpstop
20191224:15:08:19:014772 gpstop:gp-master:gpadmin-[INFO]:-Starting gpstop with args:
20191224:15:08:19:014772 gpstop:gp-master:gpadmin-[INFO]:-Gathering information and validating the environment...
20191224:15:08:19:014772 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191224:15:08:19:014772 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191224:15:08:19:014772 gpstop:gp-master:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191224:15:08:19:014772 gpstop:gp-master:gpadmin-[INFO]:---------------------------------------------
20191224:15:08:19:014772 gpstop:gp-master:gpadmin-[INFO]:-Master instance parameters

使用-M fast选项可以在关闭前回滚所有正在进行中的事务并且中断所有连接。

[gpadmin@gp-master ~]$ gpstop -M fast
20191224:15:10:04:015092 gpstop:gp-master:gpadmin-[INFO]:-Starting gpstop with args: -M fast
20191224:15:10:04:015092 gpstop:gp-master:gpadmin-[INFO]:-Gathering information and validating the environment...
20191224:15:10:04:015092 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191224:15:10:04:015092 gpstop:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:---------------------------------------------
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:-Master instance parameters
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:---------------------------------------------
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:-   Master Greenplum instance process active PID   = 14703
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:-   Database                                       = template1
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:-   Master port                                    = 5432
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:-   Master directory                               = /data/master/gpseg-1
20191224:15:10:05:015092 gpstop:gp-master:gpadmin-[INFO]:-   Shutdown mode                                  = fast

2.访问数据库

建立会话

$PGAPPNAME	应用名称默认psql
$PGDATABASE	数据库名默认--postgres
$PGHOST	主机名
$PGPORT	端口默认5432
$PGUSER	数据库用户名

支持的客户端应用

命令行接口 psql

标准数据库应用程序接口 JDBC+ODBC

使用ODBC和JDBC等标准数据库接口的客户端工具

gp自带客户端应用

psql连接

$ psql -d gpdatabase -h master_host -p 5432 -U gpadmin
$ psql gpdatabase
$ psql postgres

[gpadmin@gp-master ~]$ psql komablog
psql (8.3.23)
Type "help" for help.

komablog=# select * from  users limit 2;
 id | player | score | team
----+--------+-------+------
  1 | 库里   |  28.3 | 勇士
  3 | 阿杜   |  25.6 | 勇士

使用PgBouncer连接池

PgBouncer工具管理用于PostgreSQL和Greenplum数据库连接的连接池。数据库连接池是一种数据库连接的缓存，在大量连接情况下，一般会在数据库和应用程序之间配置 pgbouncer，pgbouncer 可以配置在数据库主机上，也可以配置在单独一台服务器上。

PgBouncer的特点
    a.内存消耗低(默认为2k/连接)，因为Bouncer不需要每次都接受完整的数据包
    b.可以把不同的数据库连接到一个机器上，而对客户端保持透明
    c.支持在线的重新配置而无须重启

驱动接口

API	PostgreSQL驱动	下载链接
ODBC	psqlODBC	https://odbc.postgresql.org/.
JDBC	pgjdbc	https://jdbc.postgresql.org/
Perl DBI	pgperl	http://search.cpan.org/dist/DBD-Pg/
Python DBI	pygresql	http://www.pygresql.org/
libpq C Library	libpq	https://www.postgresql.org/docs/8.3/static/libpq.html

常见连接问题

在Greenplum的Master主机上运行gpstate工具来验证Greenplum数据库系统是否正常运行。

显示gp版本，postgresql版本，primary segment状态，mirror segment状态

[gpadmin@gp-master ~]$ gpstate
20191224:16:03:11:024757 gpstate:gp-master:gpadmin-[INFO]:-Starting gpstate with args:
20191224:16:03:11:024757 gpstate:gp-master:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191224:16:03:11:024757 gpstate:gp-master:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.3.23 (Greenplum Database 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Jul 24 2019 22:51:37'
20191224:16:03:11:024757 gpstate:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191224:16:03:11:024757 gpstate:gp-master:gpadmin-[INFO]:-Gathering data from segments...
.
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-Greenplum instance status summary
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-----------------------------------------------------
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Master instance                                = Active
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Master standby                                 = gp-standby
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Standby master state                           = Standby host passive
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total segment instance count from metadata     = 2
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-----------------------------------------------------
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Primary Segment Status
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-----------------------------------------------------
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total primary segments                         = 2
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total primary segment valid (at master)        = 2
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total primary segment failures (at master)     = 0
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total number of postmaster.pid files missing   = 0
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total number of postmaster.pid files found     = 2
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total number of postmaster.pid PIDs missing    = 0
20191224:16:03:12:024757 gpstate:gp-master:gpadmin-[INFO]:-   Total number of postmaster.pid PIDs found      = 2

默认情况下，Greenplum数据库被配置为在Master和每个Segment上分别允许最多250和750个并发用户连接。导致该限制会被超过的连接尝试将被拒绝。
这个限制由Greenplum数据库Master的postgresql.conf配置文件中的max_connections参数控制。如果用户为Master更改了这个设置，用户还必须在Segment上做出适当的更改。

未完待续；

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据仓库 PostgreSQL

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据仓库 PostgreSQL

作者已关闭评论

0 条评论

热度