I have been on this for MANY hours and can not understand why Postgres will not listen on anything other than localhost. I an running on Ubuntu 18.04 clean install + Django, Postgres and nginx. All up and running fine. I am now trying to get external db access working. I can access the user and db from localhost by not the server's external IP. I have checked the listen_addresses and the host entries so many times I can't even remember. Restart Postgres, check. Restart server, check. Yes, there is a firewall running, but I can't even log in using psql and the server's IP. I can if I use localhost…
Anyone think of something I haven't tried yet??
csadmin@BulletinBoardServer:~$ netstat -anpt | grep LISTEN
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN -
tcp6 0 0 :::80 :::* LISTEN -
tcp6 0 0 :::22 :::* LISTEN -
csadmin@BulletinBoardServer:~$ sudo tail -20 /etc/postgresql/10/main/pg_hba.conf
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 0.0.0.0/0 md5
#host all all 127.0.0.1/32 md5
#host cs250phpdata cs250student 96.***.47.54/0 md5
#host cs250phpdata cs250student new***cs.com/0 md5
# IPv6 local connections:
host all all ::/0 md5
#host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
csadmin@BulletinBoardServer:~$ sudo cat /etc/postgresql/10/main/postgresql.conf |grep listen_
#listen_addresses = 'localhost' # what IP address(es) to listen on;
listen_addresses = '*' # Added 8/31/18 to allow external access.
csadmin@BulletinBoardServer:~$ sudo ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip
To Action From
-- ------ ----
22/tcp (OpenSSH) ALLOW IN Anywhere
80,443/tcp (Nginx Full) ALLOW IN Anywhere
5432 ALLOW IN Anywhere
22/tcp (OpenSSH (v6)) ALLOW IN Anywhere (v6)
80,443/tcp (Nginx Full (v6)) ALLOW IN Anywhere (v6)
5432 (v6) ALLOW IN Anywhere (v6)
csadmin@BulletinBoardServer:~$
Best Answer
I just wanted to make sure that this showed up as solved.
I had unknowingly installed a version 9.5 and a version 10. I had been editing the version 10 config, not knowing that both versions were running.
Make sure you have copies of your postgresql.conf and pg_hba.conf files before you start as these got returned to default state when I did the upgrade.
Now it makes A LOT of sense why my config changes weren't taking effect ;-)
Many thanks to Colin 't Hart for the invaluable clue!