Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to recover deleted rows?

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

Comments

  • Hi,

    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
  • 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;
    or 
    vsql=> AT EPOCH <epoch number> select * from table;

    Vertica Doc:
    https://my.vertica.com/docs/7.1.x/HTML/index.htm
    Programmer's Guide --> Writing Queries --> Historical (Snapshot) Queries


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

    Hope this helps. 

    Thanks.
  • thank you very much all ! 

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.