PostgreSQLDBA(127)-Develop(JDBCfailover&loadbalance)

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測試代碼

/*
 * 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