Skip to main content

Copying tables as a backup

  • April 14, 2016
  • 3 replies
  • 15 views
nvk9

Hi,

I understand there's a means to backup a table via vbr.py, but as far as I understand this script can be invoked only by the dbadmin.

We're looking at backing up our tables by creating a backup table using the CREATE TABLE statement along with the LIKE clause and then performing a COPY.  In this case, will all the data be copied over to the new table, or under the hood will there be only a rearrangement of pointers? We're trying to evaluate the space requirements associated with performing a full COPY of the table.

 

Thanks! 

    3 replies

    eli_revach
    Forum|alt.badge.img+2
    • eli_revach
    • Participating Frequently
    • April 14, 2016

    Hi ,

    Using this method you will have two separate objects with separate data ( full duplication ) 

    Thanks 

    nvk9
    • nvk9
    • Author
    • New Participant
    • April 14, 2016

    Perfect, thank you Eli!

    tharanga
    Forum|alt.badge.img
    • tharanga
    • Participating Frequently
    • September 29, 2016

    There is a much faster way to copy tables in Vertica 8.0.

     

    It's a simple meta-function, called copy_table. syntax is:

     

    =>select copy_table('source_table','dest_table');

     

    That's it. This is the fastest way to copy a table as a backup, no disk IO involved, 1000X faster than Create Table .. As Select..

     

    Disk space: no need to have additional disk space upfront, but as two tables diverge (deletes/updates/inserts/merge out), they will get their own storage.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement