ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(118) - Develop(Seamless Application Failover)

PostgreSQL DBA(118) - Develop(Seamless Application Failover)

原创 PostgreSQL 作者:husthxd 时间:2019-10-31 18:18:01 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2662178/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1442
  • 访问量
    3876163