The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Synchronize table across different clusters.

I have a table (t1) on cluster A and this needs to available as t1 on cluster B.
I am exploring multiple options to perform this activity.
Option#1 : Use "COPY FROM VERTICA"
Option#2: Load data parallel to t1 on cluster A and t1 on cluster B.
note: cluster A and cluster b has different vertica versions and different no of nodes.

do you see other options ?


  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited October 2017


    Option #2 might be easier, unless you have to make a lot of modifications to your ETL process or applications.

    Do you plan on copying the entire table every time, or just the new records? If it's the new records, I wonder if the "EXPORT TO VERTICA" command is better as you can specify a query. That is, on cluster A you could have a little script run periodically to update the remote table. Something like this:

    CONNECT TO VERTICA clusterB_db USER dbadmin PASSWORD '' ON 'clusterB', 5433;
    EXPORT TO VERTICA clusterB.t1 AS SELECT * FROM t1 WHERE create_date > (select last_exported date from my_export_config);
    DISCONNECT lusterB_db;
    UPDATE my_export_config SET last_exported date = sysdate;

    That's assuming you have a "create_date" column on the t1 table. You could also use a sequence, increment or the row epoch as indicators of which rows were already exported.


    You can also script a copy using vsql... Say you want to truncate the table t1 on cluster B then copy all the records from t1 on cluster A to B:

    On cluster B you could run in a shell script periodically:

    vsql -h clusterB -c "truncate table t1;"
    vsql -h clusterA -Atc "select * from t1;" | vsql -h clusterB -Atc "copy t1 from stdin;"
  • @Jim_Knicely awesome . thank you so much !

Leave a Comment

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