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

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测试代码

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