Lambda function unable to connect to Redshift : Temporary failure in name resolution

Question

I have an AWS Lambda function which is connected to a Jira webhook. Anytime an issue is created/changed/deleted, it sends a request to my API Gateway, which calls my function. I'm receiving the following traceback when I try to connect to Redshift inside the function:

Traceback (most recent call last):
File "/var/task/jira_webhook_proc.py", line 171, in lambda_handler
update_item(request, issue, issue_key)
File "/var/task/jira_webhook_proc.py", line 193, in update_item
delete_item(delete_key)
File "/var/task/jira_webhook_proc.py", line 277, in delete_item
port=REDSHIFT_PORT) as conn:
File "/var/task/psycopg2/__init__.py", line 164, in connect
conn = _connect(dsn, connection_factory=connection_factory, async=async)
OperationalError: could not translate host name "***" to address: Temporary failure in name resolution

I'm using a pre-compiled psycopg2 library that can be found here https://github.com/jkehler/awslambda-psycopg2. From googling it seems this might be a PostgreSQL error where I don't have a config file set right to listen to all ports. I'm not sure how I would change that however.

Did you place the Lambda function in the same VPC as the Redshift cluster? — Mark B
– Mark B, Commented Jan 6, 2017 at 20:17
Are you using a custom DNS name for the Redshift cluster, or just using the DNS name the Redshift console gives you? — Mark B
– Mark B, Commented Jan 6, 2017 at 20:36
I'm just using the * portion of the jdbc url from the console jdbc:redshift://****:5493/####. This is what I use when connecting with my other apps from the office. Then I tried using the cluster's leader node private IP. — flybonzai
– flybonzai, Commented Jan 6, 2017 at 21:20
The private IP is probably inaccessible to lambda. Try using the endpoint DNS given in the redshift console that ends something like this: your-cluster.us-east-1.redshift.amazonaws.com:5439. Also make sure you give your lambda function access to the subnet and security group: aws.amazon.com/blogs/aws/… — systemjack
– systemjack, Commented Jan 7, 2017 at 1:20

halil · Accepted Answer · 2017-01-11 08:27:15Z

1

I use this code added below to connect redshift from lambda function. Using psycopg2

conn_string = "dbname='your_db_name' port='5439' user='redshift_user' password='^%+^+&7!+' host='xxxxxxx.yyyyyyy.eu-west-1.redshift.amazonaws.com'"

conn = psycopg.connect(conn_string)

cursor = conn.cursor()

cursor.execute("COPY data_table ........ ")

conn.commit()
cursor.close()
conn.close()

answered Jan 11, 2017 at 8:27

halil

1,81216 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahan · Accepted Answer · 2020-02-14 11:42:42Z

It might help someone as it worked perfectly in my case.

import psycopg2
import od_red_load_cred as CONSTANTS

def main():

    # Amazon Redshift connect string 
    try:
        conn_string = "dbname={} port={} user={} password={} host={}"\
        .format(CONSTANTS.DBNAME, CONSTANTS.PORT, CONSTANTS.USER, 
            CONSTANTS.PASSWORD, CONSTANTS.HOST)

        print("Connect string Successful!")
    except:
        print("Unable to create the connect string")

    # query generate
    try:
        sql="""copy {}.{} from '{}'\
        credentials 'aws_access_key_id={};aws_secret_access_key={}' \
        DELIMITER ',' ACCEPTINVCHARS EMPTYASNULL ESCAPE COMPUPDATE OFF;commit;"""\
        .format(CONSTANTS.SCHEMA, CONSTANTS.TABLE_NAME_BV, CONSTANTS.S3_PATH_BV, 
            CONSTANTS.ACCESS_KEY_ID, CONSTANTS.SECRET_ACCESS_KEY)

        print("sql ==>", sql)
        print("Query creation Successful!")
        print(" ")
    except:
        print("Unable to create the query")

    # get connection
    try:
        con = psycopg2.connect(conn_string);
        print("con ==>", con)
        print("Connection Successful!")
    except Exception as e:
        print("Unable to connect to Redshift")
        print(e)
        exit(-1)

    # get cursor connection
    try:
        cur = con.cursor()
    except Exception as e:
        print("Unable to get the cursor from connection of Redshift")
        print(e)
        exit(-1)

    # execute cursor connection
    try:
        cur.execute(sql)
        print("Query executed successfully")
    except Exception as e:
        print("Failed to execute the query")
        print(e)
        exit(-1)

    # close connection
    try:
        con.close() 
        print("Connection closed Successfully!")
    except Exception as e:
        print("Unable to close connection to Redshift")
        print(e)

    print(" ")

# Main method
if __name__ == '__main__':
    print("start: __main__ ")
    main()
    print("end: __main__ ")

Collectives™ on Stack Overflow

Lambda function unable to connect to Redshift : Temporary failure in name resolution

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related