Backup and restore a single scheme with data to different single node

Vaibhav_Gupta · July 2014

I have 4 node prod vertica cluster with couple of schemes. Now I created a single node vertica for staging. Now I only want to take backup of one scheme with data from production and restore it to one node staging

Navin_C · July 2014

Hi Vaibhav,

You can take object level backup in Vertica, but I don't think they are compatible or they restore to a new database .

Instead, you can use the EXPORT to Vertica option to export data from Production cluster to Staging cluster.

Hope this helps.

Vaibhav_Gupta · July 2014

Thanks.!

I tried this after login to production:

vabs=> CONNECT TO VERTICA vabs USER dbadmin PASSWORD '*******' ON '*.*.*.*',5433;
CONNECT
vabs=> EXPORT TO VERTICA vabs.BigData.Country AS select * from vabs.BigData.Country;
ERROR 4040: Network address [] could no be resolved for node [v_vabs_node0001]
vabs=>

Navin_C · July 2014

Hi Vaibhav,

Did you follow these steps before exporting:

1. Create network interface for each node
2. Alter each node to have the export network interface created above
3. Have same structure of table on other cluster.
4. Connect database on different cluster with the export address set for that cluster ( for export address check the nodes table on that cluster)
5. Export using Export to Vertica options.

Let us know, if you have performed these steps.

Vaibhav_Gupta · July 2014

staging
node_name | node_id | node_state | node_address | export_address | catalog_path | is_ephemeral
---------------------+-------------------+------------+--------------+----------------+------------------------------------------------------------+--------------
v_vabs_node0001 | 45035996273704980 | UP | 10.0.1.127 | 10.0.1.127 | /data/vertica/vabs/v_vabs_node0001_catalog/Catalog | f

Prod

vabs=> select * from nodes;
node_name | node_id | node_state | node_address | export_address | catalog_path | is_ephemeral
---------------------+-------------------+------------+--------------+----------------+------------------------------------------------------------+--------------
v_vabs_node0001 | 45035996273704970 | UP | 10.0.1.121 | 10.0.1.121 | /data/vertica/vabs/v_vabs_node0001_catalog/Catalog | f
v_vabs_node0002 | 45035996415221126 | UP | 10.0.1.122 | 10.0.1.122 | /data/vertica/vabs/v_vabs_node0002_catalog/Catalog | f
v_vabs_node0003 | 45035996415221130 | UP | 10.0.1.123 | 10.0.1.123 | /data/vertica/vabs/v_vabs_node0003_catalog/Catalog | f
v_vabs_node0005 | 45035998153802294 | UP | 10.0.1.124 | 10.0.1.124 | /data/vertica/vabs/v_vabs_node0005_catalog/Catalog | f

I want to restore a schema from prod to staging

1. First I login to prod database(10.0.1.124)
/opt/vertica/bin/vsql -h 10.0.1.124 -U bigdatareadonly -w ******** -d vabs

2. I tried to connect to staging database node(10.0.1.124) by below command
vabs=> CONNECT TO VERTICA vabs USER dbadmin PASSWORD '*****' ON '10.0.1.127',5433;
CONNECT
vabs=> EXPORT TO VERTICA vabs.BigData.Country AS select * from BigData.Country;
ERROR 4126: No valid address found for subnet [10.1.1.0]

Navin_C · July 2014

Check your subnet ,if its configured.on production node

select * from network_interfaces

If its not configured, Create a new subnet with public IP on the production node 10.0.1.124.

CREATE SUBNET mysubnet PUBLIC_IP

Once done. then try again the export command.

Vaibhav_Gupta · July 2014

Sorry I'm new in vertica. This is my subnet table. 10.0.1.126 is my vertica loadbalancer ip.
Prod:

