Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

can't connect to redshift database

I'm trying to connect to Redshift database from SQL Workbench/J using Postgre JDBC drivers but I can't get through. I get this error "The connection attempt failed". The jdbc driver is properly located. I also tried through Aginity Workbench for Redshift but it was unable to show the list of databases. I have my connection url to the database of the form jdbc:postgresql://host:port/name_of_database but surprisingly I could not even ping the host. I can see that the health of database is good through amazon console. So, my questions are:

  1. Why can't I ping my redshift db server?
  2. Is there a way to ssh to the db server?

Edit1: After adding my public IP to security group the host gets resolved while pinging but still there's 100% packet loss.

Edit2: I could successfully host the db in EC2-Classic but not in EC2-VPC. Since I'm running short of public IPs I tried re-hosting the db in VPC where there is 100% packet loss.

like image 881
chemicalkt Avatar asked Nov 07 '13 17:11

chemicalkt


People also ask

How do I connect to Redshift from terminal?

To connect by using psql defaults Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/ . Where: <endpoint> is the Endpoint you recorded in the previous step. <userid> is a user ID with permissions to connect to the cluster.


2 Answers

You have to add your IP address block into the security group for the cluster.

This is separate from other security groups (i.e. VPC).

You can find it on the left in the Redshift Console. https://console.aws.amazon.com/redshift/

like image 180
Joe Harris Avatar answered Sep 25 '22 13:09

Joe Harris


I was attempting to connect from my local machine via psql to a Redshift cluster in a VPC. I found I needed to explicitly add my local IP/CIDR to the inbound rules for my security group.

You can find the menu in the AWS console at Services>VPC>Security Groups. Then click to edit your security group, and you should see the inbound rules tab at the bottom of the page. Example:


inbound rules menu example

I was unfamiliar with CIDR prior to this, but 32 seems to be the default CIDR. In my case, I added an inbound rule to allow traffic from xx.xxx.xxx.xx/32 - xx.xxx.xxx.xx being my local IP address.

like image 22
Mario Olivio Flores Avatar answered Sep 21 '22 13:09

Mario Olivio Flores