Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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?

Answers

  • SruthiASruthiA Employee

    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.

  • SruthiASruthiA Employee

    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

  • SruthiASruthiA Employee

    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?

  • SruthiASruthiA Employee

    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 ... ;)

  • SruthiASruthiA Employee

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
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.