Vertica的这些事(七)——-关于vertica的Connection-Failover

最近在生产中发现vertica有个别节点老是宕机(又碰到的童鞋交流下),实际业务中有Python通过odbc连接vertica,还有Java通过jdbc连接vertica。假如你连接的那个节点正好是down的节点或者是standby节点,那么就会连接失败,程序就会报错,影响正常业务。其实vertica官方文档已经给出了解决方法,下面我们来看一下如何实现。

ODBC Connection Failover

官方给出的代码如下:

1
2
3
4
5
6
[VMartBadNode]
Description=VMart Vertica Database
Driver=/opt/vertica/lib64/libverticaodbc.so
Database=VMart
Servername=badnode.example.com
BackupServerNode=node02.example.com,node03.example.com

其中上面的BackupServerNode就是备用的IP

JDBC Connection Failover

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import java.sql.*;
import java.util.Properties;

public class ConnectionFailoverExample {
public static void main(String[] args) {
// Assume using JDBC 4.0 driver on JVM 6+. No driver loading needed.
Properties myProp = new Properties();
myProp.put("user", "dbadmin");
myProp.put("password", "vertica");
// Set two backup hosts to be used if connecting to the first host
// fails. All of these hosts will be tried in order until the connection
// succeeds or all of the connections fail.
myProp.put("BackupServerNode", "VerticaHost02,VerticaHost03");
Connection conn;
try {
// The connection string is set to try to connect to a known
// bnad host (in this case, a host that never existed).
conn = DriverManager.getConnection(
"jdbc:vertica://BadVerticaHost:5433/vmart", myProp);
System.out.println("Connected!");
// Query system to table to see what node we are connected to.
// Assume a single row in response set.
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT node_name FROM v_monitor.current_session;");
rs.next();
System.out.println("Connected to node " + rs.getString(1).trim());
// Done with connection.
conn.close();
} catch (SQLException e) {
// Catch-all for other exceptions
e.printStackTrace();
}
}
}

另外也可以通过jdbc连接串来实现:

1
jdbc:vertica://192.168.000.00:5433/DBname?ConnectionLoadBalance=1&BackupServerNode=192.168.000.01:5433,192.168.000.02:5433

其中ConnectionLoadBalance是vertica的负载均衡,如果在库中没有开启该功能的话,此时是没有作用的,不影响。
开启ConnectionLoadBalance的话,需要管理员账户

1
SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');

取消该功能:

1
SELECT SET_LOAD_BALANCE_POLICY('NONE');

查询是否开启:

1
SELECT GET_LOAD_BALANCE_POLICY();

通过这次设置,加了几个备用连接节点,确保业务正常。

-------------���Ľ�����л�����Ķ�-------------
qinchaofeng wechat
坚持原创技术分享,您的支持将鼓励我继续创作!