PostgreSQL jdbc multi-host 配置与简单HA、Load Balance实现
背景
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