Restoring Objects from a Full Backup

Posted November 24, 2015 by Sarah Lemaire, Manager, Vertica Documentation

Imagine you have a storage unit. It?’s a great unit, it can fit all of your possessions. But what if any time you want to take out one thing from the unit, you were forced to haul everything back home and then pick out what you want? It?’s November, you don’?t want to spend time going through all the spring decorations just to find your Thanksgiving decorations. Do you? I didn’?t think so. One option you have is to store each object in a separate storage unit. Then when you need one specific thing, you just go straight to that unit. But that doesn?’t sound ideal either.

The same is true for your data. Before Vertica version 7.2, you had two options: perform full backups and restore full backups (remove everything from the storage unit) or perform object-level backups and restore those individually (maintain separate storage units). But as of Vertica 7.2, you can have the best of both worlds. You can perform full backups and restore individual objects from that backup! This means you don’?t have to waste time and resources on restoring data that you do not need at the moment.



Backup overview

First, let?’s take a moment to refresh our minds on how backups work. Vertica comes with a utility to help you perform backups. It?’s called vbr. To use this utility, you first need to create a configuration file that includes information the utility needs to perform the backup.

Create this configuration file by using the following command:

=> /opt/vertica/bin/vbr –setupconfig

The script prompts you to answer several questions, as shown here:

$ /opt/vertica/bin/vbr –setupconfig
Snapshot name (backup_snapshot): fullbackup
Number of restore points (1): 3
Specify objects (no default):
Object restore mode (coexist, createOrReplace or create) (createOrReplace):
Vertica user name (dbadmin):
Save password to avoid runtime prompt? (n) [y/n]: y
Password to save in vbr config file (no default):
Node v_vmart_node0001
Backup host name (no default):
Backup directory (no default): /home/dbadmin/backups
Config file name (fullbackup.ini):
Password file name (no default value) (no default):pwdfile
Change advanced settings? (n) [y/n]: n
Saved vbr configuration to fullbackup.ini.
Saved vbr database password to pwdfile.ini.

Now that you have a configuration file, you?’re ready to perform a backup. For our example, let?’s say you want to run a full backup to save all of your data. Run the vbr utility, specify the backup task, and include the configuration file you created:

vbr –task backup –config fullbackup.ini

Overtime, you perform incremental backups to add to your original backup. Now you have a list of archived backups that include all your data.

Restore an object

Now let?’s say you want to restore a table from one of your backups. You only performed full backups, and you don’?t want to spend time restoring the whole thing. So how do you perform an object level backup?

To restore individual objects from the backup, use the same vbr utility that you used to create the backup.

Here we’?re restoring table public.customer_dimension from our most recent backup:

vbr –task=restore –config-file=fullbackup.ini –restore-objects=public.customer_dimension

How did I know we were restoring the most recent backup? Because I left out another option. The archive date. That’?s right, not only can you restore individual objects from a backup, you can specify which backup you want to restore the object from. Just add the archive date and time.

When Vertica performs an incremental backup, it appends _archivedate_time to the backup name. For example, suppose you ran an incremental backup on your full backup at exactly 5pm October 10, 2015. The name of that particular backup would look like this:


To restore the customer_dimension table from that particular backup, just run the vbr utility with the archive date:

vbr –task=restore –config-file=fullbackup.ini–archive=20151010_170000


You can perform these object level backups as many times as you wish, provided the object was included in the full backup. You can even restore more than one object with one command. Just add a comma:

vbr –task=restore –config-file=fullbackup.ini –restore-objects=public.customer_dimension, store.store_sales_fact

With this new Vertica feature, you no longer have to wait for full database restore when you just need to access one, or a few, objects from a specific backup. No more wading through the junk in the storage unit! You can now can better manage both your time and data.