PostgreSQL使用Pgpool-II实现读写分离+负载均衡

2021-06-30 1835

作者：小麦苗

一、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中间件

-- 下载镜像
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、拷贝参数文件

[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会自动将你选中的所有行首都加上注释，保存退出完成注释。

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指定的输出路径：

echo 'local0.* /postgresql/pgpool/pgpool.log' >> /etc/rsyslog.conf

systemctl restart rsyslog.service

4.4、配置pool_hba.conf

su - pgsql
echo 'host all all 0.0.0.0/0 md5' >> /postgresql/pgpool/etc/pool_hba.conf

4.5、生成pool_passwd文件

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文件：

[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文件

[pgsql@lhrcentos76 ~]$ pg_md5 -u=pgpool 'lhr'
3996643de967b80174e48fb45d7227b1

echo 'pgpool:3996643de967b80174e48fb45d7227b1' >> /postgresql/pgpool/etc/pcp.conf

4.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

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

结果：

[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

六、查询节点状态

[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后端状态：

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

结果：

[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中的如下参数：

log_statement=all
log_per_node_statement =on
client_min_messages =log
log_min_messages = info

生效：

pgpool reload

测试完成后，修改回原值：

log_statement=off
log_per_node_statement = off
# client_min_messages =notice
# log_min_messages = warning

测试过程：

-- 开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：

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：

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：

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）。

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;

微信关注我们

原文链接：https://my.oschina.net/postgresqlchina/blog/5115828

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

这几个事务案例会回滚吗？最后一个90%的人判断错了...

下面这个问题源于前几日在我们的Spring技术交流群里，一个群友提出的关于事务回滚的疑问。在讨论过程中，我尝试去复现群友提出的问题场景，发现了另外一个可能让大家会迷惑的情况。当时在群里说了结果和原因，但微信群范围有限，所以单独写篇文章，拿出来给大家看看，顺便考考大家，对这块是否了解。问题描述这个问题的基础工程我用了之前Spring Boot 2.x基础教程中《使用Spring Data JPA访问MySQL》的案例。你可以通过下面仓库中的chapter3-4目录获取基础工程： Github：https://github.com/dyc87112/SpringBoot-Learning/ Gitee：https://gitee.com/didispace/SpringBoot-Learning/ 在这个工程中，定义一个名为User的实体： @Entity @Data @NoArgsConstructor public class User { @Id @GeneratedValue private Long id; @Size(max = 5) private String n...

2021-06-30

553

总结十点PostgreSQL/openGauss数据库中容易犯的错误。 1.同时设置日志行前缀和csvlog格式比较常见大家同时配置下面这两个参数 log_line_prefix = '%m %u %d %p' log_destination='csvlog' %m是带毫秒的时间戳 %u是用户名 %d是数据库名 %p是进程ID 然后当我们配置为csvlog日志时，日志行的内容项是固定的，所以当我们需要配置日志前缀，精简日志行的内容项时，log_destination不能配置为csvlog。下面是正确的配置： log_destination='stderr' log_line_prefix = '%m %u %d %p' 2.不符合预期的日志轮换策略日志轮换策略可以通过log_rotation_size参数按日志文件大小控制或者通过log_rotation_age参数按时间控制，但下面这四个参数需要合理组合使用。 log_filename log_truncate_on_rotation log_rotation_age log_rotation_size 方案一：每天生成一个新的日...

2021-06-30

790

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

WebStorm

WebStorm 是jetbrains公司旗下一款JavaScript 开发工具。目前已经被广大中国JS开发者誉为“Web前端开发神器”、“最强大的HTML5编辑器”、“最智能的JavaScript IDE”等。与IntelliJ IDEA同源，继承了IntelliJ IDEA强大的JS部分的功能。