Postgresql – psql breaks if I specify host localhost

postgresqlpostgresql-9.3tcpip

I am aware that this or similar has been asked in other places. None of the other answers have worked for me.

Machine is a Macbook Pro 2014 / OS X Yosemite 10.10 with Postgres 9.3.5 installed via Homebrew.

I can only connect to my local postgres instance if I do NOT specify a host. This works:

psql -Umyuser -d mydb
sql (9.4.4)
Type "help" for help.

myuser=# 

-h localhost does not:

psql -Umyuser -d mydb -h localhost
psql: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

It pauses for a long time before I get this message.

I am trying to set up a third party tool that seems to want to specify localhost, so it is not an option just to leave that off.

If I understand what I've read correctly elsewhere, postgres behaves differently if you're using TCP/IP, and specifying -h localhost forces that. Accordingly I have added the following in pg_hba.conf:

local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             localhost               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

(127.0.0.1 AND localhost seems redundant but desperation drives superstition) … and in postgresql.conf:

listen_addresses = 'localhost'          
port = 5432                             

netstat confirms that Postgres is listening to that port:

☁  ~  netstat -an | grep 5432
tcp4       0      0  127.0.0.1.5432         *.*                    LISTEN     
tcp6       0      0  ::1.5432               *.*                    LISTEN     
d8d7c0e31d5add1d stream 0 0 d8d7c0e326b18a1d 0 0 0 /tmp/.s.PGSQL.5432

All permissions are set on the socket file:

~ ls -l /tmp/.s.PGSQL.5432
srwxrwxrwx  1 fritz  wheel  0 12 Jul 13:27 /tmp/.s.PGSQL.5432

/etc/hosts has an entry for localhost:

127.0.0.1   localhost
::1         localhost

psql and pg_ctl are pointing to the same installations:

~ which psql               
/usr/local/bin/psql
~ ll /usr/local/bin/psql
/usr/local/bin/psql -> ../Cellar/postgresql/9.4.4/bin/psql
~ which pg_ctl
/usr/local/bin/pg_ctl
~  ll /usr/local/bin/pg_ctl
/usr/local/bin/pg_ctl -> ../Cellar/postgresql/9.4.4/bin/pg_ctl

There's nothing named psql elsewhere on my system that seems like it could be hijacking this process:

~  sudo find / -name psql
/Applications/pgAdmin3.app/Contents/SharedSupport/psql
/Users/fritz/devtools/phantomjs/src/qt/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/config.tests/unix/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/plugins/sqldrivers/psql
/Users/fritz/Qt/5.3/Src/qtbase/src/sql/drivers/psql
/usr/local/bin/psql
/usr/local/Cellar/postgresql/9.4.4/bin/psql

UPDATE: telnet connects to localhost 5432 for a while and then is disconnected:

~  telnet localhost 5432
Trying ::1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

UPDATE 2: I used wireshark to spy on tcp localhost port 5432. Trouble is, I don't know what I should see and whether/how the results are wrong. Hoping someone out there can make something of this capture file.

Best Answer

Thanks to the suggestion on pgsql-general that I check TCP/IP with netcat I discovered that the first few bytes sent to localhost were being swallowed by a process that was intended to keep an audit trail of Internet access. Uninstalled and everything works as I expect.