Postgresql – I can not get Postgres to listen on anything but localhost

postgresql-10Ubuntu

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.

sudo service postgresql stop
sudo pg_renamecluster 10 main main_pristine
sudo pg_upgradecluster 9.5 main
sudo service postgresql start
sudo pg_dropcluster 9.3 main

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!