4

I am new to redshift. Currently, I am able to create a redshift cluster and connect it through SQL Workbench but I am looking forward to tunnel my redshift cluster doing ssh from my MAC terminal. I did some research and able to create an ec2 instance with same VPC ID and subnet group which I am using to create my Redshift cluster with. I have already installed psql on my ec2 instance as well. I am not able to understand where I am going wrong when I use psql command to connect to redshift :

psql -h my redshift endpoint -p 5439 -d database name -U user -c " my query "

it gives me error psql: could not translate host name "my redshift endpoint" to address: Name or service not known

DJo
  • 2,133
  • 4
  • 30
  • 46
Saurabh Singh
  • 61
  • 2
  • 7
  • Please show the command you are using to tunnel to the EC2 instance. It should involve using `ssh -L` to forward a remote port to a local port. – John Rotenstein Nov 19 '18 at 16:07
  • yes I am using here is the command "ssh -I "test.pem" -L 5439:redshiftendpoint:5439 ec2-user@ec2instance.compute.amazonaws.com" – Saurabh Singh Nov 19 '18 at 16:13

3 Answers3

5

The first step is to tunnel to the EC2 instance using ssh, with a command that forwards a local port to a remote port:

ssh -i KEYPAIR.pem -L 5439:REDSHIFT-ENDPOINT:5439 ec2-user@EC2-PUBLIC-IP

Where:

  • KEYPAIR.pem should be the name of the keypair used to access the EC2 instance
  • REDSHIFT-ENDPOINT is the DNS name of the Redshift endpoint
  • EC2-PUBLIC-IP is the IP address of the EC2 instance

This command says:

  • Create an ssh connection using the keypair
  • Forward any traffic sent to local port 5439 to the remote machine, then have the remote machine send that traffic to REDSHIFT-ENDPOINT:5439 (substitute your endpoint for REDSHIFT-ENDPOINT)

Then, you can connect to Redshift on localhost:5439 as if it were running on your own computer. That traffic will be sent to the remote machine, which will send it to REDSHIFT-ENDPOINT:5439.

For example, if you want to use psql to connect to Redshift, use:

psql -h localhost -p 5439 -U <username>
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • Do I have to change any setting in psql or ec2 instance. As I am still getting same issue with what you have suggested. Thanks. – Saurabh Singh Nov 19 '18 at 21:59
  • Please describe what happens when you perform each step, including how long it takes to respond. For example, when you run `ssh`, it probably will probably appear to hang. This is okay! You should then use a separate Terminal session for `psql`. I can't help you unless you provide more information about what is happening. – John Rotenstein Nov 19 '18 at 22:28
0

try psql -h localhost -p 5439 -d -U -c " my query " or psql -p 5439 -d -U -c " my query ", you can't use switch -h without an argument, which is localhost by default if you don't use it

AlexYes
  • 4,088
  • 2
  • 15
  • 23
0

Thanks John Rotenstein on giving me insight, I was actually missing the Inbound rule associated with my security group to only allow traffic coming from redshift private IP. Both of my ec2 instance and redshift were in the same VPC so was supposed to use private instead of public IP. Also forgot to put ssh public key of redshift in the ec2 authorized_key file. Once I did that it worked.

Saurabh Singh
  • 61
  • 2
  • 7