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
[[email protected] ~]# 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
[[email protected] ~]# docker network connect bridge wcbpgpool
[[email protected] ~]# docker restart wcbpgpool
wcbpgpool
[[email protected] ~]# docker exec -it wcbpgpool bash
[[email protected] /]#
#建立使用者
[[email protected] /]# groupadd -g 60000 pgsql
[[email protected] /]# useradd -u 60000 -g pgsql pgsql
[[email protected] /]# echo "wcb" |passwd --stdin pgsql
Changing password for user pgsql.
passwd: all authentication tokens updated successfully.
#建立響應目錄
[[email protected] /]# mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg13,soft}
[[email protected] /]# chown -R pgsql:pgsql /postgresql
[[email protected] /]# chmod -R 775 /postgresql
#編譯pg
[[email protected] /]# su - pgsql
[[email protected] ~]$ cd /postgresql/soft
#在主控端把安裝包拷貝到容器内 docker cp postgresql-13.2.tar.gz wcbpgpool:/postgresql/soft
[[email protected] ~]$ tar zxvf postgresql-13.2.tar.gz
[[email protected] soft]$ cd postgresql-13.2
[[email protected] postgresql-13.2]$ ./configure --prefix=/postgresql/pg13 --without-readline
[[email protected] postgresql-13.2]$ make -j 8 && make install
#配置環境變量
[[email protected] 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
[[email protected] postgresql-13.2]$ source ~/.bash_profile
#安裝pgpool
[[email protected] /]# su - pgsql
Last login: Sun Dec 26 18:06:34 CST 2021 on pts/0
[[email protected] ~]$ cd /postgresql/soft
[[email protected] 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%[==================================================================================================================================>] 4,734,840 1.10MB/s in 4.1s
2021-12-26 18:07:01 (1.10 MB/s) - ‘pgpool-II-4.2.2.tar.gz’ saved [4734840/4734840]
[[email protected] soft]$ tar -zxvf pgpool-II-4.2.2.tar.gz
[[email protected] soft]$ cd pgpool-II-4.2.2/
[[email protected] pgpool-II-4.2.2]$ ./configure --prefix=/postgresql/pgpool --with-pgsql=/postgresql/pg13
[[email protected] pgpool-II-4.2.2]$make -j 8 && make install
[[email protected] pgpool-II-4.2.2]$ echo 'export PATH=/postgresql/pgpool/bin:$PATH' >> /home/pgsql/.bash_profile
[[email protected] pgpool-II-4.2.2]$
[[email protected] pgpool-II-4.2.2]$ source /home/pgsql/.bash_profile
配置pgpool
[[email protected] pgpool-II-4.2.2]$ cp /postgresql/pgpool/etc/pgpool.conf.sample /postgresql/pgpool/etc/pgpool.conf
[[email protected] pgpool-II-4.2.2]$
[[email protected] pgpool-II-4.2.2]$ cp /postgresql/pgpool/etc/pool_hba.conf.sample /postgresql/pgpool/etc/pool_hba.conf
[[email protected] pgpool-II-4.2.2]$
[[email protected] pgpool-II-4.2.2]$ cp /postgresql/pgpool/etc/pcp.conf.sample /postgresql/pgpool/etc/pcp.conf
修改/postgresql/pgpool/etc/pgpool.conf檔案
# - pgpool Connection Settings -
listen_addresses = '*'
# - Backend Connection Settings -
backend_hostname0 = '172.72.6.2'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'wcbpg64302'
backend_hostname1 = '172.72.6.3'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'wcbpg64303'
backend_hostname2 = '172.72.6.4'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'wcbpg64304'
# - Authentication -
enable_pool_hba = on
# - Where to log -
log_destination = 'syslog'
log_connections = on
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/postgresql/pgpool/pgpool.pid'
logdir = '/tmp'
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
切換到root
[[email protected] /]# echo "local0.* /postgresql/pgpool/pgpool.log" >> /etc/rsyslog.conf
[[email protected] /]#
[[email protected] /]# systemctl restart rsyslog.service
生成pool_passwd檔案
[[email protected] /]# su - pgsql
Last login: Sun Dec 26 18:15:04 CST 2021 on pts/0
[[email protected] ~]$
[[email protected] ~]$ echo "host all all 0.0.0.0/0 md5" >> /postgresql/pgpool/etc/pool_hba.conf
[[email protected] ~]$ pg_md5 --md5auth --username=nobody "wcb"
[[email protected] ~]$ pg_md5 --md5auth --username=wcb "wcb"
[[email protected] ~]$ pg_md5 --md5auth --username=pgpool "wcb"
[[email protected] ~]$ pg_md5 --md5auth --username=postgres "wcb"
[[email protected] ~]$ cat /postgresql/pgpool/etc/pool_passwd
nobody:md518ea24b40e409ed8e764ea63e714a112
wcb:md5b9ec3da5ecef5b68422584ed892b1f2e
pgpool:md582d49bafd6a23f29bc7c8715db72d806
postgres:md5ca091250a3682d4d389c20364f55716f
配置pcp.conf檔案
[[email protected] ~]$ pg_md5 -u=pgpool "wcb"
ea8ad41791daf14ab7ad63416b9383d2
[[email protected] ~]$
[[email protected] ~]$ echo "pgpool:ea8ad41791daf14ab7ad63416b9383d2" >> /postgresql/pgpool/etc/pcp.conf
在主庫建立使用者
postgres=# create role nobody login encrypted password 'wcb';
CREATE ROLE
postgres=# create role lhr login encrypted password 'wcb';
CREATE ROLE
postgres=# create role pgpool login encrypted password 'wcb';
CREATE ROLE
postgres=# grant postgres to nobody,lhr,pgpool;
GRANT ROLE
啟動pgpool
[[email protected] /]# cat >> /lib/systemd/system/pgpool.service <<"EOF"
[Unit]
Description=Pgpool-II
After=syslog.target network.target
[Service]
User=pgsql
Group=pgsql
EnvironmentFile=-/etc/sysconfig/pgpool
ExecStart=/postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf -n
ExecStop=/postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf -m fast stop
ExecReload=/postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf reload
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0
[Install]
WantedBy=multi-user.target
EOF
[[email protected] /]# chmod +x /lib/systemd/system/pgpool.service
[[email protected] /]# systemctl status pgpool
[[email protected] /]# systemctl start pgpool
[[email protected] /]# systemctl enable pgpool
[[email protected] /]# su - pgsql
Last login: Sun Dec 26 19:18:03 CST 2021 on pts/0
[[email protected] ~]$ psql -U nobody -h localhost -p 9999 -d postgres
psql (13.2, server 12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=> postgres=> show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_
change
---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+-------------
--------
0 | 172.72.6.2 | 5432 | up | 0.333333 | primary | 0 | false | 0 | | | 2021-12-26 1
9:20:54
1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 0 | false | 0 | | | 2021-12-26 1
9:20:54
2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 0 | true | 0 | | | 2021-12-26 1
9:20:54
(3 rows)
檢視pgpool後端狀态
[[email protected] ~]$ pcp_node_info -U pgpool -h localhost -p 9898 -n 0 -v
Password:
Hostname : 172.72.6.2
Port : 5432
Status : 2
Weight : 0.333333
Status Name : up
Role : primary
Replication Delay : 0
Replication State :
Replication Sync State :
Last Status Change : 2021-12-26 19:20:54
[[email protected] ~]$ pcp_node_info -U pgpool -h localhost -p 9898 -n 1 -v
Password:
Hostname : 172.72.6.3
Port : 5432
Status : 2
Weight : 0.333333
Status Name : up
Role : standby
Replication Delay : 0
Replication State :
Replication Sync State :
Last Status Change : 2021-12-26 19:20:54
[[email protected] ~]$ pcp_node_info -U pgpool -h localhost -p 9898 -n 2 -v
Password:
Hostname : 172.72.6.4
Port : 5432
Status : 2
Weight : 0.333333
Status Name : up
Role : standby
Replication Delay : 0
Replication State :
Replication Sync State :
Last Status Change : 2021-12-26 19:20:54
進行測試
[[email protected] ~]$ psql -U postgres -h 192.168.142.110 -p 9999 -d sbtest
Password for user postgres:
psql (13.2, server 12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
sbtest=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_
change
---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+-------------
--------
0 | 172.72.6.2 | 5432 | up | 0.333333 | primary | 0 | false | 0 | | | 2021-12-26 1
9:20:54
1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 0 | false | 0 | | | 2021-12-26 1
9:20:54
2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 0 | true | 0 | | | 2021-12-26 1
9:20:54
(3 rows)
sbtest=# select * from test;
id
----
1
(1 row)
sbtest=# select * from test;
id
----
1
(1 row)
sbtest=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_
change
---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+-------------
--------
0 | 172.72.6.2 | 5432 | up | 0.333333 | primary | 0 | false | 0 | | | 2021-12-26 1
9:20:54
1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 0 | false | 0 | | | 2021-12-26 1
9:20:54
2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 2 | true | 0 | | | 2021-12-26 1
9:20:54
(3 rows)
![Mysql表結構轉Postgresql建表語句快速操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)