PostgreSQL运行时角色连接及角色查询问题

本文讨论两个问题，在HA场景如何连接指定角色的节点以及如何识别连接的主库。

如何只连接主库

我们可能会有疑问：直接使用主库的IP地址进行连接会有什么问题呢？但如果我们没有使用统一的公有IP呢，PG里一些HA方案没有提供公有IP方案，不过我们也可以使用多IP+PORT的方式来进行连接，这种方案架构也更轻便。

目前可以通过以下两种协议来访问主库或者指定角色的节点：

• jdbc协议
• libpq协议

jdbc协议的targetServerType连接参数

关于targetServerType连接参数

targetServerType指定Connection连接特定状态的数据库实例，可选状态值包括any, primary, master, slave, secondary, preferSlave and preferSecondary

详细测试请参考文章：<<PostgreSQL数据库高可用及负载均衡JDBC参数测试>>

libpq协议的target_session_attrs属性

通过java语言使用jdbc的targetServerType进行连接的方式比较熟知，毕竟使用java语言开发应用程序更为广泛。

基于C语言编程的odbc接口或者基于Python语言的psycopg2接口可以使用libpq协议像jdbc一样通过连接串指定多个host+port，建立连接时可以轮流尝试直至成功。

最早在PostgreSQL 13里，target_session_attrs属性引入了两个值：

• any(默认值) 表示可以允许连接到任意数据库，它会从所有配置的连接中轮流尝试，直至连接建立成功，从而实现故障转移。
• read-write 在连接的时候，只接受可以读写的数据库。当它建立连接后，会发送SHOW transaction_read_only，如果是on，就代表是只读库，它会把连接关闭，然后测试第二个数据库，以此类推，直至连接到支持读写的数据库为止。

我们可以看出target_session_attrs属性参照了PostgreSQL JDBC连接参数targetServerType的功能，并且在PostgreSQL 14里，这两个参数的选项值保持了对齐。

• any(默认值) 可以允许连接到任意数据库。
• read-write 只接收连接到default_transaction_read_only=off，并且非standby模式的数据库。
• read-only 与read-write相反。
• primary 只接收连接非standby模式的数据库。
• standby 只接收连接standby模式的数据库。
• prefer-standby 首先尝试连接standby模式的数据库，如果失败则尝试any模式。

target_session_attrs测试

测试连接本地的单机版本13(端口1306)和版本14(端口1402)，我们要连接只读实例

[postgres@pg ~]$ psql "host=localhost,localhost port=1306,1402 dbname=postgres target_session_attrs=read-only"
psql: error: connection to server at "localhost" (127.0.0.1), port 1306 failed: session is not read-only
connection to server at "localhost" (127.0.0.1), port 1402 failed: session is not read-only

可以看到提示，没能连接成功，因为默认default_transaction_read_only是off。

下面我们修改版本13(端口1306)的default_transaction_read_only，设置为on，重启服务再测试连接只读实例

[postgres@pg ~]$ psql "host=localhost,localhost port=1306,1402 dbname=postgres target_session_attrs=read-only"
psql (14.2, server 13.6)
Type "help" for help.

postgres=# show port;
 port 
------
 1306
(1 row)

可以看到连接到了版本13(端口1306)

再测试连接standby节点

[postgres@pg ~]$ psql "host=localhost,localhost port=1306,1402 dbname=postgres target_session_attrs=standby"
psql: error: connection to server at "localhost" (127.0.0.1), port 1306 failed: server is not in hot standby mode
connection to server at "localhost" (127.0.0.1), port 1402 failed: server is not in hot standby mode

两个实例都是单机，非standby模式，不能连接。

接着连接主库(非standby模式)

[postgres@pg ~]$ psql "host=localhost,localhost port=1306,1402 dbname=postgres target_session_attrs=primary"
psql (14.2, server 13.6)
Type "help" for help.

postgres=# 

[postgres@pg ~]$ psql "host=localhost,localhost port=1402,1306 dbname=postgres target_session_attrs=primary"
psql (14.2)
Type "help" for help.

postgres=# 

可以看到依次连接到版本13及版本14

首选standby测试

[postgres@pg ~]$ psql "host=localhost,localhost port=1306,1402 dbname=postgres target_session_attrs=prefer-standby"
psql (14.2, server 13.6)
Type "help" for help.

postgres=# 

由于没有standby节点，安装默认any模式进行连接

target_session_attrs测试总结

目前target_session_attrs参数可以按照我们期望的角色进行连接，也方便我们动态扩展多个节点。比如后台的报表汇总统计为了减轻主库压力，可以只连接standby节点。

不过还有一个问题，并不是所有的图形化界面工具都支持多节点配置targetServerType或者target_session_attrs参数来连接指定角色节点，一些支持配置jdbc url参数的工具可以做到这一点。

如何查询连接的实际主库

如果使用了公有IP方案，有时客户端想在SQL层查询当前服务节点，我们想了解实际提供服务的私有IP，此时通过inet_server_addr可能会起作用(IP通过中间层代理)。

如果公有IP是实际绑定在数据库服务器，那inet_server_addr并不能查到，测试如下：
绑定一个共有IP：192.168.137.150

$ sudo ip addr add 192.168.137.150/24 dev enp0s3

数据库私有IP：192.168.137.251

[postgres@pg ~]$ ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 08:00:27:76:8f:09 brd ff:ff:ff:ff:ff:ff
    inet 192.168.137.251/24 brd 192.168.137.255 scope global enp0s3
       valid_lft forever preferred_lft forever
    inet 192.168.137.150/24 scope global secondary enp0s3
       valid_lft forever preferred_lft forever

下面通过共有IP 192.168.137.150进行连接

[postgres@pg ~]$ psql -h 192.168.137.150 -W
Password: 
psql (14.2)
Type "help" for help.

postgres=# select inet_server_addr(),inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
 192.168.137.150   |             1402
(1 row)

因为该连接是通过共有IP实际建立的，所以连接的服务IP地址也就是192.168.137.150。

还有一种识别节点的方式，通过服务端配置参数cluster_name，比如postgresql.conf文件设置

cluster_name='node1:192.168.137.251'

然后可以通过show命令或者current_setting函数来查询

postgres=# select current_setting('cluster_name');
    current_setting    
-----------------------
 node1:192.168.137.251
(1 row)

注意：最好把有差异的配置参数(cluster_name)以include方式进行配置，避免HA恢复或者重建之后覆盖了参数值。

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴进群吹牛唠嗑，交流技术，互赞文章。