Recovery by table

Vertica supports node recovery on a per-table basis.

1: Prioritizing table recovery
2: Viewing table recovery status

Vertica supports node recovery on a per-table basis. Unlike node-based recovery, recovering by table makes tables available as they recover, before the node itself is completely restored. You can prioritize your most important tables so they become available as soon as possible. Recovered tables support all DDL and DML operations.

To enhance recovery speed, Vertica recovers multiple tables in parallel. The maximum number of tables recoverable at one time is set by the MAXCONCURRENCY parameter in the RECOVERY resource pool.

After a node has fully recovered, it enables full Vertica functionality.

1 - Prioritizing table recovery

You can specify the order in which Vertica recovers tables.

You can specify the order in which Vertica recovers tables. This feature ensures that your most critical tables become available as soon as possible. To specify the recovery order of your tables, assign an integer priority value. Tables with higher priority values recover first. For example, a table with a priority of 1000 is recovered before a table with a value of 500. Table priorities have the maximum value of a 64-bit integer.

If you do not assign a priority, or if multiple tables have the same priority, Vertica restores tables by OID order. Assign a priority with a query such as this:

=> SELECT set_table_recover_priority('avro_basic', '1000');
      set_table_recover_priority
---------------------------------------
 Table recovery priority has been set.
(1 row)

View assigned priorities with a query using this form: SELECT table_name,recover_priority FROM v_catalog.tables; The next example shows prioritized tables from the VMart sample database. In this case, the table with the highest recovery priorities are listed first (DESC). The shipping_dimension table has the highest priority and will be recovered first. (Example has hard Returns for display purposes.)

=> SELECT table_name AS Name, recover_priority from v_catalog.tables WHERE recover_priority > 1
   ORDER BY recover_priority DESC;
        Name         | recover_priority
---------------------+------------------
 shipping_dimension  |            60000
 warehouse_dimension |            50000
 employee_dimension  |            40000
 vendor_dimension    |            30000
 date_dimension      |            20000
 promotion_dimension |            10000
 iris2               |             9999
 product_dimension   |               10
 customer_dimension  |               10
(9 rows)

2 - Viewing table recovery status

View general information about a recovery querying the V_MONITOR.TABLE_RECOVERY_STATUS table.

View general information about a recovery querying the V_MONITOR.TABLE_RECOVERY_STATUS table. You can also view detailed information about the status of the recovery the table being restored by querying the V_MONITOR.TABLE_RECOVERIES table.