1

I tried to connect Amazon Redshift to my local computer using pycopg2. However, I got an error message:

psycopg2.OperationalError: could not connect to server: Operation timed out. Is the server running on host xxx and accepting TCP/IP connecitons on posrt 5439

I have done two guides with searching google:

  1. Changed the Publicly Accessible setting as enable, and
  2. Add 0.0.0.0/0 and ::/0 to VPC route as gateway.

It still doesn't work. Please let me know if you know what the problem is.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
kiyeonj
  • 25
  • 6

1 Answers1

1

Things to check:

  • Check the Security Group associated with the Redshift cluster and confirm that it permits access on port 5439 from your IP address
  • Check that the Redshift cluster was launched in a Public Subnet (with the Route Table for that subnet pointing to 0.0.0.0/0 to the Internet Gateway)
  • Make sure you are connecting by using the DNS Name (If you ping the DNS Name, does it resolve to an IP address?)
  • Try going via a different network (eg home vs office vs tethered via your phone)?
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • 1. How can I check the Redshift cluster was launched in the Public Subnet? I just added one route from `0.0.0.0/0` to gateway in route tables. 2. I did `ping -a `. It only showed "Request timedout for icmp_seq x" 3. I tested it with env tethered via my phone. – kiyeonj Aug 31 '21 at 09:07
  • Ping won't work, but it _will_ show you the IP address that the DNS name resolves to, so you can test that the DNS name is being translated into an IP address. To check if it is in a Public Subnet, find the subnet it is in, find the Route Table associated with that subnet, then confirm that there is a Route to the NAT Gateway for destination `0.0.0.0/0`. – John Rotenstein Aug 31 '21 at 09:38
  • The most likely problem is always the **Security Group**. Do you have one associated with the instance, permitting access from your IP address? – John Rotenstein Aug 31 '21 at 09:40
  • I got an IP address from the failed ping results. I am not sure how to check if there is a route to gateway. In my case, there are four subnets - `xx.yy.0.0/20`, `xx.yy.16.0/20`, `xx.yy.32.0/20`, `xx.yy.48.0/20`, and the DNS's IP is `xx.yy.60.45`. For the Security Group, I just added `0.0.0.0/0` (Redshift type) as inbound rules to default setting. I am not clear that I have an instance permitting access. – kiyeonj Aug 31 '21 at 10:23
  • In the VPC Console, select the Subnet that holds your Redshift cluster. In the Route Table panel at the bottom, look at the Routes. It should show `0.0.0.0/0` pointing to an ID starting with `igw-`. – John Rotenstein Aug 31 '21 at 11:25
  • Thanks, but it still doesn't work.. I'll skip it due to time – kiyeonj Sep 01 '21 at 02:58
  • I resolved my issue by following [your previous answer](https://stackoverflow.com/questions/58399974/redshift-not-connecting-to-host-via-python-script) again. (I didn't make a new vpc because it is not possible to change vpc in the free-tier mode. I just set up new vpc security group) – kiyeonj Sep 04 '21 at 08:55