【DB宝61】PostgreSQL使用Pgpool-II实现读写分离+负载均衡
👉 本文共约2363个字，系统预计阅读时间或需9分钟。
[toc]
一、Pgpool-II 简介
官网：https://www.pgpool.net/mediawiki/index.php/Main_Page
Pgpool-II是一个在PostgreSQL服务器和PostgreSQL数据库客户端之间工作的中间件。它是根据BSD许可证授权的。它提供以下功能。
连接池
Pgpool-II保存与PostgreSQL服务器的连接，并在具有相同属性（即用户名，数据库，协议版本）的新连接进入时重用它们。它减少了连接开销，并提高了系统的整体吞吐量。
复制
Pgpool-II可以管理多个PostgreSQL服务器。使用复制功能可以在2个或更多物理磁盘上创建实时备份，以便在磁盘发生故障时服务可以继续运行而不会停止服务器。
负载均衡
如果复制了数据库，则在任何服务器上执行SELECT查询都将返回相同的结果。Pgpool-II利用复制功能，通过在多个服务器之间分配SELECT查询来减少每个PostgreSQL服务器的负载，从而提高系统的整体吞吐量。充其量，性能与PostgreSQL服务器的数量成比例地提高。在许多用户同时执行许多查询的情况下，负载平衡最有效。
限制超出连接
PostgreSQL的最大并发连接数有限制，连接在这么多连接后被拒绝。但是，设置最大连接数会增加资源消耗并影响系统性能。pgpool-II对最大连接数也有限制，但额外连接将排队，而不是立即返回错误。
看家狗
Watchdog可以协调多个Pgpool-II，创建一个强大的集群系统，避免单点故障或脑裂。看门狗可以对其他pgpool-II节点执行生命检查，以检测Pgpoll-II的故障。如果活动Pgpool-II发生故障，则可以将备用Pgpool-II提升为活动状态，并接管虚拟IP。
在内存查询缓存中
在内存中查询缓存允许保存一对SELECT语句及其结果。如果有相同的SELECT，Pgpool-II将从缓存中返回值。由于不涉及SQL解析或访问PostgreSQL，因此在内存缓存中使用速度非常快。另一方面，在某些情况下，它可能比正常路径慢，因为它增加了存储缓存数据的一些开销。
Pgpool-II讲PostgreSQL的后端和前端协议，并在后端和前端之间传递消息。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用。Pgpool-II讲述PostgreSQL的后端和前端协议，并在它们之间传递连接。因此，数据库应用程序（前端）认为Pgpool-II是实际的PostgreSQL服务器，服务器（后端）将Pgpool-II视为其客户端之一。因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序可以与Pgpool-II一起使用，几乎不需要更改其源码。
二、高可用架构图
PG搭建1主2从流复制的过程可以参考“【DB宝60】PG12高可用之1主2从流复制环境搭建及切换测试”，本文基于该篇的基础上，使用Pgpool-II来实现读写分离和负载均衡功能。
整体架构如下所示：
三、使用Docker快速部署Pgpool-II中间件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- 下载镜像 docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgpool:4.2.2 -- 创建专用网络 docker network create --subnet=172.72.6.0/24 pg-network -- 创建Pgpool-II的容器 docker rm -f lhrpgpool docker run -d --name lhrpgpool -h lhrpgpool \ --net=pg-network --ip 172.72.6.6 \ -p 9999:9999 -p 9898:9898 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrpgpool:4.2.2 \ /usr/sbin/init docker network connect bridge lhrpgpool docker restart lhrpgpool docker exec -it lhrpgpool bash |
四、配置Pgpool-II
以下所有操作均在pgsql用户执行。
4.1、拷贝参数文件
1 2 3 4 5 | [root@lhrpgpool etc]# su - pgsql Last login: Mon Apr 26 14:28:35 CST 2021 on pts/0 [pgsql@lhrpgpool /]# cp /postgresql/pgpool/etc/pgpool.conf.sample /postgresql/pgpool/etc/pgpool.conf [pgsql@lhrpgpool /]# cp /postgresql/pgpool/etc/pool_hba.conf.sample /postgresql/pgpool/etc/pool_hba.conf [pgsql@lhrpgpool /]# cp /postgresql/pgpool/etc/pcp.conf.sample /postgresql/pgpool/etc/pcp.conf |
4.2、配置pgpool.conf
? vi进入列操作：Ctrl + v 进入块选择模式，然后移动光标选中你要注释的行，再按大写的I进入行首插入模式，输入注释符号如 // 或 #，输入完毕之后，按两下ESC，Vim会自动将你选中的所有行首都加上注释，保存退出完成注释。
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | vi /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 = 'lhrpg64302' 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 = 'lhrpg64303' 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 = 'lhrpg64304' # - 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 |
4.3、开启系统日志（root操作）
pgpool的日志默认写到stderr，可以如上文中设置为syslog中。但由于默认情况下写入LOCAL0级别的日志会被丢弃，故要在/etc/rsyslog.conf中修改该syslog指定的输出路径：
1 2 3 | echo "local0.* /postgresql/pgpool/pgpool.log" >> /etc/rsyslog.conf systemctl restart rsyslog.service |
4.4、配置pool_hba.conf
1 2 | su - pgsql echo "host all all 0.0.0.0/0 md5" >> /postgresql/pgpool/etc/pool_hba.conf |
4.5、生成pool_passwd文件
1 2 3 4 | pg_md5 --md5auth --username=nobody "lhr" pg_md5 --md5auth --username=lhr "lhr" pg_md5 --md5auth --username=pgpool "lhr" pg_md5 --md5auth --username=postgres "lhr" |
会生成pool_passwd文件：
1 2 3 4 5 6 7 8 9 10 | [pgsql@lhrpgpool ~]$ pg_md5 --md5auth --username=nobody "lhr" [pgsql@lhrpgpool ~]$ pg_md5 --md5auth --username=lhr "lhr" [pgsql@lhrpgpool ~]$ pg_md5 --md5auth --username=pgpool "lhr" [pgsql@lhrpgpool ~]$ pg_md5 --md5auth --username=postgres "lhr" [pgsql@lhrpgpool ~]$ cd /postgresql/etc [pgsql@lhrpgpool etc]$ cat pool_passwd nobody:md5188b0dfd531e1734c1e5bb4d57053d64 lhr:md53e5c401ee2b9f28db1bb075b1b99e0ad pgpool:md547b6c1f1700de696bcb6b98dc6a21ab6 postgres:md5da3edeb741de62d06ab73785ed222494 |
4.6、配置pcp.conf文件
1 2 3 4 | [pgsql@lhrcentos76 ~]$ pg_md5 -u=pgpool "lhr" 3996643de967b80174e48fb45d7227b1 echo "pgpool:3996643de967b80174e48fb45d7227b1" >> /postgresql/pgpool/etc/pcp.conf |
4.7、在主库创建用户
1 2 3 4 5 6 7 | psql -U postgres -h 192.168.66.35 -p64302 create role nobody login encrypted password 'lhr'; create role lhr login encrypted password 'lhr'; create role pgpool login encrypted password 'lhr'; grant postgres to nobody,lhr,pgpool; |
在 PostgreSQL 里没有区分用户和角色的概念，CREATE USER 为 CREATE ROLE 的别名，这两个命令几乎是完全相同的，唯一的区别是”CREATE USER”命令创建的用户的 LOGIN 属性默认为 on , 而 CREATE ROLE 命令创建的用户的 NOLOGIN 属性默认为 on。
五、启动Pgpool-II
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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 chmod +x /lib/systemd/system/pgpool.service systemctl enable pgpool systemctl start pgpool systemctl status pgpool |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | [root@lhrpgpool etc]# systemctl status pgpool ● pgpool.service - Pgpool-II Loaded: loaded (/usr/lib/systemd/system/pgpool.service; enabled; vendor preset: disabled) Active: active (running) since Mon 2021-04-26 14:35:27 CST; 1s ago Main PID: 1954 (pgpool) CGroup: /docker/c3f0c9600069f719f49e81e3602f642925feecb5e134c8aedb96753728730013/system.slice/pgpool.service ├─1954 /postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf -n ├─1956 pgpool: wait for connection request ├─1957 pgpool: wait for connection request ├─1958 pgpool: wait for connection request ├─1959 pgpool: wait for connection request ├─1960 pgpool: wait for connection request ├─1961 pgpool: wait for connection request ├─1962 pgpool: wait for connection request ├─1963 pgpool: wait for connection request ├─1964 pgpool: wait for connection request ├─1965 pgpool: wait for connection request ├─1966 pgpool: wait for connection request ├─1967 pgpool: wait for connection request ├─1968 pgpool: wait for connection request ├─1969 pgpool: wait for connection request ├─1970 pgpool: wait for connection request ├─1971 pgpool: wait for connection request ├─1972 pgpool: wait for connection request ├─1973 pgpool: wait for connection request ├─1974 pgpool: wait for connection request ├─1975 pgpool: wait for connection request ├─1976 pgpool: wait for connection request ├─1977 pgpool: wait for connection request ├─1978 pgpool: wait for connection request ├─1979 pgpool: wait for connection request ├─1980 pgpool: wait for connection request ├─1981 pgpool: wait for connection request ├─1982 pgpool: wait for connection request ├─1983 pgpool: wait for connection request ├─1984 pgpool: wait for connection request ├─1985 pgpool: wait for connection request ├─1986 pgpool: wait for connection request ├─1987 pgpool: wait for connection request ├─1988 pgpool: PCP: wait for connection request ├─1989 pgpool: worker process ├─1990 pgpool: health check process(0) ├─1991 pgpool: health check process(1) └─1992 pgpool: health check process(2) Apr 26 14:35:28 lhrpgpool pgpool[1954]: [14-1] 2021-04-26 14:35:28: pid 1954: LOG: find_primary_node: standby node is 2 Apr 26 14:35:28 lhrpgpool pgpool[1988]: [15-1] 2021-04-26 14:35:28: pid 1988: LOG: PCP process: 1988 started Apr 26 14:35:28 lhrpgpool pgpool[1990]: [15-1] 2021-04-26 14:35:28: pid 1990: LOG: process started Apr 26 14:35:28 lhrpgpool pgpool[1991]: [15-1] 2021-04-26 14:35:28: pid 1991: LOG: process started Apr 26 14:35:28 lhrpgpool pgpool[1989]: [15-1] 2021-04-26 14:35:28: pid 1989: LOG: process started Apr 26 14:35:28 lhrpgpool pgpool[1992]: [15-1] 2021-04-26 14:35:28: pid 1992: LOG: process started Apr 26 14:35:28 lhrpgpool pgpool[1954]: [15-1] 2021-04-26 14:35:28: pid 1954: LOG: pgpool-II successfully started. version 4.2.2 (chichiriboshi) Apr 26 14:35:28 lhrpgpool pgpool[1954]: [16-1] 2021-04-26 14:35:28: pid 1954: LOG: node status[0]: 1 Apr 26 14:35:28 lhrpgpool pgpool[1954]: [17-1] 2021-04-26 14:35:28: pid 1954: LOG: node status[1]: 2 Apr 26 14:35:28 lhrpgpool pgpool[1954]: [18-1] 2021-04-26 14:35:28: pid 1954: LOG: node status[2]: 2 |
六、查询节点状态
1 2 3 4 5 6 7 8 9 10 11 | [pgsql@lhrpgpool ~]$ psql -U nobody -h localhost -p 9999 -d postgres psql (13.2, server 12.2 (Debian 12.2-2.pgdg100+1)) Type "help" for help. 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-04-26 14:35:27 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 0 | false | 0 | | | 2021-04-26 14:35:27 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 0 | true | 0 | | | 2021-04-26 14:35:27 (3 rows) |
查看pgpool后端状态：
1 2 3 | pcp_node_info -U pgpool -h localhost -p 9898 -n 0 -v pcp_node_info -U pgpool -h localhost -p 9898 -n 1 -v pcp_node_info -U pgpool -h localhost -p 9898 -n 2 -v |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | [pgsql@lhrpgpool ~]$ 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-04-26 14:35:27 [pgsql@lhrpgpool ~]$ 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-04-26 14:35:27 [pgsql@lhrpgpool ~]$ 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-04-26 14:35:27 |
七、测试读写分离+负载均衡
测试之前，可以考虑修改文件pgpool.conf中的如下参数：
1 2 3 4 | log_statement=all log_per_node_statement =on client_min_messages =log log_min_messages = info |
生效：
1 | pgpool reload |
测试完成后，修改回原值：
1 2 3 4 | log_statement=off log_per_node_statement = off # client_min_messages =notice # log_min_messages = warning |
测试过程：
1 2 3 4 5 6 | -- 开3个窗口 psql -U postgres -h 192.168.66.35 -p 9999 -d sbtest create table test(id int); insert into test values(1); select * from test; |
窗口1：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | C:\Users\lhrxxt>psql -U postgres -h 192.168.66.35 -p 9999 -d sbtest Password for user postgres: psql (13.2, server 12.2 (Debian 12.2-2.pgdg100+1)) Type "help" for help. sbtest=# show pool_nodes; LOG: statement: show pool_nodes; LOG: DB node id: 0 backend pid: 10106 statement: SELECT version() 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 0 | true | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 0 | false | 0 | | | 2021-04-26 09:54:44 (3 rows) sbtest=# create table test(id int); LOG: statement: create table test(id int); LOG: DB node id: 0 backend pid: 10106 statement: create table test(id int); CREATE TABLE sbtest=# insert into test values (1); LOG: statement: insert into test values (1); LOG: DB node id: 0 backend pid: 10106 statement: insert into test values (1); INSERT 0 1 sbtest=# select * from test; LOG: statement: select * from test; LOG: DB node id: 0 backend pid: 10106 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pg_catalog.to_regclass('"test"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog' LOG: DB node id: 0 backend pid: 10106 statement: SELECT count(*) FROM pg_catalog.pg_class AS c, pg_namespace AS n WHERE c.relname = 'test' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_' LOG: DB node id: 0 backend pid: 10106 statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pg_catalog.to_regclass('"test"') AND c.relpersistence = 'u' LOG: DB node id: 1 backend pid: 6069 statement: select * from test; id ---- 1 (1 row) sbtest=# show pool_nodes; LOG: statement: 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 1 | true | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 0 | false | 0 | | | 2021-04-26 09:54:44 (3 rows) |
窗口2：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | C:\Users\lhrxxt>psql -U postgres -h 192.168.66.35 -p 9999 -d sbtest Password for user postgres: psql (13.2, server 12.2 (Debian 12.2-2.pgdg100+1)) Type "help" for help. sbtest=# show pool_nodes; LOG: statement: show pool_nodes; LOG: DB node id: 0 backend pid: 10116 statement: SELECT version() 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 1 | false | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 0 | true | 0 | | | 2021-04-26 09:54:44 (3 rows) sbtest=# select * from test; LOG: statement: select * from test; LOG: DB node id: 0 backend pid: 10116 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pg_catalog.to_regclass('"test"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog' LOG: DB node id: 0 backend pid: 10116 statement: SELECT count(*) FROM pg_catalog.pg_class AS c, pg_namespace AS n WHERE c.relname = 'test' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_' LOG: DB node id: 0 backend pid: 10116 statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pg_catalog.to_regclass('"test"') AND c.relpersistence = 'u' LOG: DB node id: 2 backend pid: 6085 statement: select * from test; id ---- 1 (1 row) sbtest=# show pool_nodes; LOG: statement: 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 1 | false | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 1 | true | 0 | | | 2021-04-26 09:54:44 (3 rows) sbtest=# insert into test values(2); LOG: statement: insert into test values(2); LOG: DB node id: 0 backend pid: 10116 statement: insert into test values(2); INSERT 0 1 sbtest=# show pool_nodes; LOG: statement: 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 1 | false | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 1 | true | 0 | | | 2021-04-26 09:54:44 (3 rows) |
窗口3：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | C:\Users\lhrxxt>psql -U postgres -h 192.168.66.35 -p 9999 -d sbtest Password for user postgres: psql (13.2, server 12.2 (Debian 12.2-2.pgdg100+1)) Type "help" for help. sbtest=# show pool_nodes; LOG: statement: show pool_nodes; LOG: DB node id: 0 backend pid: 10127 statement: SELECT version() 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 1 | true | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 1 | false | 0 | | | 2021-04-26 09:54:44 (3 rows) sbtest=# select * from test; LOG: statement: select * from test; LOG: DB node id: 0 backend pid: 10127 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pg_catalog.to_regclass('"test"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog' LOG: DB node id: 0 backend pid: 10127 statement: SELECT count(*) FROM pg_catalog.pg_class AS c, pg_namespace AS n WHERE c.relname = 'test' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_' LOG: DB node id: 0 backend pid: 10127 statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pg_catalog.to_regclass('"test"') AND c.relpersistence = 'u' LOG: DB node id: 1 backend pid: 6084 statement: select * from test; id ---- 1 2 (2 rows) sbtest=# show pool_nodes; LOG: statement: 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 2 | true | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 1 | false | 0 | | | 2021-04-26 09:54:44 (3 rows) sbtest=# insert into test values(3); LOG: statement: insert into test values(3); LOG: DB node id: 0 backend pid: 10127 statement: insert into test values(3); INSERT 0 1 sbtest=# show pool_nodes; LOG: statement: 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-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 2 | true | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 1 | false | 0 | | | 2021-04-26 09:54:44 (3 rows) -- 多做几次其他测试 postgres=# show pool_nodes; LOG: statement: 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 | 6 | false | 0 | | | 2021-04-26 09:54:44 1 | 172.72.6.3 | 5432 | up | 0.333333 | standby | 7 | false | 0 | | | 2021-04-26 09:54:44 2 | 172.72.6.4 | 5432 | up | 0.333333 | standby | 8 | true | 0 | | | 2021-04-26 09:54:44 (3 rows) |
? 可以看到，读操作被均衡的分配到0、1和2这3个节点上，而写操作只分配到0节点上。
另外，在告警日志里也会记录相关的信息（/postgresql/pgpool/pgpool.log）。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Apr 26 10:01:06 lhrpgpool pgpool[32486]: [20-1] 2021-04-26 10:01:06: pid 32486: LOG: statement: show pool_nodes; Apr 26 10:01:23 lhrpgpool pgpool[32486]: [21-1] 2021-04-26 10:01:23: pid 32486: LOG: statement: insert into test values(3); Apr 26 10:01:23 lhrpgpool pgpool[32486]: [22-1] 2021-04-26 10:01:23: pid 32486: LOG: DB node id: 0 backend pid: 10127 statement: insert into test values(3); Apr 26 10:01:26 lhrpgpool pgpool[32486]: [23-1] 2021-04-26 10:01:26: pid 32486: LOG: statement: show pool_nodes; Apr 26 10:02:02 lhrpgpool pgpool[32486]: [24-1] 2021-04-26 10:02:02: pid 32486: LOG: statement: select * from test; Apr 26 10:02:02 lhrpgpool pgpool[32486]: [25-1] 2021-04-26 10:02:02: pid 32486: LOG: DB node id: 1 backend pid: 6084 statement: select * from test; Apr 26 10:02:04 lhrpgpool pgpool[32485]: [25-1] 2021-04-26 10:02:04: pid 32485: LOG: statement: select * from test; Apr 26 10:02:04 lhrpgpool pgpool[32485]: [26-1] 2021-04-26 10:02:04: pid 32485: LOG: DB node id: 1 backend pid: 6069 statement: select * from test; Apr 26 10:02:06 lhrpgpool pgpool[32485]: [27-1] 2021-04-26 10:02:06: pid 32485: LOG: statement: select * from test; Apr 26 10:02:06 lhrpgpool pgpool[32485]: [28-1] 2021-04-26 10:02:06: pid 32485: LOG: DB node id: 1 backend pid: 6069 statement: select * from test; Apr 26 10:02:09 lhrpgpool pgpool[32476]: [24-1] 2021-04-26 10:02:09: pid 32476: LOG: statement: select * from test; Apr 26 10:02:09 lhrpgpool pgpool[32476]: [25-1] 2021-04-26 10:02:09: pid 32476: LOG: DB node id: 2 backend pid: 6085 statement: select * from test; Apr 26 10:02:11 lhrpgpool pgpool[32486]: [26-1] 2021-04-26 10:02:11: pid 32486: LOG: statement: select * from test; Apr 26 10:02:11 lhrpgpool pgpool[32486]: [27-1] 2021-04-26 10:02:11: pid 32486: LOG: DB node id: 1 backend pid: 6084 statement: select * from test; Apr 26 10:02:13 lhrpgpool pgpool[32486]: [28-1] 2021-04-26 10:02:13: pid 32486: LOG: statement: select * from test; Apr 26 10:02:13 lhrpgpool pgpool[32486]: [29-1] 2021-04-26 10:02:13: pid 32486: LOG: DB node id: 1 backend pid: 6084 statement: select * from test; |
写的真详细，测试负载均衡可以使用: