PG基于pgpool-II实现读写分离和负载均衡

PG基于pgpool-II实现读写分离和负载均衡

PG:12

pgpool-II:4.2.2

OS：CentOS7.6

IP	主机名	作用	port	类型	备注
172.72.6.2	mambapg64302	Master	5432	写入	对外提供写服务
172.72.6.3	mambapg64303	slave node1	5432	读	对外提供读服务
172.72.6.4	mambapg64304	slave node2	5432	读	对外提供读服
172.72.6.6	wcbpgpool	负载均衡和读写分离	999	中间件	pgpool-II实现负载均衡和读写分离

1、pgpool-II简介

官网：https://www.pgpool.net/mediawiki/index.php/Main_Page

下载：https://www.pgpool.net/mediawiki/index.php/Downloads

https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/

文档：https://www.pgpool.net/docs/latest/en/html/

2、安装pgpool-II

[root@wcbpg ~]# docker run -d --name wcbpgpool -h wcbpgpool \
  --net=pg-network --ip 172.72.6.6 \
  -p 9999:9999 -p 9898:9898 -p 181:80 -p 33390:3389 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/lhrcentos76:8.2 \
  /usr/sbin/init
[root@wcbpg ~]# docker network connect bridge wcbpgpool
[root@wcbpg ~]# docker restart wcbpgpool
wcbpgpool
[root@wcbpg ~]# docker exec -it wcbpgpool bash
[root@wcbpgpool /]# 

#创建用户
[root@wcbpgpool /]# groupadd -g 60000 pgsql
[root@wcbpgpool /]# useradd -u 60000 -g pgsql pgsql
[root@wcbpgpool /]# echo "wcb" |passwd --stdin pgsql
Changing password for user pgsql.

passwd: all authentication tokens updated successfully.

#创建响应目录
[root@wcbpgpool /]# mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg13,soft}
[root@wcbpgpool /]# chown -R pgsql:pgsql /postgresql
[root@wcbpgpool /]# chmod -R 775 /postgresql
#编译pg
[root@wcbpgpool /]# su - pgsql
[pgsql@wcbpgpool ~]$ cd /postgresql/soft
#在宿主机把安装包拷贝到容器内 docker cp  postgresql-13.2.tar.gz wcbpgpool:/postgresql/soft
[pgsql@wcbpgpool ~]$ tar zxvf postgresql-13.2.tar.gz
[pgsql@wcbpgpool soft]$ cd postgresql-13.2
[pgsql@wcbpgpool postgresql-13.2]$ ./configure --prefix=/postgresql/pg13 --without-readline
[pgsql@wcbpgpool postgresql-13.2]$ make -j 8 && make install

#配置环境变量
[pgsql@wcbpgpool postgresql-13.2]$cat >>  ~/.bash_profile <<"EOF"
export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg13
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF
[pgsql@wcbpgpool postgresql-13.2]$ source  ~/.bash_profile

#安装pgpool
[root@wcbpgpool /]# su - pgsql
Last login: Sun Dec 26 18:06:34 CST 2021 on pts/0
[pgsql@wcbpgpool ~]$ cd /postgresql/soft
[pgsql@wcbpgpool soft]$ wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.2.2.tar.gz
--2021-12-26 18:06:56--  https://www.pgpool.net/mediawiki/images/pgpool-II-4.2.2.tar.gz
Resolving www.pgpool.net (www.pgpool.net)... 13.227.66.75, 13.227.66.91, 13.227.66.22, ...
Connecting to www.pgpool.net (www.pgpool.net)|13.227.66.75|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4734840 (4.5M) [application/x-gzip]
Saving to: ‘pgpool-II-4.2.2.tar.gz’

100%[====================