I know that I need to create a subnet or a network interface to get this to work. I would rather use something like COPY FROM VERTICA than outputting to CSVs and re-COPYing them to another Vertica cluster. However, when the Vertica databases (we are trying to automate a "test" environment) are on different subnets, COPY FROM VERTICA does not work.
You are correct in saying that you need to create a subnet OR network interfaces to get this to work. This is specifically the case when the private IPs of both clusters can't communicate with each other. We must define the IPs or Subnet which the copy can use to open connection with the other Vertica cluster.
Have you been able to successfully make the change as outlined in the doc to get COPY FROM VERTICA working?
> create network interface <interface_name> on v_xxxxxx_node0001 with '<IP>';
For v_xxxxxx_node0001, xxxxxx represents the Vertica database node I'm trying to connect to?
Also, is it necessary to create network interfaces on both Vertica instances in order to get COPY FROM VERTICA to work for Vertica databases on different subnets?
For v_xxxxxx_node0001, xxxxxx represents the Vertica database node I'm trying to connect to? - Yes, for each node you do this, this is node name, you can get the node names select * from nodes; yes: do this for each node on both the clusters
I am still very confused, as I get "No valid address found for [production_node_export_address] on this node". Can you please give an example of how to get COPY FROM VERTICA to work? Specifically, I am interested in what to do on "the cluster where the data resides" and what to do on "the cluster where I am trying to copy data to".
We have one network which we use for testing purposes, and another network which we use for production machines, and the production network cannot talk to the internal network, but the internal network can access the external network?
Comments
https://my.vertica.com/docs/6.1.x/HTML/index.htm#18308.htm
You are correct in saying that you need to create a subnet OR network interfaces to get this to work. This is specifically the case when the private IPs of both clusters can't communicate with each other. We must define the IPs or Subnet which the copy can use to open connection with the other Vertica cluster.
Have you been able to successfully make the change as outlined in the doc to get COPY FROM VERTICA working?
- Mitch
create network interface <interface_name> on v_xxxxxx_node0001 with '<IP>';
alter node v_xxxx_node0001 export on <interface_name>;
check the interfaces:
select * from vs_network_interfaces;
In your example,
> create network interface <interface_name> on v_xxxxxx_node0001 with '<IP>';
For v_xxxxxx_node0001, xxxxxx represents the Vertica database node I'm trying to connect to?
Also, is it necessary to create network interfaces on both Vertica instances in order to get COPY FROM VERTICA to work for Vertica databases on different subnets?
you can get the node names
select * from nodes;
yes: do this for each node on both the clusters
We have one network which we use for testing purposes, and another network which we use for production machines, and the production network cannot talk to the internal network, but the internal network can access the external network?
select * from nodes;
select * from vs_network_interfaces;
------
To create interface and set export address as below on all the nodes ( source and destination)
create network <interface_name> on <node_name> with '<public_ip_node>';
ALTER NODE <node_name> EXPORT ON "<interface_name>";
--------------------------------------------------------------------------
dbadmin=> select * from vs_network_interfaces;
oid | name | schema | node | address
-------------------+-------------+--------+-------------------+---------------
49539596333614568 | DFExporter1 | 0 | 45035996273704972 | 10.90.128.164
49539596333616022 | DFExporter2 | 0 | 45035996273719654 | 10.90.128.165
49539596333616024 | DFExporter3 | 0 | 45035996273719658 | 10.90.128.166
49539596333616026 | DFExporter4 | 0 | 45035996792604504 | 10.90.128.58
49539596333616028 | DFExporter5 | 0 | 45035996805467832 | 10.90.128.50
(5 rows)
dbadmin=> select * from nodes;
node_name | node_id | node_state | node_address | export_address | catalog_path | is_ephemeral
----------------------+-------------------+------------+--------------+----------------+------------------------------------------------------+--------------
v_analytics_node0001 | 45035996273704972 | UP | 10.90.141.11 | 10.90.128.164 | /data/analytics/v_analytics_node0001_catalog/Catalog | f
v_analytics_node0002 | 45035996273719654 | UP | 10.90.141.12 | 10.90.128.165 | /data/analytics/v_analytics_node0002_catalog/Catalog | f
v_analytics_node0003 | 45035996273719658 | UP | 10.90.141.13 | 10.90.128.166 | /data/analytics/v_analytics_node0003_catalog/Catalog | f
v_analytics_node0004 | 45035996792604504 | UP | 10.90.141.14 | 10.90.128.58 | /data/analytics/v_analytics_node0004_catalog/Catalog | f
v_analytics_node0005 | 45035996805467832 | UP | 10.90.141.15 | 10.90.128.50 | /data/analytics/v_analytics_node0005_catalog/Catalog | f
(5 rows)
"The cluster where I am trying to copy data to"
--------------------------------------------------------------------------
dbadmin=> select * from vs_network_interfaces;
oid | name | schema | node | address
-----+------+--------+------+---------
(0 rows)
dbadmin=> select * from nodes;
node_name | node_id | node_state | node_address | export_address | catalog_path | is_ephemeral
----------------------+-------------------+------------+--------------+----------------+------------------------------------------------------+--------------
v_analytics_node0001 | 45035996273704980 | UP | 192.168.7.56 | 192.168.7.56 | /data/analytics/v_analytics_node0001_catalog/Catalog | f
v_analytics_node0002 | 45035996273719008 | UP | 192.168.7.58 | 192.168.7.58 | /data/analytics/v_analytics_node0002_catalog/Catalog | f
(2 rows)
node_address = 10.90.141.x and export_address =10.90.128.x
For the destination:
192.168.7.x
which are public address? and which are private?
Are 10.90.141.x or 10.90.128.x accessible by 192.168.7.x?
The 10.90.141.* and 192.168.* addresses are private. The 10.90.128.* addresses are public.
> Are 10.90.141.x or 10.90.128.x accessible by 192.168.7.x?
yes
Hi David,
I am facing the same issue.
Even after creating the new network interfaces, the problem did not solve.
Did you find any solution for that?