1

I have just deployed a Postgres / timescaleDB on Ubuntu in AWS.

When I ssh my postgres VM, I can connect pgsql via CLI.

ubuntu@ip-172-31-35-57:~$ psql -U julien -h localhost db
Password for user julien: 
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

db=>

I can also see postgres is listening

ubuntu@ip-172-31-35-57:~$ sudo netstat -nlpute
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       User       Inode      PID/Program name    
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      101        46086      6152/systemd-resolv 
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      0          20033      700/sshd: /usr/sbin 
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      113        62083      20332/postgres      
tcp6       0      0 :::80                   :::*                    LISTEN      0          68924      23549/apache2       
tcp6       0      0 :::22                   :::*                    LISTEN      0          20044      700/sshd: /usr/sbin 
udp        0      0 127.0.0.53:53           0.0.0.0:*                           101        46085      6152/systemd-resolv 
udp        0      0 172.31.35.57:68         0.0.0.0:*                           100        651337     6134/systemd-networ

I checked ufw:

ubuntu@ip-172-31-35-57:~$ sudo ufw status
Status: inactive

I also opened fully Security Groups.

But still, I cannot connect my postgres DB from local.

With a local Postgres DB, I have no problem ( my app works )

What did I forget ???

Juliatzin
  • 18,455
  • 40
  • 166
  • 325

1 Answers1

4

  1. Check listen_addresses in postgresql.conf file allowed remote connections. It should be

    listen_addresses = '*'

  2. In pg_hba.conf file you need to add following entry

    host all all 0.0.0.0/0 md5

  3. Be sure it is not a firewall issue.

I S
  • 436
  • 3
  • 7
  • 1
    if it is a firewall issue, what shall I do? how to check and how to fix?? please let me know.. because I did change all, pg_hba.conf and postgresql.conf like you said above but still get same problem. – Peter Kam Jun 24 '22 at 11:35
  • @PeterKam in this case you shoud add the port 5432 to your firewall like this : sudo ufw allow 5432 – siiraaj Mar 09 '23 at 18:31