CODEWITHSUNDEEP
amazon-web-servicesamazon-ec2amazon-redshiftssh-tunneltunnel
Saurabh Singh
Saurabh Singh

Reputation: 61

Tunneling to redshift cluster

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

Upvotes: 4

Views: 5907

Answers (3)

Saurabh Singh
Saurabh Singh

Reputation: 61

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.

Upvotes: 0

John Rotenstein
John Rotenstein

Reputation: 270224

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>

Upvotes: 5

AlexYes
AlexYes

Reputation: 4208

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

Upvotes: 0

Related Questions