PostgreSQLDBA(127)-Develop(JDBCfailover&loadbalance)

PostgreSQL JDBC Driver在驅(qū)動層面提供了數(shù)據(jù)庫的Failover和Load balance，相關(guān)的參數(shù)包括：

成都創(chuàng)新互聯(lián)專注于墊江企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站設(shè)計(jì),商城網(wǎng)站開發(fā)。墊江網(wǎng)站建設(shè)公司,為墊江等地區(qū)提供建站服務(wù)。全流程按需制作網(wǎng)站，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

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.

指定目錄服務(wù)器類型，可選項(xiàng)包括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.
默認(rèn)禁用負(fù)載均衡，按列表順序先到先得。如啟用，則隨機(jī)從可用候選中選擇一個。

測試數(shù)據(jù)，創(chuàng)建數(shù)據(jù)表

[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（可用的任意一個服務(wù)器），啟用負(fù)載均衡，這時候后隨機(jī)連接到任意一臺可用的服務(wù)器上。
測試結(jié)果如下：

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

連接到備庫時，執(zhí)行插入查找會出錯，結(jié)果如預(yù)期。

參考資料
Chapter 3. Initializing the Driver