Is it safe to use -F option on start_db of admintools?

If ASR is required the -F likely won't help.

Example:

[dbadmin@s18384357 ~]$ vsql -c "SELECT node_name FROM nodes;"
     node_name
--------------------
 v_test_db_node0001
(1 row)

[dbadmin@s18384357 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch, refresh_epoch FROM system;"
 current_epoch | ahm_epoch | last_good_epoch | refresh_epoch
---------------+-----------+-----------------+---------------
           228 |       226 |             226 |            -1
(1 row)

[dbadmin@s18384357 ~]$ admintools -t kill_node -s v_test_db_node0001
*** Terminating vertica and performing host cleanup ***
        Terminating vertica processes on host '74.208.100.58'
All signals sent successfully.

[dbadmin@s18384357 ~]$ admintools -t start_db -d test_db -F
Info: no password specified, using none
        Starting nodes:
                v_test_db_node0001 (74.208.100.58)
        Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
Found these errors in startup.logs on hosts:
host ['74.208.100.58'] report: Startup Failed, ASR Required
Press RETURN to continue
Database test_db did not start successfully: Something Failed

But you can restart the DB at the LGE:

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|226

[dbadmin@s18384357 ~]$ admintools -t restart_db -d test_db -e 226
Info: no password specified, using none
*** Restarting database test_db at '2019-03-05 23:59:02.136161-05' epoch 226 ***
        Starting nodes:
                v_test_db_node0001 (74.208.100.58)
        Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (RECOVERING)
        Node Status: v_test_db_node0001: (UP)
Database startup successful

I think the bigger issue is why you need ASR? Is the DB crashing or not being shut down cleanly?

@Jim_Knicely, thanks for the response and clarification. I believe it happened because the DB was not shutdown cleanly.
Am I right in saying that it is legitimate to automate these steps when it fails with "ASR Required"?

As long as you are okay with loosing any DB changes after the LGE, I think it's fine to automate the restart.

Note, you can combine the two calls to admintools like this:

admintools -t restart_db -d test_db -e `admintools -t return_epoch -d test_db | sed 's/LastEpoch|//'`

Just replace test_db with your database name.

@Jim_Knicely, sounds good, thanks. One last question -
Does 'return_epoch' option returns current epoch in case of normal situation where database was shutdown properly? Asking this to make sure whether it is possible to combine those 2 calls (return_epoch and restart_db) always which should work in both cases - graceful db shutdown and unclean db shutdown

Yup!

[dbadmin@s18384357 ~]$ admintools -t stop_db -d test_db -F
Info: no password specified, using none
        Connecting to database
        Issuing shutdown command to database
Database test_db stopped successfully

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|238

[dbadmin@s18384357 ~]$ admintools -t restart_db -d test_db -e `admintools -t return_epoch -d test_db | sed 's/LastEpoch|//'`
Info: no password specified, using none
*** Restarting database test_db at '2019-03-06 23:59:03.433907-05' epoch 238 ***
        Starting nodes:
                v_test_db_node0001 (74.208.100.58)
        Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (RECOVERING)
        Node Status: v_test_db_node0001: (UP)
Database startup successful

@hrao -

The return_epoch tool of admintools shows the "Last Good Epoch", not the "Current Epoch". The restart_db too will always try to do ASR (Admin Specified Recovery) to the LGE.

Example on a 3 node cluster:

[dbadmin@SE-Sandbox-43-node1 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
Timing is on.
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
          4030 |      4029 |            4029
(1 row)

[dbadmin@SE-Sandbox-43-node1 ~]$ admintools -t return_epoch -d vmart
LastEpoch|4029

[dbadmin@SE-Sandbox-43-node1 ~]$ admintools -t stop_db -d vmart -F
Info: no password specified, using none
        Connecting to database
        Issuing shutdown command to database
Database vmart stopped successfully

[dbadmin@SE-Sandbox-43-node1 ~]$ admintools -t return_epoch -d vmart
LastEpoch|4029

[dbadmin@SE-Sandbox-43-node1 ~]$ admintools -t restart_db -d vmart -e `admintools -t return_epoch -d vmart | sed 's/LastEpoch|//'`
Info: no password specified, using none
*** Restarting database vmart at '2019-03-06 16:08:12.518078-05' epoch 4029 ***
        Starting nodes:
                v_vmart_node0001 (192.168.61.227)
                v_vmart_node0002 (192.168.61.228)
                v_vmart_node0003 (192.168.61.229)
        Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
        Node Status: v_vmart_node0001: (DOWN) v_vmart_node0002: (DOWN) v_vmart_node0003: (DOWN)
Nodes in TRANSITIONAL state: 192.168.61.228, 192.168.61.229, 192.168.61.227
Nodes DOWN: v_vmart_node0001, v_vmart_node0002, v_vmart_node0003 (may be still initializing).
It is suggested that you continue waiting.
Do you want to continue waiting? (yes/no) [yes] yes
        Node Status: v_vmart_node0001: (RECOVERING) v_vmart_node0002: (RECOVERING) v_vmart_node0003: (RECOVERING)
        Node Status: v_vmart_node0001: (RECOVERING) v_vmart_node0002: (RECOVERING) v_vmart_node0003: (RECOVERING)
        Node Status: v_vmart_node0001: (RECOVERING) v_vmart_node0002: (RECOVERING) v_vmart_node0003: (RECOVERING)
        Node Status: v_vmart_node0001: (UP) v_vmart_node0002: (UP) v_vmart_node0003: (UP)
Database startup successful

[dbadmin@SE-Sandbox-43-node1 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
Timing is on.
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
          4031 |      4030 |            4030
(1 row)

So, return_epoch value at least 1 epoch less than current value. In case of graceful shutdown, we should not use the return value of 'return_epoh' as input to restart_db but it can be used for ASR Required scenario only. Am I right? If so, we can't write a common playbook that covers both graceful shutdown of the database and failure of database for some unknown reason.

When it reports ASR Required, start_db command of admintools hangs without returning. I see timeout option but it is hard to guess a value that should be specified, so don't want to use it.

After a clean shut down the LGE will advance and your data will be safe on disk.

Example:

[dbadmin@s18384357 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
           240 |       239 |             239
(1 row)

[dbadmin@s18384357 ~]$ vsql -c "ALTER DATABASE test_db SET HistoryRetentionTime = 86400;"
ALTER DATABASE

[dbadmin@s18384357 ~]$ vsql -c "CREATE TABLE lge (c INT); INSERT INTO lge SELECT 1; COMMIT;"
COMMIT

[dbadmin@s18384357 ~]$ vsql -c "INSERT INTO lge SELECT 1; COMMIT; INSERT INTO lge SELECT 1; COMMIT;"
COMMIT

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM lge;"
 c
---
 1
 1
 1
(3 rows)

[dbadmin@s18384357 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
           243 |       239 |             239
(1 row)

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|239

[dbadmin@s18384357 ~]$ admintools -t stop_db -d test_db -F
Info: no password specified, using none
        Connecting to database
        Issuing shutdown command to database
Database test_db stopped successfully

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|242

[dbadmin@s18384357 ~]$ admintools -t restart_db -d test_db -e `admintools -t return_epoch -d test_db | sed 's/LastEpoch|//'`
Info: no password specified, using none
*** Restarting database test_db at '2019-03-07 10:33:24.718447-05' epoch 242 ***
        Starting nodes:
                v_test_db_node0001 (74.208.100.58)
        Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (RECOVERING)
        Node Status: v_test_db_node0001: (UP)
Database startup successful

[dbadmin@s18384357 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
           244 |       239 |             243
(1 row)

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM lge;"
 c
---
 1
 1
 1
(3 rows)

So although Vertica does a RECOVERY step because I requested an ASR, it is not needed here, but I don't think it does anything to cause you to lose committed data. Although, on a large cluster it may increase the time needed to start as Vertica is checking if Recovery is needed on each node.

Another example...

[dbadmin@s18384357 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
           259 |       239 |             257
(1 row)

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|257

[dbadmin@s18384357 ~]$ vsql -c "INSERT INTO lge SELECT 1; COMMIT; INSERT INTO lge SELECT 1; COMMIT; INSERT INTO lge SELECT 1; COMMIT;"
COMMIT

[dbadmin@s18384357 ~]$ vsql -c "DELETE FROM lge; COMMIT;"
COMMIT

[dbadmin@s18384357 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
           263 |       239 |             257
(1 row)

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|257

[dbadmin@s18384357 ~]$ admintools -t stop_db -d test_db -F
Info: no password specified, using none
        Connecting to database
        Issuing shutdown command to database
Database test_db stopped successfully

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|262

[dbadmin@s18384357 ~]$ admintools -t restart_db -d test_db -e `admintools -t return_epoch -d test_db | sed 's/LastEpoch|//'`
Info: no password specified, using none
*** Restarting database test_db at '2019-03-07 11:06:03.000156-05' epoch 262 ***
        Starting nodes:
                v_test_db_node0001 (74.208.100.58)
        Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (RECOVERING)
        Node Status: v_test_db_node0001: (UP)
Database startup successful

[dbadmin@s18384357 ~]$ admintools -t return_epoch -d test_db
LastEpoch|263
[dbadmin@s18384357 ~]$ vsql -c "SELECT current_epoch, ahm_epoch, last_good_epoch FROM system;"
 current_epoch | ahm_epoch | last_good_epoch
---------------+-----------+-----------------
           264 |       239 |             263
(1 row)

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM lge;"
 c
---
(0 rows)

If you automate this, I wouldn't do it in a single combined step. Do it in two separate steps and verify that the epoch returned is not something bogus like 0, which would rollback the database to any empty database. I've seen this situation when something is wrong with the environment, such as disks being inaccessible. Better safe than sorry.

It can also happen - if you aren't monitoring LGE - that LGE can get stuck behind because of data stuck in the WOS that is unable to moveout. This is another case where you could rollback weeks or months of data. In this case if there's a single table or projection that is holding back LGE, the technical support team can help to disable that single table or projection to allow the database to come up without rolling back.

Yeah! Heed Sharons' warnings! Especially about the LGE lagging! I of course was making the assumption the LGE was moving along as expected

See:
https://www.vertica.com/blog/ancient-history-mark-not-advancing/
https://www.vertica.com/kb/Node-Recovery-in-Vertica/Content/BestPractices/Node-Recovery-in-Vertica.htm

Thanks @Sharon_Cutter for the insights and caution. Looks like manual intervention is better when ASR Required is reported.
@Jim_Knicely, since getting correct LGE values is not guaranteed from 'return_epoch, and depending it (even filtering Zero) to roll back to non-zero LGE value has a potential to loose some data. Is my understanding correct?

Btw I think what's really desired here is using "-t restart_db -e last" which asks for a restart using the Last Good Epoch. Nothing additional needed on the command line. But, I don't know what would happen in the extreme cases where LGE might be 0 due to disk issues etc - would need to look at the admintools code (which I haven't done).

Some more background on how LGE is determined, which related to the -t return_epoch usage. During a normal shutdown, every node writes out the LGE to a file. On a subsequent startup, that file is read by admintools and used as the startup epoch (the value possibly negotiated among the nodes if the values differ).

But in the case of an abnormal shutdown - either processes were killed or they exited after an exception - that LGE was not written out to the file. So on the next startup attempt, all nodes will write out the LGE and then shutdown. It then takes another startup attempt asking vertica to start from a specific epoch.

So in Jim's example above, "-t return_epoch" was simply reporting that there's no LGE to return since the nodes exited abnormally.