PostgreSQL 10+的libpq版本提供了Seamless Application Failover功能，在连接PG的时候可指定多个目标库并指定target_session_attrs属性（read-write、any、read-only），libpq可根据目标库的状态分发/切换到不同的数据库上。 举个例子，比如现在在26.28:5433和26.26:5432上有两个PG实例，两个实例均可对外提供读写，Failover的配置如下： 1.read-write/any ``` [xdb@localhost ~]$ psql 'postgres://192.168.26.26:5432,192.168.26.28:5433/postgres?target_session_attrs=read-write' -U xdb -c "select inet_server_addr()" Timing is on. Expanded display is used automatically. inet_server_addr ------------------ 192.168.26.26 (1 row) Time: 2.439 ms [xdb@localhost ~]$ ``` 连接到第一个匹配的实例上。关闭26.26上的pg，重新连接，会自动切换到26.28上的pg上。 ``` [xdb@localhost ~]$ pg_ctl stop waiting for server to shut down.... done server stopped [xdb@localhost ~]$ [xdb@localhost ~]$ psql 'postgres://192.168.26.26:5432,192.168.26.28:5433/postgres?target_session_attrs=read-write' -U xdb -c "select inet_server_addr()" Timing is on. Expanded display is used automatically. inet_server_addr ------------------ 192.168.26.28 (1 row) Time: 0.837 ms [xdb@localhost ~]$ ``` 搭建主从流复制环境，一个主库一个备库，分别是26.28和26.25 ``` [xdb@localhost ~]$ psql 'postgres://192.168.26.25:5432,192.168.26.28:5432/postgres?target_session_attrs=read-write' -c "select inet_server_addr()" -U pg12 Password for user pg12: inet_server_addr ------------------ 192.168.26.28 (1 row) [xdb@localhost ~]$ psql 'postgres://192.168.26.25:5432,192.168.26.28:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()" -U pg12 Password for user pg12: inet_server_addr ------------------ 192.168.26.25 (1 row) ``` 使用read-write选项会自动连接到主库上，而使用any选项，则会根据顺序优先连接到从库上。 2.read-only 把连接属性改为其他，如read-only ``` [xdb@localhost ~]$ psql 'postgres://192.168.26.26:5432,192.168.26.28:5433/postgres?target_session_attrs=read-only' -U xdb -c "select inet_server_addr()" psql: invalid target_session_attrs value: "read-only" [xdb@localhost ~]$ ``` 提示无效的属性值，实际上只支持read-write和any两个选项。 >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. **参考资料** [Seamless Application Failover using libpq Features in PostgreSQL](https://www.percona.com/blog/2019/10/23/seamless-application-failover-using-libpq-features-in-postgresql/)