之前由于某个项目需要lb的功能，当时PG还没有提供该功能，但是金仓的libpq提供了相应的功能，所以把产品中的libpq用金仓的替换掉实现的， 知道今天原生PostgreSQL libpq从16也支持前端lb功能，使用load_balance_hosts参数进行控制
另外加上target_session_attrs 参数进行控制
连个参数的帮助信息如下
target_session_attrs
This option determines whether the session must have certain properties to be acceptable. It's typically used in combination with multiple host names to select the first acceptable alternative among several hosts. There are six modes:
any (default)
any successful connection is acceptable
read-write
session must accept read-write transactions by default (that is, the server must not be in hot standby mode and the default_transaction_read_only parameter must be off)
read-only
session must not accept read-write transactions by default (the converse)
primary
server must not be in hot standby mode
standby
server must be in hot standby mode
prefer-standby
first try to find a standby server, but if none of the listed hosts is a standby server, try again in any mode
load_balance_hosts
Controls the order in which the client tries to connect to the available hosts and addresses. Once a connection attempt is successful no other hosts and addresses will be tried. This parameter is typically used in combination with multiple host names or a DNS record that returns multiple IPs. This parameter can be used in combination with target_session_attrs to, for example, load balance over standby servers only. Once successfully connected, subsequent queries on the returned connection will all be sent to the same server. There are currently two modes:
disable (default)
No load balancing across hosts is performed. Hosts are tried in the order in which they are provided and addresses are tried in the order they are received from DNS or a hosts file.
random
Hosts and addresses are tried in random order. This value is mostly useful when opening multiple connections at the same time, possibly from different machines. This way connections can be load balanced across multiple PostgreSQL servers.
While random load balancing, due to its random nature, will almost never result in a completely uniform distribution, it statistically gets quite close. One important aspect here is that this algorithm uses two levels of random choices: First the hosts will be resolved in random order. Then secondly, before resolving the next host, all resolved addresses for the current host will be tried in random order. This behaviour can skew the amount of connections each node gets greatly in certain cases, for instance when some hosts resolve to more addresses than others. But such a skew can also be used on purpose, e.g. to increase the number of connections a larger server gets by providing its hostname multiple times in the host string.
When using this value it's recommended to also configure a reasonable value for connect_timeout. Because then, if one of the nodes that are used for load balancing is not responding, a new node will be tried.今天结合pg_service进行测试，测试结果如下：
注释掉load_balance_hosts，默认为disable
//pg_service.conf内容，5432实例是主机,5433,5434两个实例是备机
[mydb1]
host=geoscene.yzr.local,geoscene.yzr.local,geoscene.yzr.local
port=5432,5433,5434
#load_balance_hosts=random
target_session_attrs=standby
结果结果如下
set PGSERVICEFILE=d:\pg_service.conf
由于load_balance_hosts不起作用，只有target_session_attrs起作用，所以永远只链接到第一台通的备机上
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# show port;
port
------
5433
(1 行记录)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 行记录)
postgres=# \q
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 行记录)
postgres=# show port;
port
------
5433
(1 行记录)
关闭第一台备机，只保留5434的备机
[postgres174@geoscene ~]$ pg_ctl stop -mf -D pgdata_standby1
waiting for server to shut down.... done
server stopped
//由于要测试链接5433端口，所以会慢一些
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# show port;
port
------
5434
(1 行记录)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 行记录)
postgres=# \q
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 行记录)
postgres=# show port;
port
------
5434
(1 行记录)测试2
[mydb1]
host=geoscene.yzr.local,geoscene.yzr.local,geoscene.yzr.local
port=5432,5433,5434
load_balance_hosts=random
target_session_attrs=standby
测试结果：由于有两个备机，所以两个备机随机被选择链接
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# show port;
port
------
5433
(1 行记录)
postgres=# \q
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# show port;
port
------
5434
(1 行记录)测试3：
[mydb1]
host=geoscene.yzr.local,geoscene.yzr.local,geoscene.yzr.local
port=5432,5433,5434
load_balance_hosts=random
target_session_attrs=primary
测试结果：由于就一台primary，所以永远链接到5432上面
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# show port;
port
------
5432
(1 行记录)
postgres=# \q
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# show port;
port
------
5432
(1 行记录)
postgres=# \q
C:\Program Files\PostgreSQL\16\bin>psql "service=mydb1 user=postgres password=******"
psql (16.8, 服务器 17.4)
警告：psql 主版本16,服务器主版本为17.
一些psql功能可能无法正常使用.
输入 "help" 来获取帮助信息.
postgres=# show port;
port
------
5432
(1 行记录)
postgres=#