PostgreSQL JDBC Driver在驅動層面提供了資料庫的Failover和Load balance，相關的引數包括：

>targetServerType = String
Allows opening connections to only servers with required state, the allowed values are any, master, slave, secondary, preferSlave and preferSecondary. The master/slave distinction is currently done by observing if the server allows writes. The value preferSecondary tries to connect to secondary if any are available, otherwise allows falls back to connecting also to master.

指定目錄伺服器型別，可選項包括any（任意型別）, master（主庫）, slave（從庫）, secondary（列表中的第二個）, preferSlave（首選備庫） and preferSecondary（首選列表中的第二個）

>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.
預設禁用負載均衡，按列表順序先到先得。如啟用，則隨機從可用候選中選擇一個。

測試資料，建立資料表
```
[local]:5432 pg12@testdb=# create table tbl(id int,c1 varchar(10));
CREATE TABLE
Time: 144.018 ms
[local]:5432 pg12@testdb=# insert into tbl values(1,'1');
INSERT 0 1
Time: 41.481 ms
[local]:5432 pg12@testdb=#

```
Java測試程式碼
```Java

/*
 * TestFailoverAndLoadbalance
 *
 */
package testPG;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestFailoverAndLoadbalance {

public static void main(String[] args) {
		testLoadBalance();
	}

public static void testLoadBalance() {
		for (int i = 0; i < 10; i++) {
			try (Connection conn = DriverManager.getConnection(
					"jdbc:postgresql://192.168.26.28:5432,192.168.26.25:5432/testdb?targetServerType=any&loadBalanceHosts=true",
					"pg12", "root")) {
				System.out.println("NO:" + i);
				execSelect(conn);
				execInsert(conn);
			} catch (SQLException se) {
				System.out.println(se.getMessage());
			} catch (Exception e) {
				e.printStackTrace();
			} finally {

} // end try
		}
	}

public static void execSelect(Connection conn) {
		try (PreparedStatement pstmt = conn.prepareStatement("SELECT inet_server_addr() as ipaddr");
				ResultSet rs = pstmt.executeQuery();) {
			while (rs.next()) {
				String ipaddr = rs.getString("ipaddr");
				System.out.println("ipaddr:" + ipaddr + ";Execute SELECT");
			}

} catch (SQLException se) {
			System.out.println(se.getMessage());

} catch (Exception e) {
			e.printStackTrace();
		} finally {

} // end try

} // end

public static void execInsert(Connection conn) {
		try (PreparedStatement pstmtSelect = conn.prepareStatement("SELECT inet_server_addr() as ipaddr");
				ResultSet rs = pstmtSelect.executeQuery();
				PreparedStatement pstmtInsert = conn.prepareStatement("insert into tbl(id,c1) values(?,?)");) {
			while (rs.next()) {
				String ipaddr = rs.getString("ipaddr");
				System.out.println("ipaddr:" + ipaddr + ";Execute Insert");
				System.out.println();
				pstmtInsert.setInt(1, 2);
				pstmtInsert.setString(2, "2");
				pstmtInsert.executeUpdate();
			}

} catch (SQLException se) {
			System.out.println(se.getMessage());

} catch (Exception e) {
			e.printStackTrace();
		} finally {

} // end try

} // end

} // end ExecJDBC Class

```

targetServerType使用any（可用的任意一個伺服器），啟用負載均衡，這時候後隨機連線到任意一臺可用的伺服器上。
測試結果如下：
```
NO:0
ipaddr:192.168.26.25;Execute SELECT
ipaddr:192.168.26.25;Execute Insert

ERROR: cannot execute INSERT in a read-only transaction
NO:1
ipaddr:192.168.26.28;Execute SELECT
ipaddr:192.168.26.28;Execute Insert

NO:2
ipaddr:192.168.26.28;Execute SELECT
ipaddr:192.168.26.28;Execute Insert

NO:3
ipaddr:192.168.26.28;Execute SELECT
ipaddr:192.168.26.28;Execute Insert

NO:4
ipaddr:192.168.26.25;Execute SELECT
ipaddr:192.168.26.25;Execute Insert

ERROR: cannot execute INSERT in a read-only transaction
NO:5
ipaddr:192.168.26.28;Execute SELECT
ipaddr:192.168.26.28;Execute Insert

NO:6
ipaddr:192.168.26.28;Execute SELECT
ipaddr:192.168.26.28;Execute Insert

NO:7
ipaddr:192.168.26.25;Execute SELECT
ipaddr:192.168.26.25;Execute Insert

ERROR: cannot execute INSERT in a read-only transaction
NO:8
ipaddr:192.168.26.28;Execute SELECT
ipaddr:192.168.26.28;Execute Insert

NO:9
ipaddr:192.168.26.25;Execute SELECT
ipaddr:192.168.26.25;Execute Insert

ERROR: cannot execute INSERT in a read-only transaction

```
連線到備庫時，執行插入查詢會出錯，結果如預期。

**參考資料**
[Chapter 3. Initializing the Driver](https://jdbc.postgresql.org/documentation/head/connect.html?spm=a2c4e.10696291.0.0.172b19a4JpX4N0#connection-parameters)

PostgreSQL DBA(127) - Develop(JDBC failover&load balance)

相關文章