Table restore from full backup?

DanL_1

Coming from an Oracle background, I am trying to understand the Vertica backup methodology. Full backups I think I understand, but not object level backups.

If all I had was a full backup of a database, how would I be able to use that to restore a single table? In Oracle if I lose a table (which unfortunately can happen in any DB), I can restore a subset of that database at a particular timestamp to a clone, export that single table from the clone, and import into the original - so I would not have to restore the entire database, just that one table.

Is anything like this available in Vertica? If I have a 3 node Vertica cluster, does that mean I would need to have 3 available servers (in a different domain, I guess, since they have to be the same name and IP) and the same amount of storage to thus build a duplicate of the entire cluster to eventually be able to restore that one table? Or would it be a better bet to just set up a second set of backups every day - one for full backups/incrementals and another set of backups for single table restores (one backup each for every single table in the DB)?

Stelios_Hadjist

Vertica documentation clearly states: "You cannot restore part of an object-level backup; you must restore the entire backup. For example, if you create an object-level backup containing two schemas,schema1 and schema2, and later want to restore schema1, you cannot do so without also restoring schema2. To restore a single object, you can create a single object backup." If you are worried about single table restores, a better policy is to have a DR cluster that it is an exact copy of production and use it to restore single tables. I would also keep object-level backups of "critical tables".

DanL_1

It looks like this is an enhancement request that has already been asked before:

https://community.vertica.com/vertica/topics/table_restore_from_full_backup


Having to do a single object backup for each of (currently) 250+ objects in the DB simply so I can have the ability to restore a single table out of a multi-terabyte DB is, to me, a major problem.

I know it *should* not happen, but if someone, while loading a 1 million row lookup table manages to hork it up, it should not mean that I have to restore TBs of data to get that single table back (no, we do not have a spare multi-TB capacity cluster lying around). Yes, this is a matter of access policy, but also highlights what is to me a significant Achilles heel for a database - something that has been in place for over two decades in other RDBMSs.

tharanga

This feature is avalable from 7.2.

 

https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/AdministratorsGuide/BackupRestore/RestoringIndividualObjectsfromaFullorPartialBackup.htm%3FTocPath%3DAdministrator's%2520Guide%7CBacking%2520Up%2520and%2520Restoring%2520the%2520Database%7C_____21