1

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:~$
Improve this question
5
  • Can you show us that you don’t have multiple versions of Postgres installed?. What does pg_lsclusters say?
    – Colin 't Hart
    Sep 1, 2018 at 4:08
  • netstat shows the PostgreSQL server is still listening on 127.0.0.1:5432. Changing listen_addresses requires server restart, you did restart the server, right?
    – hruske
    Sep 1, 2018 at 9:17
  • Yes, I have restarted the sever a few times, since I didn't really trust restarting only Postgres. I don't believe I have multiple copies installed, where is the pg_lsclusters? postgresql.conf?
    – Arana
    Sep 2, 2018 at 16:14
  • All I could find that was even remotely close... csadmin@BulletinBoardServer:~$ sudo cat /etc/postgresql/10/main/postgresql.conf | grep pg_ # server for the changes to take effect, run "pg_ctl reload", or execute # "SELECT pg_reload_conf()". Some parameters, which are marked below, hba_file = '/etc/postgresql/10/main/pg_hba.conf' # host-based authentication file ident_file = '/etc/postgresql/10/main/pg_ident.conf' # ident configuration file stats_temp_directory = '/var/run/postgresql/10-main.pg_stat_tmp' default_text_search_config = 'pg_catalog.english'
    – Arana
    Sep 2, 2018 at 23:36
  • Colin, you were quite right! I initially thought you meant multiple versions running! This explains a lot, like why the default port was set to 5433 not 5432. I'm not sure how I got two versions on there, but I've been working the version 10 config files and it's been running version 9.5. I have migrated the cluster over to version 10, and all is now quite happy! I learned a lot in the process, Thank you for your assistance.
    – Arana
    Sep 5, 2018 at 1:32

1 Answer 1

Reset to default
1

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!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.