关于vertica的Connection Failover

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

ODBC Connection Failover

官方给出的代码如下：

[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

代码：

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连接串来实现：

jdbc:vertica://192.168.111.25:5433/DBname?ConnectionLoadBalance=1&BackupServerNode=192.168.111.12:5433,192.168.111.13:5433

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

SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');

取消该功能：

SELECT SET_LOAD_BALANCE_POLICY('NONE');

查询是否开启：

SELECT GET_LOAD_BALANCE_POLICY();

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

<script type="text/javascript"> $(function () { $('pre.prettyprint code').each(function () { var lines = $(this).text().split('\n').length; var $numbering = $('<ul/>').addClass('pre-numbering').hide(); $(this).addClass('has-numbering').parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($('<li/>').text(i)); }; $numbering.fadeIn(1700); }); }); </script>