Database and Node Uptime: Quick Tip

You can query the DATABASES system table to find out the last time your Vertica database started and you can get the cluster node up times by querying the NODE_STATES system table.

Example:

dbadmin=> SELECT database_name, start_time
dbadmin-> FROM databases;
database_name | start_time
---------------+-------------------------------
test_db | 2018-09-06 14:33:07.301363-04
(1 row)

dbadmin=> SELECT node_name, MAX(event_timestamp) node_uptime
dbadmin-> FROM node_states
dbadmin-> WHERE node_state = ‘UP’
dbadmin-> GROUP BY node_name
dbadmin-> ORDER BY node_name;
node_name | node_uptime
——————–+——————————-
v_test_db_node0001 | 2018-09-06 14:33:07.248692-04
v_test_db_node0002 | 2018-09-06 14:33:07.26675-04
v_test_db_node0003 | 2018-09-06 14:33:07.243385-04
(3 rows)

Typically, your cluster nodes will have the same uptime as the database, unless you had to restart one of the nodes later.

Example:

dbadmin=> SELECT local_node_name();
local_node_name
--------------------
v_test_db_node0001
(1 row)

dbadmin=> SELECT restart_this_node();
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q

[dbadmin@vertica01 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

dbadmin=> SELECT node_name, MAX(event_timestamp) node_uptime
dbadmin-> FROM node_states
dbadmin-> WHERE node_state = ‘UP’
dbadmin-> GROUP BY node_name
dbadmin-> ORDER BY node_name;
node_name | node_uptime
——————–+——————————-
v_test_db_node0001 | 2018-09-10 09:52:26.76466-04
v_test_db_node0002 | 2018-09-06 14:33:07.26675-04
v_test_db_node0003 | 2018-09-06 14:33:07.243385-04
(3 rows)

Helpful links:

https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/DATABASES.htm

https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/NODE_STATES.htm

Have fun!