greenplum的升级与连接池pgbouncer

greenplum系列

一：greenplum 的升级

1.1 greenplum的文件最新版本准备

下载地址：
   https://network.pivotal.io/products/vmware-tanzu-greenplum  
下载：
      greenplum-db-6.17.0-rhel7-x86_64.rpm

1.2 升级greenplum

上传新版本的GP 包到 gp的master节点，然后同步所有主机
切换到root
后执行命令：
  cd /home/gpadmin/    [全部节点]
  rpm -Uvh greenplum-db-6.17.0-rhel7-x86_64.rpm

重启一下GP 集群
 gpstop -a 
 gpstart -a 

二： gp的连接池pgbouncer

2.1 pgbouncer的概述：

PgBouncer工具管理用于PostgreSQL和Greenplum数据库连接的连接池。
Greenplum数据库安装包括PgBouncer连接池软件。下列主题描述了如何对Greenplum数据库设置和使用PgBouncer。有关如何把PgBouncer和PostgreSQL一起使用请见 PgBouncer网站
参考：
https://gp-docs-cn.github.io/docs/admin_guide/access_db/topics/pgbouncer.html
pgbouncer的官网
 http://www.pgbouncer.org/
 下载 pgbouncer的1.14 版本
      pgbouncer-1.14.0.tar.gz
    上传到node06.flyfish.com 主机

2.2 安装配置pgbouncer

安装 pgbouncer 
yum install libevent* 
yum install gcc gcc-c++ openssl*

tar -zxvf pgbouncer-1.14.0.tar.gz
./configure --prefix=/usr/local/pgbouncer
make 
make install 
mkdir /usr/local/pgbouncer/config
cp -p pgbouncer.ini userlist.txt /usr/local/pgbouncer/config/

useradd pgbouncer && echo pgbouncer |passwd pgbouncer --stdin
chown -R pgbouncer:pgbouncer /usr/local/pgbouncer 

cd /usr/local/pgbouncer/config
cp -p pgbouncer.ini pgbouncer.ini.bak 
vim pgbouncer.ini
[databases]
postgres = host=192.168.100.11 port=5432 dbname=postgres auth_user=postgres
gpperfmon = host=192.168.100.11 port=5432 dbname=gpperfmon auth_user=postgres
template0 = host=192.168.100.11 port=5432 dbname=template0 auth_user=postgres
template1 = host=192.168.100.11 port=5432 dbname=template1 auth_user=postgres
flyfish_db = host=192.168.100.11 port=5432 dbname=flyfish_db auth_user=postgres
[pgbouncer]
logfile = /usr/local/pgbouncer/pgbouncer.log
pidfile = /usr/local/pgbouncer/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = trust
auth_file = /usr/local/pgbouncer/config/userlist.txt

cp -p userlist.txt userlist.txt.bak
vim userlist.txt 
"flyfish" "flyfish123"
"postgres" "postgres123"
这两个账号前提是在greenplum 的数据库当中有授权
create user flyfish with password 'flyfish123';
create user postgres with password 'postgres123';

启动pgbouncer 
cd /usr/local/pgbouncer 
bin/pgbouncer -d config/pgbouncer.ini 

登录测试：
     psql -h 192.168.100.16 -p 6432 -U flyfish
     psql -h 192.168.100.16 -p 6432 -U postgres