Table restore from full backup?
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)?
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)?
0
Comments
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.
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