作者
digoal
日期
2019-09-01
标签
PostgreSQL , libpq , 连接倾向 , readonly , perfer-read , perfer-write , write , jdbc
背景
PostgreSQL 客户端驱动 (c, ecpg, perl, java, c++, javascript, .net, tcl, go, odbc, python等) , 某些驱动支持配置多个后端。
https://www.postgresql.org/docs/devel/external-interfaces.html
为什么要配置多个后端呢？
例如后端是multi-master架构，master-slave架构。
客户端请求连接时，应该选择哪个数据库节点？
当某些节点连接响应超时时，应该如何处理，是继续连接其他后端还是报错？
当业务需要读写分离时，如何配置？
当业务希望优先连接某些节点时，如何配置？
jdbc和libpq都支持多后端配置，jdbc的支持更丰富一点。
https://jdbc.postgresql.org/documentation/head/connect.html
pgjdbc uses three parameters (since 2014):
1) targetServerType=(any | master | secondary | preferSecondary). Default
is "any". When set to "master" it will look for "read-write" server. If set
to "preferSecondary" it would search for "read-only" server first, then
fall back to master, and so on.
2) loadBalanceHosts=(true | false). pgjdbc enables to load-balance across
servers provided in the connection URL. When set to "false", pgjdbc tries
connections in order, otherwise it shuffles the connections.
3) hostRecheckSeconds=int. pgjdbc caches "read/write" status of a host:port
combination, so it don't re-check the status if multiple connections are
created within hostRecheckSeconds timeframe.
- targetServerType = String
Allows opening connections to only servers with required state, the allowed values are any, master, slave, secondary, preferSlave and preferSecondary. The master/slave distinction is currently done by observing if the server allows writes. The value preferSecondary tries to connect to secondary if any are available, otherwise allows falls back to connecting also to master.
- hostRecheckSeconds = int
Controls how long in seconds the knowledge about a host state is cached in JVM wide global cache. The default value is 10 seconds.
- loadBalanceHosts = boolean
In default mode (disabled) hosts are connected in the given order. If enabled hosts are chosen randomly from the set of suitable candidates.
libpq也支持多后端配置，但是在后端选择方面，仅支持any和read-write模式。
当target_session_attrs配置位any时，任意可用后端都可以使用。
当target_session_attrs配置为read-write时，优先使用master节点(transaction_read_only=false的节点)
https://www.postgresql.org/docs/devel/libpq-connect.html
target_session_attrs
If this parameter is set to read-write, only a connection in which read-write transactions are accepted by default is considered acceptable. The query SHOW transaction_read_only will be sent upon any successful connection; if it returns on, the connection will be closed. If multiple hosts were specified in the connection string, any remaining servers will be tried just as if the connection attempt had failed. The default value of this parameter, any, regards all connections as acceptable.
配置举例
postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp
transaction_read_only是一个会话状态参数，是否允许发送写请求sql。
standby返回：
```
postgres=# show default_transaction_read_only ;
default_transaction_read_only
off
(1 row)
postgres=# show transaction_read_only ;
transaction_read_only
on
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
t
(1 row)
```
primary,master 返回
```
postgres=# show default_transaction_read_only ;
default_transaction_read_only
off
(1 row)
postgres=# show transaction_read_only ;
transaction_read_only
off
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
f
(1 row)
```
libpq read-only或prefer-read, prefer-write patch
libpq未支持优先选择readonly节点的配置，这个patch 可以支持配置read-only或prefer-read, prefer-write
read-only：只允许选择只读后端。
prefer-read：优先选择只读后端。
prefer-write：优先选择可写后端。
https://www.postgresql.org/message-id/flat/CAF3+xM+8-ztOkaV9gHiJ3wfgENTq97QcjXQt+rbFQ6F7oNzt9A@mail.gmail.com
```
The patch applies, builds and passes "make check-world".
I think the "prefer-read" functionality is desirable: It is exactly what you need
if you want to use replication for load balancing, and your application supports
different database connections for reading and writing queries.
"read-only" does not have a clear use case in my opinion.
With the patch, PostgreSQL behaves as expected if I have a primary and a standby and run:
psql "host=/tmp,/tmp port=5433,5434 target_session_attrs=prefer-read"
But if I stop the standby (port 5434), libpq goes into an endless loop.
Concerning the code:
- The documentation needs some attention. Suggestion:
If this parameter is set to
where
If no such connection can be found, a connection that allows read-write
transactions will be accepted.
- I think the construction with "read_write_host_index" makes the code even more
complicated than it already is.
What about keeping the first successful connection open and storing it in a
variable if we are in "prefer-read" mode.
If we get the read-only connection we desire, close that cached connection,
otherwise use it.
Yours,
Laurenz Albe
```
参考
https://www.postgresql.org/docs/devel/libpq-connect.html
https://jdbc.postgresql.org/documentation/head/connect.html
https://www.postgresql.org/message-id/flat/CAF3+xM+8-ztOkaV9gHiJ3wfgENTq97QcjXQt+rbFQ6F7oNzt9A@mail.gmail.com
https://www.postgresql.org/docs/devel/external-interfaces.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
