Delete Vector is also recovered with Full Database Restore?

Kaito

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

Jim_Knicely

Hi,

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

Quick Example:

[dbadmin@vertica9 ~]$ 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:

[dbadmin@vertica9 ~]$ vbr -t init -c backup_restore_full_local.ini
Initializing backup locations.
Backup locations initialized.

[dbadmin@vertica9 ~]$ 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!

[dbadmin@vertica9 ~]$ 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)

[dbadmin@vertica9 ~]$ 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

[dbadmin@vertica9 ~]$ 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!

[dbadmin@vertica9 ~]$ 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

[dbadmin@vertica9 ~]$ vsql -c "SELECT get_current_epoch();"
 get_current_epoch
-------------------
                22
(1 row)

[dbadmin@vertica9 ~]$ vsql -c "SELECT * FROM some_table;"
 c
---
 1
 3
 4
 5
(4 rows)

[dbadmin@vertica9 ~]$ 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!

Kaito

Thank you, Jim!!!