PostgreSQL database on EC2: Connection refused

Question

I have my Django app and PostgreSQL database set up on two EC2 instances in the same VPC. App is on the instance with subnet connected to internet gateway; database is on instance with subnet that has no internet gateway.

The app instance's private IP is 10.0.0.164; the database instance's private IP is 10.0.1.136.

When I try to connect my Django app to the database, I get the error

could not connect to server: Connection refused
Is the server running on host "10.0.1.136" and accepting TCP/IP connections on port 5432?

However, I have allowed inbound TCP traffic on port 5432 on the database instance. My security group rules for the instance that hosts the database:

Inbound: allow all TCP and ICMP IPV4&IPV6 traffic in all ports from the internal IP address of the instance hosting the Django app (10.0.0.164/32)

(screenshot of my inbound rules https://i.sstatic.net/7ukjJ.jpg)

Outbound: allow all traffic in all ports to anywhere

My pg_hba.conf file on the database EC2 instance:

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

My postgresql.conf file has set listening address to '10.0.0.164, 127.0.0.1' and port to '5432'.

My database settings in Django's settings.py:

ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'articles',
            'USER': 'postgres',
            'PASSWORD': 'password',
            'HOST': '10.0.1.136',
            'PORT': '5432',

What else can I do to make the database instance accept connection?

EDIT: My EC2 instances are running Ubuntu 16.04

EDIT: this is what I got from running sudo lsof -nP -i | grep LISTEN on the database instance: postgres 1823 postgres 6u IPv4 19766 0t0 TCP 127.0.0.1:5432 (LISTEN)

I ran sudo ufw allow 5432 and still same error

When I ran netstat -nlt on the database instance, I don't see port 5432

Which Linux distro are you using? Make sure you allow the port 5432 on the Operating System Firewall. For example in Ubuntu you have to do sudo ufw allow 5432. — p14z
– p14z, Commented Mar 14, 2019 at 3:00
@Pedro Ubuntu 16.04. Thanks for reminding me to include that info! I will try that command — Ashley Liu
– Ashley Liu, Commented Mar 14, 2019 at 3:22
@Pedro Do you mean I have to do sudo ufw allow 5432 on the database instance or the app instance? — Ashley Liu
– Ashley Liu, Commented Mar 14, 2019 at 3:25
Just to be clear your database is an EC2 instance right? not RDS — p14z
– p14z, Commented Mar 14, 2019 at 3:27

Tms91 · Accepted Answer · 2024-03-07 02:05:03Z

5

postgres 1823 postgres    6u  IPv4  19766      0t0  TCP 127.0.0.1:5432 (LISTEN)

That's your issue right there, your postgres is bound to localhost only.

Change the IP that postgres is listening on by editing the /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/"Version number here"/main/postgresql.conf file and change the listen address as follows...

listen_addresses='127.0.0.1 10.0.1.136'

you must state listening addresses as I have without the commas in later versions of postgres

I hope this resolves your issue! :)

edited Mar 7, 2024 at 2:05

Tms91

4,40910 gold badges56 silver badges100 bronze badges

answered Mar 14, 2019 at 4:26

Tom

1168 bronze badges

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

3 Comments

Ashley Liu Over a year ago

yup that was the issue! thanks! just posted an answer that detailed how I resolved the issue

Tom Over a year ago

Glad I could help, I also noticed that you said the listening address was 10.0.1.164 in your question, I'm assuming that's the address of the Django app and not the database? That would probably be the reason why it was only listening on localhost :)

Aman Over a year ago

same problem occured to me. but I cannot find postgresql.conf file. in windows how can i find that file?

Ashley Liu · Accepted Answer · 2019-03-14 04:28:50Z

2

This page solved my issue: https://zaiste.net/postgresql_allow_remote_connections/

I ran sudo netstat -plunt |grep postgres and found that my Postgres is actually running on port 1823 (WHY). I edited my postgresql.conf to allow all listen_addresses (listen_addresses = '*') then changed my Django database settings to port 1823

answered Mar 14, 2019 at 4:28

Ashley Liu

4631 gold badge7 silver badges18 bronze badges

Comments

Mohd Sheeraz · Accepted Answer · 2019-03-14 04:19:29Z

0

As you mentioned that your DB is in EC2 instance, check the inbound rules of the instance. Saw the image of inbound rules. Source should be 0.0.0.0/0,::/0 instead on the instance ip

answered Mar 14, 2019 at 4:19

Mohd Sheeraz

3631 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

PostgreSQL database on EC2: Connection refused

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related