table restore

Yuriy_T

I had two tables partitioned by month which I backed up to disk using vbr.py utility. Backup run for ~6 hours. I then decided to check if my backup worked by renaming original two table names by appending _orig suffix to them and then run a restore using vbr.py with the same config file which I used to make a backup. My expectation was that restore will run for 6 hours or more and two new tables with old names will be added to the database and I will have both renamed tables and restored tables with the old names in the database. Instead, restore took less then a minute and tables with _orig suffix has been renamed back to their original names. No new two tables have appeared. It appears that simple tables rename will not be sufficient to validate a table restored from a backup. Essentially, I want to be able to have duplicate tables (one original and same table restored from a backup) side by side to validate that my backup worked properly. How can I accomplish this? I do not plan to do this kind of validation all the time, but since I am still learning about Vertica, I want to be able not to lose data while discovering how it works.

Adrian_Oprea_1

You can always use export_objects(); function to dump you table DML into a sql file and then restore it  with the name you want. Next dump you data using the piped vsql commnad:
Example:

 vsql -U username -w passwd -F $'|' -At -c "SELECT * FROM table_name" | gzip -c > '/bkp_place/backup_table.gz'  

I don't know if this might be an option for you but in some cases is does the job for me !

kushal_alester

Hi Adrian,

We can user EXPORT_OBEJCTS(''); to create a file with DDLs. right?
Can you please help, how table data could be moved from "one Database to Other" ?
I mean to say- if the Source and Target DB has same no of tables and also the similar table structure.
How all table data could be moved from Source to Target DB efficiently?
TIA

Regards,
Kushal

Daniel_Leybovic

Hi!

One of efficient ways its EXPORT TO VERTICA. Also try search of this forum.

Daniel_Leybovic

Hi Yura!

Everything is ok, vbr.py uses in rsync.
rsync validates a check sum of files and do not copies/removes/replaces if check sum match (on this based an incremental backup).
Catalog - is a separate file from data, that describes objects dependencies. You changed a Catalog, but data isn't changed, so restore done only on Catalog and not on data.

IMO -  vbr.py did a good work for you.

>> Essentially, I want to be able to have duplicate tables
Try create additional table, after it move data with move partition function and do a restore:

create table TblCopy like TblOrig including projections

select move_partition_to_table('TBlOrig', 'min partition', 'max partition', 'TblCopy')