node_id | node_name | interface | ip_address | subnet | mask | broadcast_address -------------------+---------------------+-----------+------------+------------+-----------------+-------------------
45035996273704970 | v_vabs_node0001 | lo | 127.0.0.1 | 127.0.0.0 | 255.0.0.0 | 127.0.0.1
45035996273704970 | v_vabs_node0001 | lo:0 | 10.0.1.126 | 10.0.1.126 | 255.255.255.255 | 127.255.255.255
45035996273704970 | v_vabs_node0001 | eth0 | 10.0.1.121 | 10.0.1.0 | 255.255.255.0 | 10.0.1.255
45035996273704970 | v_vabs_node0001 | eth0 | 10.0.1.126 | 10.0.1.126 | 255.255.255.255 | 10.0.1.126
45035996415221130 | v_vabs_node0003 | lo | 127.0.0.1 | 127.0.0.0 | 255.0.0.0 | 127.0.0.1
45035996415221130 | v_vabs_node0003 | lo:0 | 10.0.1.126 | 10.0.1.126 | 255.255.255.255 | 127.255.255.255
45035996415221130 | v_vabs_node0003 | eth0 | 10.0.1.123 | 10.0.1.0 | 255.255.255.0 | 10.0.1.255
45035998153802294 | v_vabs_node0005 | lo | 127.0.0.1 | 127.0.0.0 | 255.0.0.0 | 127.0.0.1
45035998153802294 | v_vabs_node0005 | lo:0 | 10.0.1.126 | 10.0.1.126 | 255.255.255.255 | 127.255.255.255
45035998153802294 | v_vabs_node0005 | eth0 | 10.0.1.124 | 10.0.1.0 | 255.255.255.0 | 10.0.1.255
45035996415221126 | v_vabs_node0002 | lo | 127.0.0.1 | 127.0.0.0 | 255.0.0.0 | 127.0.0.1
45035996415221126 | v_vabs_node0002 | lo:0 | 10.0.1.126 | 10.0.1.126 | 255.255.255.255 | 127.255.255.255
45035996415221126 | v_vabs_node0002 | eth0 | 10.0.1.122 | 10.0.1.0 | 255.255.255.0 | 10.0.1.255

Stage:
vabs-> ; node_id | node_name | interface | ip_address | subnet | mask | broadcast_address
-------------------+---------------------+-----------+------------+-----------+---------------+-------------------
45035996273704980 | v_vabs_node0001 | lo | 127.0.0.1 | 127.0.0.0 | 255.0.0.0 | 127.0.0.1
45035996273704980 | v_vabs_node0001 | eth0 | 10.0.1.127 | 10.0.1.0 | 255.255.255.0 | 10.0.1.255

we don't have public ip in our vertica cluster.

Daniel_Leybovic · July 2014

Hi!

For large data sets EXPORT TO VERTICA works bad.
IMO, will be a better solution:

vsql -h prod -c "SELECT ..." | vsql -h dev -c "COPY tbl FROM STDIN"

You can apply compression on the fly:

vsql -h prod -c "SELECT ..." | gzip -f | vsql -h dev -c "COPY tbl FROM STDIN GZIP"

PS
If you will use in method that Navin suggested, so don't do COPY in one stream, split it with filtering, for example (EXPORT TO VERTICA works bad with large data set):

- WHERE id >= 1 and id <= 100000
- WHERE id >= 10000 and id <= 100000000
...

Also for better network throughput define CompressNetworkData, it will improve Network throughput

General Parameters

CompressNetworkData

0

When enabled (set to value 1), HP Vertica will compress all of the data it sends over the network. This speeds up network traffic at the expense of added CPU load. You can enable this if you find that the network is throttling your database performance.

Navin_C · July 2014

Excellent workaround,

Try creating a subnet with loadbalancer IP.

If this works, you can use public network for exporting data to your stage cluster.

Prasanta_Pal · July 2014

Hope you are all set if not, we can verify the output of the below from the source and target cluster:

select * from vs_network_interfaces;
select * from nodes;

[Deleted User] · July 2014

Interesting -- EXPORT TO VERTICA is, in my experience, at its best with large data sets. Where have you had trouble with it?

Is this specifically with large data sets and limited bandwidth between the two clusters? EXPORT TO VERTICA is optimized for high-bandwidth setups, so vsql + gzip (or bzip2, etc) can have a significant advantage over slow networks.

Daniel_Leybovic · July 2014

Hi!

@adam
>> Where have you had trouble with it?
Performance degrades, that's why Im recommending to bound it with filters. With small data set it works good.

>>EXPORT TO VERTICA is optimized for high-bandwidth setups
Hmm... I don't remember and unfortunately have no network configuration, but I think it was on Amazon. I will try to test on local network.

@all
Do not listen to me about EXPORT TO VERTICA, test it.
Actually its will be interesting to build a graph: NUM ROWS VS TIME

Backup and restore a single scheme with data to different single node

Comments

Leave a Comment