Database and Node Uptime

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited September 10 in Vertica Tips

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

[[email protected] ~]$ 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/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/DATABASES.htm
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/MONITOR/NODE_STATES.htm

Have fun!

Sign In or Register to comment.