Tunneling to redshift cluster

Question

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

Answer 1

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>