作者
digoal
日期
2018-06-14
标签
PostgreSQL , HA , jdbc , multi host , target_session_attrs
背景
pg jdbc 与libpq一样，都是PG的连接驱动，都支持multi-master ，同时pg jdbc还支持了loadbalance。
《PostgreSQL libpq multi-host 配置与简单HA实现》
例子
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.
Connection Fail-over
To support simple connection fail-over it is possible to define multiple endpoints (host and port pairs)
in the connection url separated by commas.
The driver will try to once connect to each of them in order until the connection succeeds.
If none succeed, a normal connection exception is thrown.
The syntax for the connection url is:
jdbc:postgresql://host1:port1,host2:port2/database
The simple connection fail-over is useful when running against a high availability
postgres installation that has identical data on each node.
For example streaming replication postgres or postgres-xc cluster.
For example an application can create two connection pools.
One data source is for writes, another for reads.
The write pool limits connections only to master node:
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=master.
And read pool balances connections between slaves nodes,
but allows connections also to master if no slaves are available:
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true
If a slave fails, all slaves in the list will be tried first.
If the case that there are no available slaves the master will be tried.
If all of the servers are marked as "can't connect" in the cache
then an attempt will be made to connect to all of the hosts in the URL in order.
小结
优点：HA简化了网络结构，因为不再需要管VIP漂移的事情。
缺点：因为CLIENT直接连物理主机IP，如果物理主机搬迁IP变化，需要调整libpq配置。
参考
https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
《PostgreSQL 一主多从(多副本,强同步)简明手册 - 配置、压测、监控、切换、防脑裂、修复、0丢失 - 珍藏级》
《PG多节点(quorum based), 0丢失 HA(failover,switchover)方案》
https://github.com/digoal/PostgreSQL_HA_with_primary_standby_2vip
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
