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

Answer 1

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