标签
PostgreSQL , HA , libpq , multi host , target_session_attrs
背景
PostgreSQL libpq 是数据库的一个连接驱动，支持多主机配置，同时支持target_session_attrs 主机角色判断配置。
当配置了多个主机时，会按顺序尝试连接，之道获取到成功的连接为止。
什么是成功的连接，满足以下两点：
1、能与之成功建立连接。
2、如果target_session_attrs配置为read-write，那么SHOW transaction_read_only 如果返回ON，则也是不成功的连接。
target_session_attrs配置为read-write的意思是，只连接到可以读写的节点，比如standby就是只读的，所以不能算成功的连接。
利用libpq的这个特性，结合数据库自动HA的一些软件，可以实现在不引入VIP以及中间路由节点的情况下实现数据库应用系统层级的高可用。
例如，有流复制组成的两个节点，或者由共享存储组成的两个节点。
在libpq中配置两个节点的HOST:PORT，当数据库某个主机发生异常切换到另一个主机，不需要漂移VIP，libpq可以帮你实现主节点的自动选择。（当然，需要配合自动断开重连的机制来实现）
也可以与三节点（多副本同步复制）结合来使用。
例子
格式
postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]
1、多HOST配置例子
postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp
说明
https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS
33.1.1.3. Specifying Multiple Hosts
It is possible to specify multiple hosts to connect to, so that they are tried in the given order.
In the Keyword/Value format, the host, hostaddr, and port options accept a comma-separated list of values.
The same number of elements must be given in each option, such that e.g.
the first hostaddr corresponds to the first host name,
the second hostaddr corresponds to the second host name, and so forth.
As an exception, if only one port is specified, it applies to all the hosts.
In the connection URI format, you can list multiple host:port pairs separated by commas,
in the host component of the URI. In either format, a single hostname can also translate
to multiple network addresses. A common example of this is a host that has both an IPv4 and an IPv6 address.
When multiple hosts are specified, or when a single hostname is translated to multiple addresses,
all the hosts and addresses will be tried in order, until one succeeds.
If none of the hosts can be reached, the connection fails. If a connection is established successfully,
but authentication fails, the remaining hosts in the list are not tried.
If a password file is used, you can have different passwords for different hosts.
All the other connection options are the same for every host, it is not possible to e.g.
specify a different username for different hosts.
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.
如何判断是否为备节点。
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
备节点transaction_read_only参数为ON
postgres=# show transaction_read_only ;
transaction_read_only
-----------------------
on
(1 row)
小结
优点：HA简化了网络结构，因为不再需要管VIP漂移的事情。
缺点：因为CLIENT直接连物理主机IP，如果物理主机搬迁IP变化，需要调整libpq配置。
参考
https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS
《PostgreSQL 一主多从(多副本,强同步)简明手册 - 配置、压测、监控、切换、防脑裂、修复、0丢失 - 珍藏级》
《PG多节点(quorum based), 0丢失 HA(failover,switchover)方案》
https://github.com/digoal/PostgreSQL_HA_with_primary_standby_2vip
