PostgreSQLlibpqmulti-host配置與簡單HA實現

德哥發表於2018-09-15
SQL

標籤

PostgreSQL , HA , libpq , multi host , target_session_attrs

背景

PostgreSQL libpq 是資料庫的一個連線驅動，支援多主機配置，同時支援target_session_attrs 主機角色判斷配置。

當配置了多個主機時，會按順序嘗試連線，之道獲取到成功的連線為止。

什麼是成功的連線，滿足以下兩點：

1、能與之成功建立連線。

2、如果target_session_attrs配置為read-write，那麼SHOW transaction_read_only 如果返回ON，則也是不成功的連線。

target_session_attrs配置為read-write的意思是，只連線到可以讀寫的節點，比如standby就是隻讀的，所以不能算成功的連線。

利用libpq的這個特性，結合資料庫自動HA的一些軟體，可以實現在不引入VIP以及中間路由節點的情況下實現資料庫應用系統層級的高可用。

例如，有流複製組成的兩個節點，或者由共享儲存組成的兩個節點。

在libpq中配置兩個節點的HOST:PORT，當資料庫某個主機發生異常切換到另一個主機，不需要漂移VIP，libpq可以幫你實現主節點的自動選擇。（當然，需要配合自動斷開重連的機制來實現）

也可以與三節點（多副本同步複製）結合來使用。

例子

格式

postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]

1、多HOST配置例子

postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

說明

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS

33.1.1.3. 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, 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 hostname can also 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 hostname 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, it is not possible to e.g.   
specify a different username for different 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.

如何判斷是否為備節點。

  
postgres=# select pg_is_in_recovery();  
 pg_is_in_recovery   
-------------------  
 t  
(1 row)  
  
  
備節點transaction_read_only引數為ON   
  
postgres=# show transaction_read_only ;  
 transaction_read_only   
-----------------------  
 on  
(1 row)

小結

優點：HA簡化了網路結構，因為不再需要管VIP漂移的事情。

缺點：因為CLIENT直接連物理主機IP，如果物理主機搬遷IP變化，需要調整libpq配置。

參考

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS

《PostgreSQL 一主多從(多副本,強同步)簡明手冊 – 配置、壓測、監控、切換、防腦裂、修復、0丟失 – 珍藏級》

《PG多節點(quorum based), 0丟失 HA(failover,switchover)方案》

https://github.com/digoal/PostgreSQL_HA_with_primary_standby_2vip


相關文章