PostgreSQL database on EC2: Connection refused

install postgres on ec2
postgres connection refused
cannot connect to aws rds postgres
is the server running on host and accepting tcp/ip connections on port 5432?
pgadmin connect to aws rds
ec2 install postgresql server
postgres ami
ec2 console

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

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

Connecting to a DB Instance Running the PostgreSQL Database , to server: Connection timed out. or Amazon EC2 instance where the application is running. default database name postgres for the --dbname option . $ psql -h 107.170.158.89 -U postgres psql: could not connect to server: Connection refused Is the server running on host "107.170.158.89" and accepting TCP/IP connections on port 5432? In order to fix it, open pg_hba.conf and add following entry at the very end.

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

Cannot remotely access PostgreSQL on EC2 instance, psql -h *EC2ExternalIP* -U *me* psql: could not connect to server: Operation timed out Is the server running on host *EC2ExternalIP* and accepting TCP/IP� By default, the PostgreSQL server only allows connections to the database from the local machine or localhost. This is a security feature. Step # 1: Allow remote IP address to access PostgreSQL

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

Connect to Postgresql database from another EC2 instance, # psql -h [redash-ec2-instance-private-ip-address-here] psql: could not connect to server: Connection refused Is the server running on host "[� This will create a security group that will allow connection from the IP address of the device that you are currently using to the database created. Database options. Database name: Type a database name that is 1 to 64 alphanumeric characters. If you do not provide a name, Amazon RDS will not automatically create a database on the DB instance

Creating a PostgreSQL DB Instance and Connecting to a Database , The most common error is could not connect to server: Connection timed out. If you receive this error, check that the host name is the DB instance endpoint and� Following are two ways to connect to a PostgreSQL DB instance. The first example uses pgAdmin, a popular open-source administration and development tool for PostgreSQL. The second example uses psql, a command line utility that is part of a PostgreSQL installation.

Unable to connect to a remote PostgreSQL server: Could not , Could not connect to server: connection refused. Cause. By default, PostgreSQL server allows connection from localhost only. Verify your connection by running one of the following commands: telnet <RDS endpoint> <port number> nc <RDS endpoint> <port number> If either the telnet or nc commands succeed, then a network connection was established, and the issue is likely caused by the user authentication to the database, such as user name and password.

How can I use port tunneling to connect to a private database , -U postgres psql: could not connect to server: Connection refused Is the server running on host You do not need to create another SSH connection. So, locally , you ec2-user@example.compute-1.amazonaws.com. Assuming postgres. example.us-east-1.rds.amazonaws.com resolves to the private IP address. Then to� Creating a PostgreSQL DB Instance and Connecting to a Database on a PostgreSQL DB Instance The easiest way to create a DB instance is to use the RDS console. After you have created the DB instance, you can use standard SQL client utilities to connect to the DB instance, such as the pgAdmin utility.

Comments
  • 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.
  • @Pedro Ubuntu 16.04. Thanks for reminding me to include that info! I will try that command
  • @Pedro Do you mean I have to do sudo ufw allow 5432 on the database instance or the app instance?
  • Just to be clear your database is an EC2 instance right? not RDS
  • @Pedro yes EC2, not RDS
  • yup that was the issue! thanks! just posted an answer that detailed how I resolved the issue
  • 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 :)