copy from vertica over ssh tunnel
I have a three node cluster and I want to copy the data from that cluster to a single node vertica instance. I only have ssh access between the databases, so if I wanted to use the copy from vertica command, I have to setup port forwarding. I can then connect from the single node instance to one of the cluster nodes using local forwarding, but when I issue the copy from vertica command, it says connection timed out. On the cluster, it says (vertica.log) that it timed out to port 5433.
This makes me wonder what communications are taking place, and if it is even possible to copy the data if the normal communication ports aren't open. Any thoughts?
0
Answers
Did you run connect to vertica before running copy from vertica?
https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/DataExport/CopyExportData/CopyingData.htm
Yes. Basically following the instructions as listed in the manual. If I have no firewalls between my databases, this works fine. But now I need to setup port forwarding. And either I'm not doing that correctly, or it just can't be done.
And the vertica version is 10.1, both source and target. And one correction, it times out on 5434.
could you please share me the output of connect to vertica and copy from vertica with exact messages displayed on the screen?
dbadmin=> connect to vertica ksdsspv1 user dbadmin password '***' on '127.0.0.1',4433;
CONNECT
dbadmin=> copy appdba.runstats from vertica ksdsspv1.appdba.runstats no commit;
ERROR 4054: [ksdsspv1] NetworkSend on v_ksdsspv1_node0001: failed to open connection to node v_ksdssmvc_node0001 (socket error: Connection timed out)
dbadmin=> \q
I observe couple of issues here...
connect to vertica ksdsspv1 user dbadmin password '***' on '127.0.0.1',4433;
1) why are you using 127.0.0.1, localhost IP address? you need to use hostname of the source or IPV4 address of the node in source cluster
2) why is port number 4433? it has to be 5433 or did you change the default vsql port in source cluster to 4433? did vsql work on source cluster with this port number?
That is the whole point of my question. I am using ssh port forwarding. 4433 will be forwarded to 5433 on the target host (tunnelling over port 22), and a few other ports are also forwarded. This works fine. Because firewall will block port 5433. And it will take weeks to open that port, so port forwarding would be a quicker way. And yes I can run vsql fine, but copy from vertica doesn't work. How to set up port forwarding so that it will work. Or is it not possible?
I think issue requires webEx and reviewing logs to see where it is getting stuck. Could you please open a support case?
Will do. And maybe someone has done this and knows how to tweak this ...
I understand why you are using port 4433. However you should not use 127.0.0.1 as the IP.. How is it getting converting to the source node IP address when running connect to vertica in target database?
I am not sure what communication takes places, but I see in the logs that the remote database tries to open a connection to the correct IP and port: 30.201.186.127:5434. However, this hasn't been tunneled from the remote end and will fail. I guess the answer is that it can't be done.