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

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

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ù)器類型，可選項包括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.
默認禁用負載均衡，按列表順序先到先得。如啟用，則隨機從可用候選中選擇一個。

測試數(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ù)器），啟用負載均衡，這時候后隨機連接到任意一臺可用的服務(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