How to recover deleted rows?

anybody know, how we can recover rows that was deleted 3 hours ago? we don't have backup.


  • Options

    Unfortunately there isn't any transactional or table level "undo". Hence here you'd have to figure out if you can reload the deleted data.
    However, You can rollback the entire database to some previous LGE. It would let you go back to the epoch for the AHM. If your history retention is set to 0 then AHM is one epoch prior to current epoch and probably won't get you back far enough in time. The HistoryRetentionTime configuration parameter determines how long deleted data is available before it's eligible to be purged. 

    You can find the history settings and the current and AHM epoch values as below.

    select current_epoch,ahm_epoch from system;
    > current_epoch | ahm_epoch
    98 | 97
  • Options
    If you have history retention setup to maintain 3 hour history (configuration parameters "HistoryRetentionTime" or "HistoryRetentionEpochs"),then you can probably query those deleted records using a historical query.

    vsql=> AT TIME 'timestamp' select * from table;
    vsql=> AT EPOCH <epoch number> select * from table;

    Vertica Doc:
    Programmer's Guide --> Writing Queries --> Historical (Snapshot) Queries

    If history retention is off, these records are probably purged by now.

    Hope this helps. 

  • Options
    thank you very much all ! 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file