閱讀391 返回首頁    go 技術社區[雲棲]

PostgreSQL 10.0 preview 功能增強 - libpq支持多主機連接(failover,LB)讓數據庫HA和應用配合更緊密

標簽

PostgreSQL , 10.0 , libpq , jdbc , failover , loadbalance , multi host , target_session_attrs

背景

數據庫一主多備，這個詞在互聯網應該不陌生。但是主備切換和應用程序如何配合才能天衣無縫呢？你可能會有這樣的疑問。

1. 什麼類型的QUERY發給主庫，什麼類型的QUERY發給備庫？

2. 主庫和備庫發生了角色切換之後，客戶端連接如何配合？

業界有一些做法可以回答這兩個問題。

1. 通常使用集群軟件，使用VIP來表示主備庫的角色，不管怎麼切換，VIP1永遠都是主庫，VIP2永遠都是備庫。

對於客戶端來說，通過VIP來判斷是主庫還是備庫，程序自己控製哪些SQL發給主庫，哪些SQL發給備庫。

一個典型的例子

https://github.com/digoal/PostgreSQL_HA_with_primary_standby_2vip

2. 使用VIP是讓數據庫和應用程序形成默契的一種方法，還有沒有更好的方法呢？比如數據庫驅動層能不能配合角色的判斷？

Oracle做得不錯，Oracle的客戶端，可以配合RAC，自動識別failover, switchover。

PostgreSQL jdbc驅動，也實現了類似的功能，支持failover, loadbalance。

https://jdbc.postgresql.org/documentation/94/connect.html

PostgreSQL jdbc相關參數如下

1.

targetServerType = {any, master, slave, preferSlave}  
  
Allows opening connections to only servers with required state, the allowed values are any, master, slave and preferSlave.   
The master/slave distinction is currently done by observing if the server allows writes.   
The value preferSlave tries to connect to slaves if any are available, otherwise allows falls back to connecting also to master.  
  
any表示連接到任意節點  
preferSlave表示優先連接到slave節點  
master或slave表示連接到master或slave節點。

2.

hostRecheckSeconds = int  
  
Controls how long in seconds the knowledge about a host state is cached in JVM wide global cache.   
The default value is 10 seconds.  
  
重新檢測節點狀態的時間間隔

3.

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.  
  
是否隨機選擇節點，負載均衡

4.

Connection Fail-over  
  
To support simple connection fail-over it is possible to define multiple endpoints (host and port pairs) in the connection url separated by commas.   
The driver will try to once connect to each of them in order until the connection succeeds.   
If none succeed, a normal connection exception is thrown.  
  
當配置了多個目標節點時，JDBC會按順序連接目標，直到連接成功為止。  
  
jdbc連接串語法  
  
The syntax for the connection url is:  
jdbc:postgresql://host1:port1,host2:port2/database  
  
The simple connection fail-over is useful when running against a high availability postgres installation that has identical data on each node.   
For example streaming replication postgres or postgres-xc cluster.

5.

典型的例子，應用程序可以配置兩個連接池，一個配置為master，寫請求發往這個連接池。另一個配置為slave優先，同時開啟負載均衡參數，讀請求發往這個連接池。

For example an application can create two connection pools.   
One data source is for writes, another for reads.   
The write pool limits connections only to master node:  
  
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=master .   
  
And read pool balances connections between slaves nodes, but allows connections also to master if no slaves are available:  
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true

PostgreSQL 10.0 libpq增加多個連接的功能

PostgreSQL 10.0 libpq層，也增加了多連接功能，設計時引入了target_session_attrs參數，可以設置為read-write或者any。不同的target_session_attrs配置，對應不同的節點檢測機製。

target_session_attrs=read-write，使用show transaction_read_only檢測節點，返回on，表示這是隻讀節點，off表示這是可讀寫節點。(standby返回on, 同時通過default_transaction_read_only可以讓master也返回on)。

target_session_attrs=any，表示不檢測。

兩個patch的commit信息如下。

libpq: Allow connection strings and URIs to specify multiple hosts.  
  
