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)?
Table restore from full backup?
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".
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.
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.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
