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

2019-12-27 12:02:51 浏览数 (1)

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

目录：

  1. 启停数据库
  2. 访问数据库

基本概念：

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数据库系统

代码语言：javascript复制
[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数据库系统然后重新启动它。

代码语言：javascript复制
[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
  • 重载配置文件，系统不中断
代码语言：javascript复制
[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的话，有可能造成脑裂。

代码语言：javascript复制
[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实例。

代码语言：javascript复制
[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选项可以在关闭前回滚所有正在进行中的事务并且中断所有连接。

代码语言：javascript复制
[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连接
代码语言：javascript复制
$ psql -d gpdatabase -h master_host -p 5432 -U gpadmin
$ psql gpdatabase
$ psql postgres
代码语言：javascript复制
[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状态

代码语言：javascript复制
[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上做出适当的更改。

未完待续；

云数据仓库PostgreSQL

0 人点赞