table restore

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.


  • Options
    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:
     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 !
  • Options
    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?

  • Options

    One of efficient ways its EXPORT TO VERTICA. Also try search of this forum.
  • Options
    Hi Yura!

    Everything is ok, vbr.py uses in 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')

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file