Cannot connect remotely to postgresql database hosted on amazon ec2 instance

Question

I have a postgresql database hosted on my ec2 instance. I am trying to connect to it using Python from my local computer.

import psycopg2

try:
 connection = psycopg2.connect(user="postgres",
                               password="<password>",
                               host="ec2-***-***-***-***.***-***-1.compute.amazonaws.com",
                               port="5432",
                               database="<db_name>")

 cursor = connection.cursor()

 cursor.execute(f"SELECT * FROM <tablename>;")
 record = cursor.fetchall()
 print(record, "\n")

except (Exception, psycopg2.Error) as error:
 print("Error while connecting to PostgreSQL", error)
finally:
 try:
     if connection:
         cursor.close()
         connection.close()
         print("PostgreSQL connection is closed")
 except:
     print("no work")

But I get

Error while connecting to PostgreSQL could not connect to server: Connection timed out (0x0000274C/10060)
    Is the server running on host "ec2-***-***-***-***.***-***-1.compute.amazonaws.com" (**.**.**.***) and accepting
    TCP/IP connections on port 5432?

no work

My pg_hba.conf file looks like this

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

and my postgresql.conf file looks like

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

I have an ec2 Security Group: What am I doing wrong? I am a complete newbie, any help appreciated!

Mike Organek · Accepted Answer · 2020-06-25 23:21:39Z

4

Connection timeout usually indicates that you cannot reach the host or are being blocked by a firewall.

First try to ping the host from your machine. If it pings all right, then there is probably a firewall between you and your EC2 instance. Your EC2 security screenshot looks right to me.

Are you behind a firewall that might be blocking outbound sessions from your local computer to the Internet?

After some troubleshooting over chat, we found that the AWS Security Group allowing TCP/5432 inbound was not assigned to the EC2 instance.

edited Jun 25, 2020 at 23:21

answered Jun 25, 2020 at 18:51

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

7 Comments

MadMan47 Over a year ago

I tried pinging it ping ec2-***-***-***-***.***-***-1.compute.amazonaws.com and it says Request timed out.

Mike Organek Over a year ago

Are you able to make an ssh connection to that host from where you are? Please determine whether you can reach the server before troubleshooting the database connection to it.

MadMan47 Over a year ago

I am able to connect to the instance via SSH using PuTTY with the ec2 key pairs thing.

Mike Organek Over a year ago

Ok. Are you using the same DNA name? Does it resolve to the same IP address that your PuTTY session uses? If so, then are you in control of your local network, or are you at work?

Mike Organek Over a year ago

Let us continue this discussion in chat.

|

Curt Evans · Accepted Answer · 2020-06-25 19:09:31Z

1

There are many possible causes, so I would start with a trusted DB client like DBeaver and attempt to make the connection from your local machine to rule out python issues.

Depending on your setup, you may have a second incoming firewall (iptables, etc) running inside your ec2 instance that needs to be configured or disabled.

Log into the ec2 console and see if you can connect to the server. Load a db client like pgsql inside ec2 and attempt to connect to the server with localhost:5432 as the target.

You may need to alter pg_hba.conf so that the server will generate log files, but that can tell you if the server is being reached, and what the problem is.

answered Jun 25, 2020 at 19:09

Curt Evans

3462 silver badges6 bronze badges

1 Comment

MadMan47 Over a year ago

I can access the db from the ec2 instance just not remotely. I tried using pgAdmin but it also said "timeout expired". I will try with the log files, thanks.

Collectives™ on Stack Overflow

Cannot connect remotely to postgresql database hosted on amazon ec2 instance

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related