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://imgur.com/a/HNbrIDm)

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
@Pedro Ubuntu 16.04. Thanks for reminding me to include that info! I will try that command — Ashley Liu
@Pedro Do you mean I have to do sudo ufw allow 5432 on the database instance or the app instance? — Ashley Liu
Just to be clear your database is an EC2 instance right? not RDS — p14z

Tom Tom · Accepted Answer · 2019-03-14T04:26:38

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! :)

PostgreSQL database on EC2: Connection refused

3 Answers