PostgreSQL libpq|jdbc 驱动层 load balance 与 failover

digoal 2019-10-27

1099

作者

digoal

日期

2019-10-27

背景

自从10版本开始，postgresql libpq驱动层开始支持了简单的failover，只需要简单设置。jdbc驱动则支持了简单的failover和负载均衡，同样只需要简单的设置。

使用jdbc或libpq的客户端，在链接配置中设置就可以使用这部分功能。

《PostgreSQL 10.0 preview 功能增强 - libpq支持多主机连接(failover,LB)让数据库HA和应用配合更紧密》

《PostgreSQL jdbc 支持load balance 和 connection failover了》

《PostgreSQL libpq 多后端连接倾向配置支持 - readonly , perfer-read , perfer-write , write》

《PostgreSQL jdbc multi-host 配置与简单HA、Load Balance实现》

《PostgreSQL libpq multi-host 配置与简单HA实现》

libpq 文档关于failover的部分

It is possible to specify multiple host components, each with an optional port component, in a single URI. A URI of the form postgresql://host1:port1,host2:port2,host3:port3/ is equivalent to a connection string of the form host=host1,host2,host3 port=port1,port2,port3. Each host will be tried in turn until a connection is successfully established.

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 that is specified, 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 host name can 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 host name 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 in the list; it is not possible to e.g. specify different usernames for different hosts.

使用multi-host配置时，只有host,port可以配置多个，其他参数只能配置一个（所有host,port共用），如果多个host有同样的用户配置了不同的密码，需要在密码文件中指出。否则在uri中只能配置一个密码公用会导致其他连接错误。

passfile

Specifies the name of the file used to store passwords (see Section 33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on Microsoft Windows. (No error is reported if this file does not exist.)

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.

connect_timeout

Maximum wait for connection, in seconds (write as a decimal integer, e.g. 10). Zero, negative, or not specified means wait indefinitely. The minimum allowed timeout is 2 seconds, therefore a value of 1 is interpreted as 2. This timeout applies separately to each host name or IP address. For example, if you specify two hosts and connect_timeout is 5, each host will time out if no connection is made within 5 seconds, so the total time spent waiting for a connection might be up to 10 seconds.

jdbc 文档关于failover和loadbalance的部分

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.

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.

```
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=master.

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true
```

psql连接多个host的例子

psql使用libpq驱动链接数据库，支持multi-master uri.下面截取一些例子.

https://www.percona.com/blog/2019/10/23/seamless-application-failover-using-libpq-features-in-postgresql/

环境

192.168.70.10 is the master 192.168.70.20 is the first standby 192.168.70.30 is the second standby

使用inet_server_addr()和inet_server_port()查看链接到的数据库的地址和端口，pg_is_in_recovery()查看是否在恢复中，用来区分是那个库，当前属于什么角色(读写or恢复中)。

```
$psql -h 192.168.70.10
Password for user postgres:
psql (11.5)
Type "help" for help.

postgres=# select inet_server_addr() as "connected_to";
connected_to

192.168.70.10 主库
(1 row)

postgres=# select client_addr, write_lag,flush_lag,replay_lag from pg_stat_replication;
client_addr | write_lag | flush_lag | replay_lag
---------------+-----------------+-----------------+-----------------
192.168.70.20 | 00:00:00.058204 | 00:00:00.058617 | 00:00:00.058619
192.168.70.30 | 00:00:00.03639 | 00:00:00.036689 | 00:00:00.036693
(2 rows)
```

以下设置，连接三个数据库，指定target_session_attrs=read-write需要连接到读写库。

```
$ psql 'postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=read-write' -c "select inet_server_addr()"
Password for user postgres:
inet_server_addr

192.168.70.10
(1 row)
```

指定target_session_attrs=ANY允许链接到任意库，会从所有配置中随机选择一个进行连接，查看inet_server_addr可以看出：

```
$ psql 'postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"
inet_server_addr

192.168.70.20
(1 row)

$ psql 'postgres://192.168.70.10:5432,192.168.70.20:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"
inet_server_addr

192.168.70.10
(1 row)
```

停掉主库，指定target_session_attrs=ANY允许链接到任意库，会继续连接第二个从所有配置中随机选择一个进行连接

```
-- On Server : 192.168.70.10

$ pg_ctl -D $PGDATA stop -mf
waiting for server to shut down.... done
server stopped
[postgres@pg1]$ psql 'postgres://192.168.70.10:5432,192.168.70.20:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"
inet_server_addr

192.168.70.20
(1 row)
```

python连接多个host的例子

```
$ cat pg_conn.py
import psycopg2
conn = psycopg2.connect(database="postgres",host="192.168.70.10,192.168.70.20,192.168.70.30", user="postgres", password="secret", port="5432", target_session_attrs="read-write")
cur = conn.cursor()
cur.execute("select pg_is_in_recovery(), inet_server_addr()")
row = cur.fetchone()
print "recovery =",row[0]
print "server =",row[1]

$ python pg_conn.py
recovery = False
server = 192.168.70.10
```

php连接多个host的例子

```

cat pg_conn.php

<?php
$conn = pg_connect("host=192.168.70.10,192.168.70.20,192.168.70.30 port=5432 dbname=postgres user=postgres password=secret target_session_attrs=read-write") or die("Could not connect");
$status = pg_connection_status($conn);
if ($status === PGSQL_CONNECTION_OK) {
print "Connection status ok\n";
} else {
print "Connection status bad\n";
}
$sql = pg_query($conn, "select pg_is_in_recovery()");
while ($row = pg_fetch_row($sql)) {
echo "Recovery-status: $row[0]\n";
}
?>

$ php -f pg_conn.php
Connection status ok
Recovery-status: f
Server: 192.168.70.10
```

注意驱动

因为multi-host是10开始支持的，所以务必使用10以上的驱动，否则不支持multi-host。

查询到php,python驱动的libpq依赖如下，libpq 11的版本，所以可以支持multi-host。

```

yum info python2-psycopg2-2.8.3-2.rhel7.x86_64 | grep repo

From repo : pgdg11

rpm -q --requires python2-psycopg2-2.8.3-2.rhel7.x86_64 | grep libpq

libpq.so.5()(64bit)

rpm -q --requires php-pgsql-5.4.16-46.el7 | grep libpq