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

PostgreSQL JDBC Driver provides the Failover and Load balance of the database at the driver level. Related parameters include:

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.

Specify the directory server type, options include any (any type), master (master library), slave (slave library), secondary (second in the list), preferSlave (preferred standby library) and preferSecondary (second in the preferred list)

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.

Load balancing is disabled by default and is listed on a first-come-first-served basis. If enabled, randomly select one of the available candidates.

Test data, create datasheets

[local]: 5432 pg12@testdb=# create table tbl (id int,c1 varchar (10)); CREATE TABLETime: 144.018 ms [local]: 5432 pg12@testdb=# insert into tbl values; INSERT 0 1Time: 41.481 ms [local]: 5432 pg12@testdb=#

Java test code

/ * * 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) Try (Connection conn = DriverManager.getConnection ("jdbc:postgresql://192.168.26.28:5432192.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 uses any (any available server), enables load balancing, and then randomly connects to any available server.

The test results are as follows:

NO:0ipaddr:192.168.26.25;Execute SELECTipaddr:192.168.26.25;Execute InsertERROR: cannot execute INSERT in a read-only transactionNO:1ipaddr:192.168.26.28;Execute SELECTipaddr:192.168.26.28;Execute InsertNO:2ipaddr:192.168.26.28;Execute SELECTipaddr:192.168.26.28;Execute InsertNO:3ipaddr:192.168.26.28;Execute SELECTipaddr:192.168.26.28;Execute InsertNO:4ipaddr:192.168.26.25;Execute SELECTipaddr:192.168.26.25 Execute InsertERROR: cannot execute INSERT in a read-only transactionNO:5ipaddr:192.168.26.28;Execute SELECTipaddr:192.168.26.28;Execute InsertNO:6ipaddr:192.168.26.28;Execute SELECTipaddr:192.168.26.28;Execute InsertNO:7ipaddr:192.168.26.25;Execute SELECTipaddr:192.168.26.25;Execute InsertERROR: cannot execute INSERT in a read-only transactionNO:8ipaddr:192.168.26.28;Execute SELECTipaddr:192.168.26.28;Execute InsertNO:9ipaddr:192.168.26.25 Execute SELECTipaddr:192.168.26.25;Execute InsertERROR: cannot execute INSERT in a read-only transaction

When connecting to the standby library, performing an insert lookup will cause an error and the result will be as expected.

references

Chapter 3. Initializing the Driver