author	Robert Haas <rhaas@postgresql.org>	  
Thu, 3 Nov 2016 21:25:20 +0800 (09:25 -0400)  
committer	Robert Haas <rhaas@postgresql.org>	  
Thu, 3 Nov 2016 21:25:20 +0800 (09:25 -0400)  
commit	274bb2b3857cc987cfa21d14775cae9b0dababa5  
tree	488b5fd46e2cb4acdab7fb2dd30c4e4d1d4bb7d1	tree | snapshot  
parent	770671062f130a830aa89100c9aa2d26f8d4bf32	commit | diff  
libpq: Allow connection strings and URIs to specify multiple hosts.  
  
It's also possible to specify a separate port for each host.  
  
Previously, we'd loop over every address returned by looking up the  
host name; now, we'll try every address for every host name.  
  
Patch by me.  Victor Wagner wrote an earlier patch for this feature,  
which I read, but I didn't use any of his code.  Review by Mithun Cy.  

libpq: Add target_session_attrs parameter.  
  
author	Robert Haas <rhaas@postgresql.org>	  
Wed, 30 Nov 2016 01:18:31 +0800 (12:18 -0500)  
committer	Robert Haas <rhaas@postgresql.org>	  
Wed, 30 Nov 2016 01:18:31 +0800 (12:18 -0500)  
Commit 274bb2b3857cc987cfa21d14775cae9b0dababa5 made it possible to  
specify multiple IPs in a connection string, but that's not good  
enough for the case where you have a read-write master and a bunch of  
read-only standbys and want to connect to whichever server is the  
master at the current time.  This commit allows that, by making it  
possible to specify target_session_attrs=read-write as a connection  
parameter.  
  
There was extensive discussion of the best name for the connection  
parameter and its values as well as the best way to distinguish master  
and standbys.  For now, adopt the same solution as JDBC: if the user  
wants a read-write connection, issue 'show transaction_read_only' and  
rejection the connection if the result is 'on'.  In the future, we  
could add additional values of this new target_session_attrs parameter  
that issue different queries; or we might have some way of  
distinguishing the server type without resorting to an SQL query; but  
right now, we have this, and that's (hopefully) a good start.  
  
Victor Wagner and Mithun Cy.  Design review by Álvaro Herrera, Catalin  
Iacob, Takayuki Tsunakawa, and Craig Ringer; code review by me.  I  
changed Mithun's patch to skip all remaining IPs for a host if we  
reject a connection based on this new parameter, rewrote the  
documentation, and did some other cosmetic cleanup.  
  
Discussion: https://postgr.es/m/CAD__OuhqPRGpcsfwPHz_PDqAGkoqS1UvnUnOnAB-LBWBW=wu4A@mail.gmail.com

libpq用法介紹

URI格式

postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]  
  
postgresql://  
postgresql://localhost  
postgresql://localhost:5433  
postgresql://localhost/mydb  
postgresql://user@localhost  
postgresql://user:secret@localhost  
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp  
postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

配置多個目標節點，host:port使用逗號隔開即可。

host  
  
Comma-separated list of host names.   
If a host name begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication;   
the value is the name of the directory in which the socket file is stored.   
If multiple host names are specified, each will be tried in turn in the order given.   
The default behavior when host is not specified is to connect to a Unix-domain socket in /tmp (or whatever socket directory was specified when PostgreSQL was built).   
On machines without Unix-domain sockets, the default is to connect to localhost.  
  
port  
  
Port number to connect to at the server host, or socket file name extension for Unix-domain connections.   
If the host parameter included multiple, comma-separated hosts, this parameter may specify a list of ports of equal length,   
or it may specify a single port number to be used for all 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.

使用libpq，你同樣可以實現與jdbc一樣的效果（負載均衡，自動找到master）。

HA隻需要負責切換角色，不需要再負責切換IP地址了。可以更省心一些。

這個patch的討論，詳見郵件組，本文末尾URL。

PostgreSQL社區的作風非常嚴謹，一個patch可能在郵件組中討論幾個月甚至幾年，根據大家的意見反複的修正，patch合並到master已經非常成熟，所以PostgreSQL的穩定性也是遠近聞名的。

參考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=274bb2b3857cc987cfa21d14775cae9b0dababa5

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=721f7bd3cbccaf8c07cad2707826b83f84694832

https://www.postgresql.org/docs/devel/static/libpq-connect.html

https://jdbc.postgresql.org/documentation/94/connect.html

最後更新：2017-04-21 00:30:37

  上一篇：go PostgreSQL 10.0 preview 功能增強 - 增加access method CHECK接口amcheck
  下一篇：go 美麗景色