How to Move/Copy Partition across cluster?

Vertica has the great utility on Move/copy partition and same as copy table but they work in the same cluster.Is there any way i can move/Copy the partitions across cluster with the same DDL of table.




  • If the target cluster is identically configured (i.e. same number of nodes), you could try and see if the following works:

    • In the source cluster, use COPY_PARTITIONS_TO_TABLE() to copy the paritions to a new (temporary) table.
    • Use VBR Replicate to replicate the object (in this case, the new table with copied partitions) across to an identically configured alternate cluster.
    • In the target cluster, use MOVE_PARTITIONS_TO_TABLE() to move copied partitions from the temporary table to the target table.
  • Hi Lenoy,Thanks for replying.I want to use the COPY_PARTITIONS_TO_TABLE() and MOVE_PARTITIONS_TO_TABLE() and now i don't want to use the vbr.but the problem is i have to copy the partitions across cluster.let's assume if i have a development cluster and i have created an empty table with same structure in test i want to copy partitions from dev cluster to test cluster. The answer you gave will work if the source and target in same cluster.

  • LenoyJLenoyJ Employee
    edited September 2019

    VBR Replicate is intended exactly for the example you've mentioned. If your dev and test clusters have the same number of nodes, you can follow my previous post step by step. Any reason why you think it won't work? Quoting from our docs here:

    One reason you might use object replication is to copy tables and schemas between test, staging, and production clusters.

    To reiterate - In your dev cluster, copy partitions you want to a table. Then perform vbr replicate of this table to the test cluster. Then in your test cluster, move the partitions in the replicated table to your target table. Done! Script it out if needed. The reason why we're doing this is because vbr can replicate tables and schemas but not partitions (AFAIK). :-)
    Now, one caveat with vbr replicate is that you need your dev cluster to have the same number of nodes as your test cluster. If this is not the case, you can use COPY FROM VERTICA or EXPORT TO VERTICA to copy data between dissimilar clusters. This is slower than using VBR Replicate (if you're moving really large tables, that is). See the following docs & blog post on how:

  • Hi Lenoy,This is the reason i don't want vbr as the no of nodes in both cluster is not same.And also if we go with Export utility it will take a lot of time, so thought if we could do with moving partitions.Anyway i got answer that it can not be done

  • Did you try to see how long it takes to copy one dose of records at a time?
    For example:
    CONNECT TO VERTICA my_db USER dbadmin PASSWORD 'mypass' ON 'myhost', 5433;
    COPY my_small_dimension FROM VERTICA mydb.my_small_dimension DIRECT;
    DISCONNECT my_db;

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.