Delete Vector is also recovered with Full Database Restore?

KaitoKaito Employee, Registered User

My customer wants to recover all of a database with full restore. Delete Vector status is also recovered?

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BackupRestore/RestoringFullDatabaseBackups.htm

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Hi,

    All deleted vectors associated with committed data at the point of the full backup will be restored from that backup.

    Quick Example:

    [[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 current_session();
             current_session
    ---------------------------------
     _dv_restore_node0001-19217:0x46
    (1 row)
    
    dbadmin=> SELECT * FROM some_table;
     c
    ---
     1
     2
     3
     4
     5
    (5 rows)
    
    dbadmin=> DELETE FROM some_table WHERE c = 2;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> COMMIT;
    COMMIT
    
    dbadmin=> SELECT projection_name, deleted_row_count, start_epoch, end_epoch FROM delete_vectors;
     projection_name  | deleted_row_count | start_epoch | end_epoch
    ------------------+-------------------+-------------+-----------
     some_table_super |                 1 |          21 |        21
    (1 rows)
    

    Now, from another session, I will take a full DB backup, shut down the DB and then restore the DB:

    [[email protected] ~]$ vbr -t init -c backup_restore_full_local.ini
    Initializing backup locations.
    Backup locations initialized.
    
    [[email protected] ~]$ vbr -t backup -c backup_restore_full_local.ini
    Starting backup of database test_dv_restore.
    Participating nodes: v_test_dv_restore_node0001.
    Snapshotting database.
    
    Snapshot complete.
    Approximate bytes to copy: 461153485 of 461153485 total.
    [==================================================] 100%
    Copying backup metadata.
    Finalizing backup.
    Backup complete!
    
    [[email protected] ~]$ vsql -c "SELECT backup_timestamp, node_name, backup_epoch FROM database_backups;"
           backup_timestamp        |         node_name          | backup_epoch
    -------------------------------+----------------------------+--------------
     2018-12-27 11:33:53.468997-05 | v_test_dv_restore_node0001 |           21
    (1 rows)
    
    [[email protected] ~]$ admintools -t stop_db -d test_dv_restore -F
    Info: no password specified, using none
            Connecting to database
            Issuing shutdown command to database
    Database test_dv_restore stopped successfully
    
    [[email protected] ~]$ vbr -t restore -c backup_restore_full_local.ini
    Starting full restore of database test_dv_restore.
    Participating nodes: v_test_dv_restore_node0001.
    Restoring from restore point: backup_snapshot_20181227_163055
    Determining what data to restore from backup.
    [==================================================] 100%
    Approximate bytes to copy: 461152768 of 461153485 total.
    Syncing data from backup to cluster nodes.
    [==================================================] 100%
    Restoring catalog.
    Restore complete!
    
    [[email protected] ~]$ admintools -t start_db -d test_dv_restore
    Info: no password specified, using none
            Starting nodes:
                    v_test_dv_restore_node0001 (192.168.2.210)
            Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
            Node Status: v_test_dv_restore_node0001: (DOWN)
            Node Status: v_test_dv_restore_node0001: (DOWN)
            Node Status: v_test_dv_restore_node0001: (INITIALIZING)
            Node Status: v_test_dv_restore_node0001: (UP)
    Database test_dv_restore: Startup Succeeded.  All Nodes are UP
    
    [[email protected] ~]$ vsql -c "SELECT get_current_epoch();"
     get_current_epoch
    -------------------
                    22
    (1 row)
    
    [[email protected] ~]$ vsql -c "SELECT * FROM some_table;"
     c
    ---
     1
     3
     4
     5
    (4 rows)
    
    [[email protected] ~]$ vsql -c "SELECT projection_name, deleted_row_count, start_epoch, end_epoch FROM delete_vectors;"
     projection_name  | deleted_row_count | start_epoch | end_epoch
    ------------------+-------------------+-------------+-----------
     some_table_super |                 1 |          21 |        21
    (1 row)
    

    The delete vector was restored!

  • KaitoKaito Employee, Registered User

    Thank you, Jim!!!

Leave a Comment

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