3.2. Managing the Database Using voltadmin and sqlcmd

Documentation

VoltDB Home » Documentation » VoltDB Kubernetes Administrator's Guide

3.2. Managing the Database Using voltadmin and sqlcmd

You manage the database using the VoltDB command line utilities voltadmin and sqlcmd, the same way you would in a traditional server environment. The one difference is that before you can issue VoltDB commands, you need to decide how to access the database cluster itself. There are two types of access available to you:

  • Interactive access for issuing sqlcmd or voltadmin commands to manage the database

  • Programmatic access, through the client or admin port, for invoking stored procedures

3.2.1. Accessing the Database Interactively

Kubernetes provides several ways to access the pods running your services. You can run commands on individual pods interactively through the kubectl exec command. You can use the same command to access the command shell for the pod by running bash. Or you can use port forwarding to open ports from the pods to your current environment.

In all three cases, you need to know the name of the pod you wish to access. When you start a VoltDB cluster with Helm, the pods are created with templated names based on the Helm release name and a sequential number. So if you named your three node cluster mydb, the pods would be called mydb-voltdb-cluster-0, mydb-voltdb-cluster-1, and mydb-voltdb-cluster-2. There are also separate pods for any auxiliary services, such as the Volt Management Center (VMC) and the Prometheus agent. If you are not sure of the names, you can use the kubectl get pods command to see a list:

$ kubectl get pods
NAME                                    READY   STATUS    RESTARTS   AGE
mydb-voltdb-cluster-0                   1/1     Running   0          26m
mydb-voltdb-cluster-1                   1/1     Running   0          26m
mydb-voltdb-operator-6bbb96b575-8z75x   1/1     Running   0          26m
mydb-voltdb-vmc-86c8d7b688-pnmlg        1/1     Running   0          26m

Having chosen a pod to use, running VoltDB commands interactively with kubectl exec is useful for issuing individual commands. After the command executes, kubectl returns you to your local shell. For example, you can check the status of the cluster using the voltadmin status command:

$ kubectl exec -it mydb-voltdb-cluster-0 -- voltadmin status
Cluster 0, version 10.0, hostcount 2, kfactor 0
 2 live host, 0 missing host, 0 live client, uptime 0 days 00:41:34.293
-----------------------------------------------
  HostId       Host Name
       0mydb-voltdb-cluster-0
       1mydb-voltdb-cluster-1

You can even use kubectl exec to start an interactive sqlcmd session, which stays active until you exit sqlcmd:

$ kubectl exec -it mydb-voltdb-cluster-0 -- sqlcmd
SQL Command :: localhost:21212
1> exit
$

Or you can pipe a file of SQL statements to sqlcmd as part of the command:

$ kubectl exec -it mydb-voltdb-cluster-0 -- sqlcmd < myschema.sql

However, kubectl exec commands execute in the context of the pod. So you cannot do things like load JAR files that are in your local directory. If you need to load schema and stored procedures, it is easier to use port forwarding, where ports on the pod are forwarded to the equivalent ports on localhost for your local machine, so you can run applications and utilities (such as sqlcmd, voltdb, and voltadmin) locally.

The kubectl port-forward command initiates port forwarding, which is active until you stop the command process. So you need a second process to utilize the linked ports. In the following example the user runs the voter sample application locally on a database in a Kubernetes cluster. To do this, one session enables port forwarding on the client port and the second session loads the stored procedures, schema, and then runs the client application:

Session #1

$ kubectl port-forward mydb-voltdb-cluster-0 21212

Session #2

$ cd ~/voltdb/examples/voter
$ sqlcmd 
SQL Command :: localhost:21212
1> load classes voter-procs.jar;
2> file ddl.sql;
3> exit
$ ./run.sh client

Port forwarding is useful for ad hoc activities such as loading schema and stored procedures to a running database and quick test runs of client applications. Port forwarding is not good for running production applications or any ongoing activities, due to its inherent lack of security or robustness as a network solution.

You can also use port forwarding to monitor the cluster using the web-based Volt Management Center (VMC) by forwarding port 8080 from the VMC service, using the service name. The following example also adds the --address argument so it is available to others on the local area network. (Otherwise it is only accessible as localhost:8080 from the system on which the port forward command is issued.)

$ kubectl port-forward svc/mydb-voltdb-vmc 8080 --address=0.0.0.0 

Note that there is only one instance of VMC for the entire cluster. By forwarding the port from the VMC service you can access all nodes of the cluster by using the servers menu on the DB Monitor tab.

3.2.2. Accessing the Database Programmatically

The approaches for connecting to the database interactively do not work for access by applications, because interactive access focuses on connecting to one node of the database. Applications are encouraged to create connections to all nodes of the database to distribute the workload and avoid bottle necks. In fact, the Java client for VoltDB has special settings to automatically connect to all available nodes (topology awareness) and direct partitioned procedures to the appropriate host (client affinity).

Kubernetes provides a number of services to make pods accessible beyond the Kubernetes cluster they run in; services such as cluster IPs, node ports, and load balancers. These services usually change the address and/or port number seen outside the cluster. And there are still other layers of networking and firewalls to traverse before these open ports are accessible outside of Kubernetes itself. This complexity, plus the fact that these services result in port numbers and external network addresses that do not match what the database itself thinks it is running on, make accessing the database from external applications impractical.

The recommended way to access a VoltDB database running in Kubernetes programmatically is to run your application as its own service within the same Kubernetes cluster as the database. This way you can take advantage of the existing VoltDB service names, such as mydb-voltdb-cluster-client, to connect to the database. You can then enable topology awareness in the Java cient and let the client make the appropriate connections to the current VoltDB host IPs.

For example, if your database Helm release is called mydb and is running in the namespace mydata, the Java application code to initiate access to the database might look like the following:

org.voltdb.client.Client client = null;

ClientConfig config = new ClientConfig("","");
config.setTopologyChangeAware(true);

client = ClientFactory.createClient(config);
client.createConnection("mydb-voltdb-cluster-client.mydata.svc.cluster.local");