I have a simple PostgreSQL DB running on an EC2 instance.
ubuntu@ip-172-31-38-xx:~$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2020-06-19 14:04:12 UTC; 7h ago
Main PID: 11065 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 1152)
CGroup: /system.slice/postgresql.service
Jun 19 14:04:12 ip-172-31-38-xx systemd[1]: Starting PostgreSQL RDBMS...
Jun 19 14:04:12 ip-172-31-38-xx systemd[1]: Started PostgreSQL RDBMS.
ubuntu@ip-172-31-38-xx:~$ psql -U postgres
Password for user postgres:
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
postgres=# SELECT *
postgres-# FROM pg_settings
postgres-# WHERE name = 'port';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
------+---------+------+------------------------------------------------------+------------------------------------------+------------+------------+---------+--------------------+---------+---------+----------+----------+-----------+-----------------------------------------+------------+-----------------
port | 5432 | | Connections and Authentication / Connection Settings | Sets the TCP port the server listens on. | | postmaster | integer | configuration file | 1 | 65535 | | 5432 | 5432 | /etc/postgresql/10/main/postgresql.conf | 63 | f
(1 row)
The only Security Group that is associated with this EC2 instance has inbound rules wide open:
5432, TCP, 0.0.0.0/0
But when I use a client to connect to this DB with the correct hostname (public IP/DNS), port number, DB name, user name and password typed in, it always says:
could not connect to server: Connection refused, is the server running on host "ec2-dns.com(172.public.ip)" and accepting TCP/IP connections on port 5432?
All right, I've figured it out from this answer
Two things I did to enable myself to connect (exactly from the link above, I'm duplicating it here for convenience):
sudo vi /etc/postgresql/10/main/pg_hba.confimmediately below this line:
host all all 127.0.0.1/32 md5
added this line:
host all all 0.0.0.0/0 md5
sudo vi /etc/postgresql/10/main/postgresql.conffind a line that starts with this:
#listen_addresses = 'localhost'
Uncomment the line by deleting the #, and change 'localhost' to '*'.
The line should now look like this:
listen_addresses = '*' # what IP address(es) to listen on;.
then restart your service:
sudo service postgresql restart
then you should be able to connect to your DB via a SQL client.
Are you sure PostgreSQL is listening on the IP address and Port number that you are using as host and port parameters. Try by modifying your postgresql.conf file and restarting the server.
sudo nano /etc/postgresql/{YOUR_POSTGRES_VERSION}/main/postgresql.conf
Now go on and find the connection settings and update the following values.
listen_addresses = {YOUR_IP_ADDRESS}
port = {YOUR_PORT_NUMBER}
Now save the file and restart postgresl server:
sudo systemctl restart postgres
Checkout documentation here: