By using AWS re:Post, you agree to the Terms of Use

Connecting to Redshift with Python - Operation Timed Out

0

Hi

Hoping to get some assistance. I'm new to AWS and Redshift, and having some trouble connecting to Redshift using Python.

I've been using the below code (I've added X's to things that I think should be private) and running it in Python.

import psycopg2
con = psycopg2.connect(
                    dbname = "dev", 
                     host= 'reddit-redshift-cluster.XXXXXXXX.eu-west-2.redshift.amazonaws.com', 
                     port= "5439", 
                     user= "awsuser", 
                     password= "XXXXXXX")

This is the error I'm getting outputted:

psycopg2.OperationalError: connection to server at "reddit-redshift-cluster.XXXXXXXXX.eu-west-2.redshift.amazonaws.com" (X.X.X.X), port 5439 failed: Operation timed out
        Is the server running on that host and accepting TCP/IP connections?

I've tried suggestions online. For example, I've modified publicly accessible setting to Enabled. I've also played around with the Inbound and Outbound rules for the VPC security group (although truthfully, I'm not sure how all this works).

Nothing seems to work.

Should also note that I set up my cluster with the free trial option, if that changes anything.

asked 7 months ago543 views
2 Answers
0

To add to the information and the link that Fabrizio have posted: Redshift is designed to be accessible from a VPC, and while tunneling to an EC2 instance from your local machine is possible, this isn't really that straight forward (and also adds latency between you and the cloud).
It will be more convenient if you work directly from an EC2 instance instead of the local machine - by this I mean running your python code there and not locally.

Hope this was useful. Regards

answered 7 months ago
EXPERT
reviewed 7 months ago
  • Thank you for the addition.

    Probably Looking at Glue Python shell might help to avoid EC2 costs.

  • Thank you. I think my issue was to do without inbound routing (something I don't know much about). I'll need to do more research into this. Although I do plan on setting up an EC2 instance. I have some familiarity with setting up a VM in Google Cloud. I'm guessing EC2 is a similar idea.

0

Hi,

from the error it seems that you cannot still reach the Amazon Redshift cluster.

Could you please try to clarify:

  1. where are you running the Python job? (from inside the cloud or from your laptop?)
  2. who deployed the cluster? Can you clarify if it run in a public or private subnet? (has the subnet access to the internet or no)
  3. how have you modified the security groups?

In case you are running from your local laptop and the Redshift cluster is in a private subnet , you could try to review the this knowledge base article.

hope this helps

EXPERT
answered 7 months ago
  • Hi,

    1. From my local machine running a basic Python script. I use a Mac M1.

    2. It was me that deployed the cluster. I just did it via the AWS UI following the default options, except I specified that I wanted the 2 month free trial. I'm not sure if it's private or public, I think public though. Is there a way to find this out?

    3. I ended up making a lot of changes to the VPC Security Group, adding a bunch of inbound rules.

    I can try following the instructions you linked. I was maybe going to delete my current cluster and try again (I have no data in it as I just only set it up today so I could practice moving data between s3 and redshift).

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions