Rolling Back the Database to the Last Good Epoch

Vertica provides the ability to roll the entire database back to a specific epoch primarily to assist in the correction of human errors during data loads or other accidental corruptions. For example, suppose that you have been performing a bulk load and the cluster went down during a particular COPY command. You might want to discard all epochs back to the point at which the previous COPY command committed and run the one that did not finish again. You can determine that point by examining the log files (see Monitoring the Log Files).

  1. On the Advanced Menu, select Roll Back Database to Last Good Epoch.
  2. Select the database to roll back. The database must be stopped.
  3. Accept the suggested restart epoch or specify a different one.

  4. Confirm that you want to discard the changes after the specified epoch.

The database restarts successfully.

The default value of HistoryRetentionTime is 0, which means that Vertica only keeps historical data when nodes are down. This settings prevents the use of the Administration Tools 'Roll Back Database to Last Good Epoch' option because the AHM remains close to the current epoch. Vertica cannot roll back to an epoch that precedes the AHM.

If you rely on the Roll Back option to remove recently loaded data, consider setting a day-wide window for removing loaded data. For example:

=> ALTER DATABASE DEFAULT SET HistoryRetentionTime = 86400;